Add multiple constraints in one statement
constraints, sql, sql-server, sql-server-2012
Solution
You have three problems:
- You terminate the statement with the `;` at the end of your second line of code.
- You have `FOR MEMBER_ID` in the last statement, which should probably be `FOR Sys_date`.
- You repeat `ADD` but don't have to.
Assuming this table structure:
CREATE TABLE Member (MEMBER_ID BIGINT NOT NULL, Sys_date DATETIME);
This DDL will work:
ALTER TABLE MEMBER
ADD CONSTRAINT U_MEMBERID UNIQUE(MEMBER_ID), primary key (MEMBER_ID),
CONSTRAINT Sys_date DEFAULT GETDATE() FOR Sys_date;
See this sqlfiddle.
You can theoretically also see this on the MSDN's page on `ALTER TABLE`, though I'd readily admit those specs can be hard to read. Here's a stab at how they explain it:
ALTER TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name
{
ALTER COLUMN column_name
-- Omitted....
| ADD
{
<column_definition>
| <computed_column_definition>
| <table_constraint>
| <column_set_definition>
} [ ,...n ]
-- Omitted....
The `ADD` keyword occurs once, and the `} [ ,...n ]` bit tells you that you can repeat the bit between {brackets} `n` times, separated by a `,`.
Problem
I am supposed to modify my current table named MEMBER in Microsoft SQL Server 2012. I am trying to modify the tables ``` ALTER TABLE MEMBER ADD CONSTRAINT U_MEMBERID UNIQUE(MEMBER_ID), primary key (MEMBER_ID); ADD CONSTRAINT Sys_date DEFAULT GETDATE() FOR MEMBER_ID; ``` The above doesn't work and it says: Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'CONSTRAINT'. I think I am doing something wrong, but I am not sure what it is. ``` ALTER TABLE TITLE ADD CONSTRAINT U_TITLEID UNIQUE(TITLE_ID), primary key (TITLE_ID); add constraint C_CATEGORY CHECK(CATEGORY='DRAMA' OR 'COMEDY' OR 'ACTION' OR 'CHILD' OR 'SCIFI' OR 'DOCUMENTARY'; ``` Also: is it possible to add the above code to the code preceding it and execute both of them in the same sql query? How do I fix this?