SQL PRIMARY KEY Constraint

The PRIMARY KEY constraint is used to identify each row in a database table. It has some features:

  • It must be unique value
  • One table should have ONE primary key.
  • One table should ONLY have one primary key.

To create a table with PRIMARY KEY:

SQL Server

CREATE TABLE Users
(
    UserID int NOT NULL PRIMARY KEY,
    FirstName varchar(100) NOT NULL,
    LastName varchar(100) NOT NULL 
)

My SQL

CREATE TABLE Users
(
    UserID int NOT NULL,
    FirstName varchar(100) NOT NULL,
    LastName varchar(100) NOT NULL,
    PRIMARY KEY (UserID)
)

To set PRIMARY KEY by using ALTER TABLE:

SQL Server / MySQL / Oracle / MS Access:

    ALTER TABLE Users
    ADD PRIMARY KEY (UserID)

A PRIMARY KEY can be a combine of multiple columns:
SQL Server / MySQL / Oracle / MS Access:

    ALTER TABLE Users
    ADD CONSTRAINT pk_UserName PRIMARY KEY (FirstName, LastName)

To drop a PRIMARY KEY:

SQL Server:

    ALTER TABLE Users
    DROP CONSTRAINT pk_UserName 

My SQL:

    ALTER TABLE Users
    DROP PRIMARY KEY

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>