Re: Can I have check and referential constraint on same column?

Rick.Chevalier wrote:

I have come across a situation where I think I need both a check
constraint and a referential constraint on the same column to
handle different situations.

When a row is inserted the default value for the status column
will be 'N'. When the information has been validated it will
change to 'V'. This is my current check constraint.

If the validation is overridden an override code can be placed
into the status column from an exception table. This is my
referential constraint.

I have thought about adding the check values to the exception
table and excluding them from the exception prompt but I would
prefer to handle it without doing that if possible. If I can't
have both constraints on the status column this is probably what
I will do.

Why not use the referential constraint to include all possible values; that is the point of the parent. I think just a slight perspective change is in order; i.e. step back from concentration on just those values that are override codes. The default value and the is-validated value are both valid values for the relationship to a parent, and the foreign key effects the capability of the CHECK constraint. Those two values only seem incorrect\unexpected from the perspective of the /exception prompt/ which is described as rows of the /exception/ table. So instead of an exception table, create a /valid status/ table, which should seem more intuitively to serve as the parent for the /status column/. Then create and use a VIEW which excludes those undesirable values to produce the exception prompt; i.e. the VIEW encapsulates the logic for generating the rows that represents the /exception table/.

<code>

create table ValidStatus
( StatusCode char not null)
,primary key (StatusCode))
;
create view ExceptionStatus (OverrideCode) as
( select StatusCode from ValidStatus
where StatusCode not in ( 'N' /* default status */
, 'V' /* status=Validated */ )
)
;
create table DInfo
( i int /* just some data to have a /status/ */
, Dstatus char not null with default 'N'
, foreign key (Dstatus) references ValidStatus(StatusCode))
;
insert into ValidStatus values('N'),('V'),('O')
;
insert into DInfo (i) values(1) /* new row; defaut Dstatus */
;
update DInfo set Dstatus='V' where i=1 /* row is Validated */
;
select * from ExceptionStatus /* list override status codes*/
....+....1..
OVERRIDECODE
O
** End of data **

</code>

Regards, Chuck