ITSourceCode.com

We Exist to Provide 100% Free Source Code and Tutorials

Custom Search

SQL Primary Key Constraint

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

Check Your Domain Ranking

Leave a Reply

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

ITSourceCode.com © 2016 Frontier Theme

Subscribe For Latest Updates

Signup for our newsletter and get notified when we publish new articles for free!