Don't know why MySQL is not creating table with primary key constraint 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.
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.