How Do You Troubleshoot a Syntax Error When Trying to Create a Table with a Primary Key in SQL?

Problem scenario
You are trying to create a table using SQL.  You use the keywords "CONSTRAINT" and "PRIMARY KEY."  For example, your query (or SQL DML command) looks like this:

create table contint ( specialcode char(6) CONSTRAINT PRIMARY KEY, title varchar(40)) ;

When you run the SQL command you get the message '...syntax error at or near "PRIMARY" LINE...'  What should you do to fix this?

Solution
Make sure you give the constraint itself a name.  The syntax is for the name of the constraint to follow the word "CONSTRAINT" and appear before the "PRIMARY KEY" words.

create table contint ( specialcode char(6) CONSTRAINT initialkey PRIMARY KEY, title varchar(50)) ;

Identifying constraints by names, while mandatory, helps future SQL querying.  The error message that is thrown may include the potentially descriptive name of the CONSTRAINT that was violated.  A constraint's name can be modified later. 

Leave a comment

Your email address will not be published.