MySQL Primary Key

primary key in MySQL

MySQL Primary Key is a field in the table that uniquely identifies the table records.

The primary key main features:

  • It must contain a unique value for each row.
  • Each table can contain only one primary key.
  • It cannot contain NULL values.
  • MySQL does not allow us to insert a new row with the existing primary key.
  • It is recommended to use INT or BIGINT data type for the primary key column.

We can create a primary key in two ways:

  • CREATE TABLE Statement
  • ALTER TABLE Statement

CREATE TABLE Statement

Syntax

The following are the syntax used to create a primary key in MySQL.

CREATE TABLE table_name(

    col1 datatype PRIMARY KEY,

    col2 datatype,

    ...

);

If we want to create more than one primary key column into the table, use the below syntax:

CREATE TABLE table_name

(

  col1 col_definition,

  col2 col_definition,

  ...



  CONSTRAINT [constraint_name] 

   PRIMARY KEY (column_name(s))

);

Primary Key Example

The following example explains how a primary key used in My SQL.

Mysql> CREATE TABLE Login(

   login_id INT AUTO_INCREMENT PRIMARY KEY,

   username VARCHAR(40),

   password VARCHAR(55),

   email VARCHAR(55)

);

Next, use the insert query to store data into a table:

mysql> INSERT INTO Login(login_id, username, password, email) 

VALUES (1,'Stephen', 15343434532, 'stephen@javatpoint.com'), 

(2, 'Joseph', 35435479495, 'Joseph@javatpoint.com');



mysql> INSERT INTO Login(login_id, username, password, email) 

VALUES (1,'Peter', 15343434532, 'peter@javatpoint.com');

ALTER TABLE Statement

Syntax

Following are the syntax of the ALTER TABLE statement to create a primary key in My SQL:

ALTER TABLE table_name ADD PRIMARY KEY(column_list);

Example

The following statement creates a table “Persons” that have no primary key column into the table definition.

mysql> CREATE TABLE Persons (

        Person_ID int NOT NULL, 

        Name varchar(45), 

        Age int, 

        City varchar(25)

    );

Spread the love

Leave a Comment

Your email address will not be published. Required fields are marked *