SQL Basics | DDL: Data Definition Language | Primary Key

Tags:

Each value of a primary key is unique within a table. In other words, a primary key is used to uniquely identify each row of data in a table. A primary key can be a field from the original data or a generated field that is unrelated to the original data. A primary key can include one or more fields. When a primary key includes multiple fields, it is called a composite key.

A primary key can be set when creating a new table with CREATE TABLE, or when changing the structure of an existing table with ALTER TABLE.

Setting a primary key when creating a table

The following are examples of setting a primary key when creating a new table.

MySQL:

CREATE TABLE customer  (
  sid INTEGER,
  last_name VARCHAR(30),
  first_name VARCHAR(30),
  PRIMARY KEY (sid)
);

Oracle:

CREATE TABLE Customer (
  sid integer PRIMARY KEY,
  last_name VARCHAR(30),
  first_name VARCHAR(30)
);

SQL Server:

CREATE TABLE Customer (
  sid INTEGER PRIMARY KEY,
  last_name VARCHAR(30),
  first_name VARCHAR(30)
);

Setting a primary key when changing a table structure

The following are examples of setting a primary key when changing the structure of an existing table.

MySQL:

ALTER TABLE customer ADD PRIMARY KEY (sid);

Oracle:

ALTER TABLE customer ADD PRIMARY KEY (sid);

SQL Server:

ALTER TABLE customer ADD PRIMARY KEY (sid);

Before setting a primary key with ALTER TABLE, make sure the field used as the primary key is set to NOT NULL. In other words, the field must always contain data.