For example, if a date column can no longer be null, and the earliest valid date is 1/1/98 then I suggested the user replace all column values for null dates with something like 1/1/90. Once they corrected all column values, then the constraints were added. In most cases it was just making integer and dollar values zero (when not known) and dates to a dummy date for null values.
Dennis
*****
At 05:14 PM 9/15/2005, you 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]
Dennis Fleming
IISCO
Phone: 570 775-7593
Mobile: 570 351-5290
