0

I can't understand why MySQL showing this error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONSTRAINT (uname_pk) PRIMARY KEY, pwd Varchar(50), fname Varchar(50), lname ' at line 1

And this is the query:

create table uinfo_master(uname Varchar(50) NOT NULL CONSTRAINT (uname_pk) PRIMARY KEY,
pwd Varchar(50),
fname Varchar(50),
lname Varchar(50),
email Varchar(50),
phno Varchar(50),
address Varchar(200),
city Varchar(50),
state Varchar(50),
pin Varchar(50),
country Varchar(50));

What is wrong with my query, kindly show my mistake.

closed as off-topic by Erik, Andriy M, Vérace, Michael Green, Tom V Mar 29 '16 at 9:26

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Erik, Andriy M, Vérace, Michael Green, Tom V
If this question can be reworded to fit the rules in the help center, please edit the question.

1

Correct syntax, with minimal changes to your style:

create table uinfo_master(
uname Varchar(50) NOT NULL, 
CONSTRAINT uname_pk PRIMARY KEY (uname),
pwd Varchar(50),
fname Varchar(50),
lname Varchar(50),
email Varchar(50),
phno Varchar(50),
address Varchar(200),
city Varchar(50),
state Varchar(50),
pin Varchar(50),
country Varchar(50));

You cannot use the CONSTRAINT syntax for the PK on the same line as the column definition.

See the CREATE TABLE syntax:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    { LIKE old_tbl_name | (LIKE old_tbl_name) }

create_definition:
    col_name column_definition
  | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
      [index_option] ...

Defining the column itself is done on the col_name column_definition version of the syntax. The | that follows that stands for OR in the documentation, meaning an alternate CREATE TABLE (comma separated) "line" can use the syntax that starts with CONSTRAINT, which optionally includes a "symbol" (that's what brackets mean: optional), then PRIMARY KEY, optionally an index_type, and then the column itself, in parentheses.

From the look of it, you tried to do all that on one line, which was where your problem was. You can declare a column to be a PRIMARY KEY on the line the column is defined, but you cannot use the CONSTRAINT syntax to do so.

  • Declaring a field NOT NULL and PRIMARY KEY is pointless - by definition if a field is a PRIMARY KEY (on its own), it cannot be NULL. – Vérace Mar 28 '16 at 22:35
  • @WillemRenzema Thank you for your detailed answer, but I came to know about this syntax from this site docs.oracle.com/javadb/10.8.3.0/ref/rrefsqlj13590.html see the query given on the site -- column-level primary key constraint named OUT_TRAY_PK: CREATE TABLE SAMP.OUT_TRAY ( SENT TIMESTAMP, DESTINATION CHAR(8), SUBJECT CHAR(64) NOT NULL CONSTRAINT OUT_TRAY_PK PRIMARY KEY, NOTE_TEXT VARCHAR(3000) ); – Amit Roy Mar 29 '16 at 3:55
  • 2
    That is Oracle syntax, not MySQL. Stick with stuff at dev.mysql.com . – Rick James Mar 29 '16 at 4:27
  • @RickJames Thank you for clearing this confusion. – Amit Roy Mar 29 '16 at 14:35

Not the answer you're looking for? Browse other questions tagged or ask your own question.