CHECK constraint

CREATE TABLE T_StaffMain (
[snip]
SM_PostNameE VARCHAR(40) NOT NULL,
SM_PostNameD NVARCHAR(40) NOT NULL,
SM_PostLevel INTEGER DEFAULT 0 NOT NULL CHECK (SM_PostLevel IN
(0,1,2,3)),
[snip]
);
guys. i've got to change the CHECK statement above to values from 0 to
7 instead of 0 to 3.
this is in a production table, so dropping the table is not an option.
how to do this?
thanx
riyazHi
First , you have to DROP CONSTRAINT (see details in the BOL)
<rmanchu@.gmail.com> wrote in message
news:1141616935.268356.322910@.i39g2000cwa.googlegroups.com...
> CREATE TABLE T_StaffMain (
> [snip]
> SM_PostNameE VARCHAR(40) NOT NULL,
> SM_PostNameD NVARCHAR(40) NOT NULL,
> SM_PostLevel INTEGER DEFAULT 0 NOT NULL CHECK (SM_PostLevel IN
> (0,1,2,3)),
> [snip]
> );
> guys. i've got to change the CHECK statement above to values from 0 to
> 7 instead of 0 to 3.
> this is in a production table, so dropping the table is not an option.
> how to do this?
> thanx
> riyaz
>|||Hello, riyaz
You will have to drop the constraint, but you need to know it's name,
because you didn't name it when you created it. To find out the
constraint's name, you can use Enterprise Manager or the following
query:
SELECT o.name FROM sysconstraints k
INNER JOIN sysobjects o ON k.constid=o.id
INNER JOIN syscolumns c ON c.id=k.id AND c.colid=k.colid
WHERE c.id=OBJECT_ID('T_StaffMain') AND c.name='SM_PostLevel'
AND o.type='C'
You should create the new constraint with a name, like this:
ALTER TABLE T_StaffMain ADD CONSTRAINT [CK_T_StaffMain_SM_PostLevel]
CHECK (SM_PostLevel BETWEEN 0 AND 7)
Razvan