In this article we will see "How to use PRIMARY KEY Constraint in MySql" and "PRIMARY KEY Constraint query syntax with example".

PRIMARY KEY on CREATE TABLE query syntax

To create a primary key in mySql while creating a table, following syntax is used.


/* Without providing a name to the key*/
CREATE TABLE tableName (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
   	PRIMARY KEY (columnName/columnNames)
);

/* Providing a name to the key*/
CREATE TABLE tableName (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
   	CONSTRAINT primaryKeyName PRIMARY KEY (columnName/columnNames)
);

PRIMARY KEY on CREATE TABLE query example

"PRIMARY KEY" can be used to create a primary key in mySql while creating a table, as shown in the example below.


/* Without providing a name to the key*/
CREATE TABLE testTableA (
    id int,
    name varchar(30),
    email varchar(20),
    PRIMARY KEY (id)
);

/* Providing a name to the key*/
CREATE TABLE testTableB (
    id int,
    name varchar(30),
    email varchar(20),
   	CONSTRAINT PK_B PRIMARY KEY (id)
);


PRIMARY KEY on already created table syntax

To create a primary key in mySql on already created table, following syntax is used.


/* Without providing a name to the key*/
ALTER TABLE tableName
ADD PRIMARY KEY (columnName/columnNames);

/* Providing a name to the key*/
ALTER TABLE tableName
ADD CONSTRAINT primaryKeyName PRIMARY KEY (columnName/columnNames);

PRIMARY KEY on already created table example

"PRIMARY KEY" can be used to create a primary key in mySql on already created table, as shown in the example below.


/* Without providing a name to the key*/
ALTER TABLE testTableC
ADD PRIMARY KEY (name);

/* Providing a name to the key*/
ALTER TABLE testTableD
ADD CONSTRAINT PK_NAME PRIMARY KEY (name);


DROP PRIMARY KEY syntax

"DROP PRIMARY KEY" can be used to drop a primary key in mySql, as shown below.


ALTER TABLE tableName
DROP PRIMARY KEY;

DROP PRIMARY KEY example

"DROP PRIMARY KEY" can be used to drop a primary key in mySql, as shown in the example below.


ALTER TABLE testTableC
DROP PRIMARY KEY;


In this article we have seen "How to use PRIMARY KEY Constraint in MySql" and "PRIMARY KEY Constraint query syntax with example", in upcoming articles we will see more about Mysql syntax and queries with example.
  • By Techburps.com
  • Oct 19, 2017
  • MySql