Add multiple constraints in one statement

Question

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?

Answer 1

You have three problems:

1. You terminate the statement with the ; at the end of your second line of code.
2. You have FOR MEMBER_ID in the last statement, which should probably be FOR Sys_date.
3. 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 ,.