Sure. Make sure neither column is specified as NOT NULL. ALTER TABLE <sametable> ADD FOREIGN KEY (ProjectState) REFERENCES States ALTER TABLE <sametable> ADD FOREIGN KEY (OwnerState) REFERENCES States
Now when the user adds a state, it checks it, but if it is null doesn't bother with the check. Charles Parks <[EMAIL PROTECTED]> wrote: >In my case, I have ProjectState and OwnerState in the same table. �They both >need to refernce ST in the State Table. �This is why I am using a rule >instead of a Foreign Key. �Would FK still be better? > >-----Original Message----- >From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] >Sent: Monday, February 03, 2003 9:10 AM >To: [EMAIL PROTECTED] >Subject: [RBASE-L] - RE: Verify value or is null > > >If is not uncommon to have a field that, if it has a value, must have a >valid one. A common one would be a State/Province field that must be a valid >State/Province, but can be null when you have no address filled in. > >To do this > >Check the data, if any, in the FK side for invalids, and correct them > >Create a Foreign Key on the column, but do not define the column as not >null. > >This is much more efficient than a rule. > > >Charles Parks <[EMAIL PROTECTED]> wrote: > >>If the user will have a chance of not knowing the correct answer except >that >>it must exist in a certain list would it be better to have no referencing? >> >>Charlie >>-----Original Message----- >>From: Walker, Buddy [mailto:[EMAIL PROTECTED]] >>Sent: Monday, February 03, 2003 7:55 AM >>To: [EMAIL PROTECTED] >>Subject: [RBASE-L] - RE: Verify value or is null >> >> >>Charlie >> �What I use is Primary and Foreign Keys. I wouldn't suggest storing a null >>value as a primary key but use something that you know won't be input. For >>instance if the primary key is idnumber and it is integer then maybe make >>use -1 >> >>Buddy >> >> >>-----Original Message----- >>From: Charles Parks [mailto:[EMAIL PROTECTED]] >>Sent: Monday, February 03, 2003 8:48 AM >>To: [EMAIL PROTECTED] >>Subject: [RBASE-L] - Verify value or is null >> >> >>Is there a way to create a rule with a where clause that will allow entry >to >>made into the table if the value exists in another table or if the value is >>null? >> >>I thought it would be something like this: >>WHERE EXISTS (SELECT Column1 FROM Table1 WHERE Table1.Column1 = >>Table2.Column2 ) or Table2.Column2 is null >>but that where clause is not working. >> >>Is possible to just store a null value in the lookup table or should I just >>set a default value in the primary table? >> >>Thanks, >>Charlie >> >> > > >-- >Albert Berry >Full Time Consultant to >PSD Solutions >350 West Hubbard, Suite 210 >Chicago, IL 60610 >312-828-9253 Ext. 32 > > >__________________________________________________________________ >The NEW Netscape 7.0 browser is now available. Upgrade now! >http://channels.netscape.com/ns/browsers/download.jsp > >Get your own FREE, personal Netscape Mail account today at >http://webmail.netscape.com/ > > -- Albert Berry Full Time Consultant to PSD Solutions 350 West Hubbard, Suite 210 Chicago, IL 60610 312-828-9253 Ext. 32 __________________________________________________________________ The NEW Netscape 7.0 browser is now available. Upgrade now! http://channels.netscape.com/ns/browsers/download.jsp Get your own FREE, personal Netscape Mail account today at http://webmail.netscape.com/
