SQL Incorrect syntax for definition of the 'TABLE' constraint

Question

I keep getting this error everytime i try to exceute the query . I checked for any syntax errors multiple time, but i cant find any.

Msg 142, Level 15, State 2, Line 0
Incorrect syntax for definition of the 'TABLE' constraint. 

CREATE TABLE "hold" (
    timePutOnHold TIME(7),
    customer_id VARCHAR(13),REFERENCES "Customer",
    isbn VARCHAR(13) REFERENCES "Item_Details",
    PRIMARY KEY (customer_id, isbn, timePutOnHold)
)

SOLVED! I had to remove a comma after customer_id VARCHAR(13)

score 4 · Accepted Answer · answered Feb 20 '13 at 05:32

customer_id VARCHAR(13),REFERENCES "Customer",

The comma between the data type and the REFERENCES keyword might be causing the syntax error.

In general, it is a good practice to give meaningful names to constraints so that they can be dropped easily later on.

CREATE TABLE hold 
( 
   timeputonhold TIME(7), 
   customer_id   VARCHAR(13), 
   isbn          VARCHAR(13), 
   CONSTRAINT pk_hold PRIMARY KEY (customer_id, isbn, timeputonhold), 
   CONSTRAINT fk_hold_customer FOREIGN KEY (customer_id) REFERENCES customer(customer_id), 
   CONSTRAINT fk_hold_isbn FOREIGN KEY (isbn) REFERENCES item_details(isbn) 
)

Thanks for the advice regarding naming the constraints. I'll keep that in mind. — Dynamiite, Feb 20 '13 at 05:33

score 3 · Answer 2 · edited May 23 '17 at 12:13

Please note that DEFAULT constraints are different with regards to syntax in a CREATE TABLE statement.

Without a comma separator, the CONSTRAINT references the preceding column.

-- correct syntax for adding a default constraint to a CREATE TABLE statement    
IsAvailable BIT NOT NULL CONSTRAINT d_IsAvailable DEFAULT(1)

However, using a comma separate and the keys words DEFAULT FOR will produce the same error message.

-- incorrect syntax for adding a default constraint to a CREATE TABLE statement
IsAvailable BIT NOT NULL
, CONSTRAINT d_IsAvailable DEFAULT(1) FOR IsAvailable

This nuance can be tricky since CONSTRAINT constraint_name DEFAULT(constraint) FOR column_name is correct for ALTER TABLE statements.

Further Reading:
Declaring a default constraint when creating a table
Specify Default Values for Columns

SQL Incorrect syntax for definition of the 'TABLE' constraint

2 Answers2