SQL Primary Key Constraint

0
17

sqltuts

This constraint is used to uniquely define an identify any entry in the table. There are certain conditions like a primary key value can’t be NULL and it must be UNIQUE. There can be only one primary key in a table.

  Example of PRIMARY KEY Constraint on CREATE TABLE

 

  1. CREATE TABLE Employee
  2. (
  3. Id int,
  4. Name varchar(50) NOT NULL,
  5. Address varchar(50),
  6.  
  7. PRIMARY KEY (Id)
  8. );

 

 

Example of PRIMARY KEY Constraint on ALTER TABLE

 

  1. ALTER TABLE Employee
  2.  
  3. ADD PRIMARY KEY (E_Id)

 

Example of DROP a PRIMARY KEY Constraint

 

  1. ALTER TABLE Employee
  2.  
  3. DROP PRIMARY KEY

 

Sample Version

  1. CREATE TABLE table_name
  2.  
  3. (
  4.  
  5. column1 column_definition,
  6.  
  7. column2 column_definition,
  8.  
  9. ...

 

  1. CONSTRAINT [constraint_name]
  2.  
  3. PRIMARY KEY [ USING BTREE | HASH ] (column1, column2, ... column_n)
  4.  
  5. );

 

 

Example

Let’s look at an example of how to create a primary key using the CREATE TABLE statement in MySQL.

  1. CREATE TABLE contacts( contact_id INT(11) NOT NULL AUTO_INCREMENT,  Lname VARCHAR(10) NOT NULL,  Fname VARCHAR(15) NOT NULL,  birthday time,   CONSTRAINT contacts_pk PRIMARY KEY (contact_id));

 

We could also create a primary key with more than one field as in the example below:

  1. CREATE TABLE contacts( Lname VARCHAR(10) NOT NULL,  Fname VARCHAR(15) NOT NULL,  birthday time,  CONSTRAINT contacts_pk PRIMARY KEY (Lname, Fname));

 

 

CREATE PRIMARY KEY – USING ALTER TABLE STATEMENT

You can create a primary key in MySQL with the ALTER TABLE statement.

  1. ALTER TABLE table_name  ADD CONSTRAINT [ constraint_name ]    PRIMARY KEY [ USING BTREE | HASH ] (column1, column2, ... column_n)

table_name

The name of the table to modify.

constraint_name

The name of the primary key.

column1, column2, … column_n

The columns that make up the primary key.

Facebook Comments
(Visited 18 times, 1 visits today)

LEAVE A REPLY

Please enter your comment!
Please enter your name here