CODEWITHSUNDEEP
sqlsql-serverdatabase
Kasik
Kasik

Reputation: 41

Insertin new column into table with condition

I have a table Plane where is column made_in_year (INT) and i need a new column last_repair (datetime) NOT NULL and it need to have condition which is last_repair>= made_in_year and i'm not sure how to add that condition or what syntax i should use... I know i have to use ALTER TABLE Plane ALTER COLUMN last_repair DATETIME xxxxxxxxx and what should i use instead of xxxxxx?

Upvotes: 2

Views: 71

Answers (2)

OneCricketeer
OneCricketeer

Reputation: 191743

I thought you wanted to add both the column and the constraint at the same time. 

ALTER TABLE Plane
ADD last_repair DATETIME NOT NULL
CONSTRAINT repair_chk CHECK (last_repair >= made_in_year)
DEFAULT(-1);

Then since you wanted NOT NULL, you need to specify a default but I couldn't figure out how to set last_repair = made_in_year in that last statement, so here's an UPDATE. 

UPDATE Plane
SET last_repair = made_in_year
WHERE last_repair = -1;

Upvotes: 1

Rahul Tripathi
Rahul Tripathi

Reputation: 172438

Try this:

alter table Plane 
add constraint myconstraint
check(last_repair>= made_in_year)

Upvotes: 2

Related Questions