CODEWITHSUNDEEP
sql
Dynamiite
Dynamiite

Reputation: 1499

SQL Incorrect syntax for definition of the 'TABLE' constraint

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)

Upvotes: 0

Views: 15039

Answers (2)

datalifenyc
datalifenyc

Reputation: 2248

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

Upvotes: 3

Vikdor
Vikdor

Reputation: 24124

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) 
)

Upvotes: 5

Related Questions