Hi Steve, There is another message about getting rid of the NULLs, which makes good sense. Fill them with some easily identifiable value.
If, however, you have values in a column you wish to FOREIGN KEY to the primary key, I would create one or more arbitrary, easily recognizable, PKs to meet the invalid FK values. This can be done quite easily with a simple command file. --- Steve Breen <[EMAIL PROTECTED]> wrote: > Albert, > > Let me throw you a curve. This constraint can be set to not allow DoorNam > to > be null but as this is this is an older database it has to take effect > based > on a certain start date. It can not affect older data. > > I know I can do this with a rule but can it be done with a constraint? > > Steve > > -----Original Message----- > From: Albert Berry [mailto:[EMAIL PROTECTED] > Sent: Thursday, September 15, 2005 2:34 PM > To: [email protected] > Subject: [RBG7-L] - Re: Constraints > > > That would do for the customer name if you wish it. You can also define a > multi column UNIQUE index, which would prevent the same branch from being > entered twice. Note that the UNIQUE constraint and the UNIQUE index work > differently. > > --- Steve Breen <[EMAIL PROTECTED]> wrote: > > > Albert, > > > > This column would have duplicate data so can you just use the following > > command > > > > ALTER TABLE Customers ALTER COLUMN CustName NOT NULL > > > > Steve > > > > -----Original Message----- > > From: Albert Berry [mailto:[EMAIL PROTECTED] > > Sent: Thursday, September 15, 2005 12:03 PM > > To: [email protected] > > Subject: [RBG7-L] - Re: Constraints > > > > > > Steve, here is a short review of constraints. > > > > PRIMARY KEY - Allows neither NULLs nor duplicate values. It is used as a > > reference from FOREIGN KEY. > > > > When the FOREIGN KEY is active, only values in the PRIMARY KEY are > allowed. > > NULLs are allowed unless the FOREIGN KEY column is expressly constrained > as > > NOT NULL. > > > > A UNIQUE constraint may be applied where a column other than the PRIMARY > > KEY > > needs to have no duplicates. UNIQUE requires NOT NULL - you can have no > > nulls > > in PRIMARY KEYS or in UNIQUE constraints. > > > > Here are the commands: > > > > ALTER TABLE Customers ALTER COLUMN CustID NOT NULL PRIMARY KEY > > > > ALTER TABLE CustomerNotes ALTER COLUMN CustID NOT NULL REFERENCES > Customers > > > > ALTER TABLE Customers ALTER COLUMN CustName NOT NULL UNIQUE > > > > > > --- Steve Breen <[EMAIL PROTECTED]> wrote: > > > > > > > > > > > I have never worked with constraints and need to put a constraint on > the > > > column (Doornam) in a master > > > > > > Any assistance would be appreciated > > > > > > > > > > > > > > > > > > Stephen Breen > > > > > > CDI Services, Inc. > > > > > > > > > > > > > > > > > > > > > > > > Albert Berry > > Management Consultant > > RR2 - 1252 Ponderosa Drive > > Sparwood BC, V0B 2G2 > > Canada > > (250) 425-5806 > > (250) 425-7259 > > (708) 575-3952 (fax) > > [EMAIL PROTECTED] > > > > > > > Albert Berry > Management Consultant > RR2 - 1252 Ponderosa Drive > Sparwood BC, V0B 2G2 > Canada > (250) 425-5806 > (250) 425-7259 > (708) 575-3952 (fax) > [EMAIL PROTECTED] > > Albert Berry Management Consultant RR2 - 1252 Ponderosa Drive Sparwood BC, V0B 2G2 Canada (250) 425-5806 (250) 425-7259 (708) 575-3952 (fax) [EMAIL PROTECTED]
