alter table

No books on line tem um exemplo:

G. Disable and reenable a constraint

This example disables a constraint that limits the salaries accepted in the data. WITH NOCHECK CONSTRAINT is used with ALTER TABLE to disable the constraint and allow an insert that would normally violate the constraint. WITH CHECK CONSTRAINT re-enables the constraint.

 

CREATE TABLE cnst_example

(id INT NOT NULL,

name VARCHAR(10) NOT NULL,

salary MONEY NOT NULL

CONSTRAINT salary_cap CHECK (salary < 100000)

)

-- Valid inserts

INSERT INTO cnst_example VALUES (1,"Joe Brown",65000)

INSERT INTO cnst_example VALUES (2,"Mary Smith",75000)

-- This insert violates the constraint.

INSERT INTO cnst_example VALUES (3,"Pat Jones",105000)

-- Disable the constraint and try again.

ALTER TABLE cnst_example NOCHECK CONSTRAINT salary_cap

INSERT INTO cnst_example VALUES (3,"Pat Jones",105000)

-- Reenable the constraint and try another insert, will fail.

ALTER TABLE cnst_example CHECK CONSTRAINT salary_cap

INSERT INTO cnst_example VALUES (4,"Eric James",110000)

Att.

Junior