1. SQL Tutorial
  2. SQL PRIMARY KEY Constraint

SQL PRIMARY KEY Constraint

Submitted by admin on Monday, March 28, 2011 - 22:44.

Every table must be identified with a unique key or the Primary Key. Primary Key uniquely identifies each record in the table. It makes searching of record faster.
A Primary Key cannot contain a NULL values and must be unique in every record.

Different DBMS have different implementations to set the primary keys, so let’s see each of them below.

SQL PRIMARY KEY Syntax

  1. CREATE TABLE TABLE_NAME
  2.   (
  3.   column_name1 data_type CONSTRAINT PRIMARY KEY,
  4.   column_name2 data_type   NULL/NOT NULL  ,
  5.   column_name3 data_type   NULL/NOT NULL,....)

Example For Oracle/SQL Server

  1. CREATE TABLE Users
  2.   (
  3.   ID INT NOT NULL PRIMARY KEY,
  4.   Firstname   VARCHAR(255)   NOT NULL,
  5.   Lastname   VARCHAR(255),
  6.   Salary FLOAT NOT NULL,
  7.   DeptID INT NOT NULL)

Users

ID Firstname Lastname Salary DeptID

Example For MySQL

  1. CREATE TABLE Users
  2.   (
  3.   ID   INT NOT NULL,
  4.   Firstname   VARCHAR(30)   NOT NULL,
  5.   Lastname   VARCHAR(30),
  6.   Salary FLOAT NOT NULL,
  7.   DeptID INT NOT NULL,
  8.   PRIMARY KEY (ID)
  9.   )

Users

ID Firstname Lastname Salary DeptID

We have set the primary key for the column ID in the users table

ALTER TABLE method

Example

  1. ALTER TABLE Users
  2.      ADD PRIMARY KEY (column_name)

Users

ID Firstname Lastname Salary deptnumber

TO REMOVE PRIMARY KEY

Example

  1. ALTER TABLE Users
  2. DROP PRIMARY KEY
Tags
sql tutorial
PRIMARY KEY Constraint

Add new comment

About text formats
  • 108 views