SQL: Primary key

Primary key is an unique identifier, which is used to distinguish one  record from other record.

 

You can define a primary key to the table at the time of creation.

 

Example

CREATE TABLE employee (

    emp_id int,

    first_name varchar(255) NOT NULL,

    last_name varchar(255),

    PRIMARY KEY (emp_id)

);

mysql> CREATE TABLE employee (
    ->     emp_id int,
    ->     first_name varchar(255) NOT NULL,
    ->     last_name varchar(255),
    ->     PRIMARY KEY (emp_id)
    -> );
Query OK, 0 rows affected (0.01 sec)

 

When you set primary key constraint on a table, it implicitly applies NOT NULL constraint on this column.

mysql> desc employee;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| emp_id     | int          | NO   | PRI | NULL    |       |
| first_name | varchar(255) | NO   |     | NULL    |       |
| last_name  | varchar(255) | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

 

Let’s insert a record to the table employee.

 

INSERT INTO employee(emp_id, first_name, last_name) VALUES (1, 'krishna', 'gurram');

mysql> INSERT INTO employee(emp_id, first_name, last_name) VALUES (1, 'krishna', 'gurram');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM employee;
+--------+------------+-----------+
| emp_id | first_name | last_name |
+--------+------------+-----------+
|      1 | krishna    | gurram    |
+--------+------------+-----------+
1 row in set (0.00 sec)

 

Since emp_id is the primary key to the table employee, when you try to insert another employee record with the id 1, you will get an error.

mysql> INSERT INTO employee(emp_id, first_name, last_name) VALUES (1, 'Gopi', 'Battu');
ERROR 1062 (23000): Duplicate entry '1' for key 'employee.PRIMARY'

 

Can I have a combination of columns a primary key

Yes, you can have.

 

CREATE TABLE my_orders (

    order_id int,

    item_code varchar(20) NOT NULL,

    ordered_date date,

    CONSTRAINT order_pk PRIMARY KEY(order_id, ordered_date)

);

mysql> CREATE TABLE my_orders (
    ->     order_id int,
    ->     item_code varchar(20) NOT NULL,
    ->     ordered_date date,
    ->     CONSTRAINT order_pk PRIMARY KEY(order_id, ordered_date)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> desc my_orders;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| order_id     | int         | NO   | PRI | NULL    |       |
| item_code    | varchar(20) | NO   |     | NULL    |       |
| ordered_date | date        | NO   | PRI | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Can I apply primary key to an existing table?

Yes, you can do that using ‘ADD CONSTRAINT’ command. This command is used to define a constraint on existing table.

 

CREATE TABLE student(

         first_name varchar(20) NOT NULL,

         last_name varchar(20) NOT NULL,

         email varchar(20) NOT NULL

);

 

You can apply primary key constraint on email column using below statement.

 

ALTER TABLE student

ADD CONSTRAINT pk_student PRIMARY KEY (email);

mysql> CREATE TABLE student(
    -> first_name varchar(20) NOT NULL,
    -> last_name varchar(20) NOT NULL,
    -> email varchar(20) NOT NULL
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> ALTER TABLE student
    -> ADD CONSTRAINT pk_student PRIMARY KEY (email);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| first_name | varchar(20) | NO   |     | NULL    |       |
| last_name  | varchar(20) | NO   |     | NULL    |       |
| email      | varchar(20) | NO   | PRI | NULL    |       |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Can I drop a primary key constraint on existing table?

Yes, you can drop a constraint on a table using ‘DROP CONSTRAINT’ command.

 

ALTER TABLE student

DROP PRIMARY KEY;

mysql> ALTER TABLE student DROP PRIMARY KEY;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| first_name | varchar(20) | NO   |     | NULL    |       |
| last_name  | varchar(20) | NO   |     | NULL    |       |
| email      | varchar(20) | NO   |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

 

 

 

 

Previous                                                    Next                                                    Home