CODEWITHSUNDEEP
sqlsql-serversql-server-2008
legend
legend

Reputation: 99

How to redefine the constraint in a field?

the table was built as follow

create table schema1.detail(
ornum char(6) foreign key references schema1.menu(ornum),
num int,
pdname char(20),
price money check(price>0) default null,
amount int check(amount>0) not null,
primary key (ornum,num)
)

and I want to redefine the field 'amount' as amount>0 and amount<=1000 not null but I don't know how to do it.

Upvotes: 1

Views: 195

Answers (3)

Zohar Peled
Zohar Peled

Reputation: 82474

Well, if you already had data in your table you would have needed to keep it so you would have needed to first find out the name of the constraint (since your create script does not provide names to the constraints, SQL Server does it automatically for you), then drop the constraint and create a new one.
To do that, you would need to do something like this to find out the name of the constraints in your table:

SELECT   TABLE_NAME, 
         COLUMN_NAME, 
         CHECK_CLAUSE, 
         cc.CONSTRAINT_SCHEMA, 
         cc.CONSTRAINT_NAME 
FROM     INFORMATION_SCHEMA.CHECK_CONSTRAINTS cc 
         INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE c 
           ON cc.CONSTRAINT_NAME = c.CONSTRAINT_NAME 
WHERE TABLE_NAME = 'detail'
AND cc.CONSTRAINT_SCHEMA = 'schema1'
ORDER BY CONSTRAINT_SCHEMA, 
         TABLE_NAME, 
         COLUMN_NAME

Then you need to drop the constraint and recreate it:

ALTER TABLE dbo.detail
DROP CONSTRAINT <constraint name> -- you got from the query above

ALTER TABLE detail
ADD CONSTRAINT CK_detail_amount CHECK(amount>0 AND amount<1000)

However, since this is a brand new table, I would suggest drop the table all together and re-create it, this time with proper names for the constraints:

drop table schema1.detail;

create table schema1.detail (
ornum char(6),
num int,
pdname char(20),
price money null, -- there is no need for the default keyword here...
amount int check(amount>0) not null,
constraint PK_detail primary key (ornum,num),
constraint FK_detail_menu foreign key (ornum) references schema1.menu(ornum),
constraint CK_detail_price check(price>0),
constraint CK_detail_amount check(amount>0 and amount <1000)
);

It's best practice to give your constraints meaningful names.

Upvotes: 3

SynozeN Technologies
SynozeN Technologies

Reputation: 1347

UPDATED

alter table schema1.detail drop constraint [yourConstraintName] ;
alter table schema1.detail add constraint [yourConstraintName] check (amount>0 and amount < 1000)

Upvotes: 1

Aleksandar Stanojevic Dante
Aleksandar Stanojevic Dante

Reputation: 95

check constraint like "fieldname BETWEEN 0 and 1000"

or

amount int check (amount>0 AND amount <1000) not null,

Upvotes: 0

Related Questions