Chuck,
I did think about this but not the use of the view. It is actually my preferred solution. SQL tables, constraints, etc. are still fairly new in our shop and I try to be careful about how I implement them so as not to hinder progress. I like the view idea. I think that will solve the problem.
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Friday, May 21, 2010 2:47 AM
To: midrange-l@xxxxxxxxxxxx
Subject: 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
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at
http://archive.midrange.com/midrange-l.
Privileged and Confidential. This e-mail, and any attachments there to, is intended only for use by the addressee(s) named herein and may contain privileged or confidential information. If you have received this e-mail in error, please notify me immediately by a return e-mail and delete this e-mail. You are hereby notified that any dissemination, distribution or copying of this e-mail and/or any attachments thereto, is strictly prohibited.
midrange.com
