Primary Key SQL

In this tutorial we will take a walk through various concepts that are
essential when working with SQL, in any of its versions.

Primary Key

A PRIMARY KEY is a column or group of columns that uniquely identifies
each row in a table.

You can create a primary key for a table using the PRIMARY KEY

No two rows in a table can have the same primary key.

Examples of primary keys are NID (associated with a person), passport
number, a MAC address, a public IP address, or ISBN (associated with a

Telephone directories and dictionaries cannot use names or words or Dewey
decimal numbers as candidate keys, because they do not uniquely identify
telephone numbers or words.

Primary keys are used with the SQL standard primarily for programmer

The primary key allows the relationships of the table that has the
primary key with other tables that are going to use the information of
this table, this, for an entity-relationship model.

Each table can contain only one primary key.

All columns participating in the primary key must be defined as NOT NULL.

However, SQL Server automatically sets the NOT NULL constraint for all
primary key columns if the NOT NULL constraint is not specified for these

SQL Server also automatically creates a unique clustered index (or
nonclustered index if specified as such) when it creates a primary key.

Ways to create a primary key

Primary keys can be specified when the table is created (using CREATE
TABLE) or by changing the existing structure of the table (using ALTER

You can also use the tools of each SQL database manager, for example SSMS
or MySQL Workbench.

The syntax for each type of Database is as follows:


CREATE TABLE transit.passenger
(PassNUMB integer,
Last_Name varchar(30),
First_Name varchar(30),


CREATE TABLE transit.dbo.passenger
(PassNUMB integer PRIMARY KEY,
Last_Name varchar(30),
First_Name varchar(30));

In this Passenger table, the PassNUMB column is the primary key column.

It means that the PassNUMB column contains unique values.

Table with two columns as a primary key

The following statement creates a new table named sales.participants
whose primary key consists of two columns:

CREATE TABLE cohort.students(subject_id int,  student_id
int,  PRIMARY KEY(subject_id, student_id));

In this example, the values ​​in the subject_id or student_id column can
be duplicates, but each combination of values ​​in both columns must be

Normally, a table always has a primary key defined at creation time.

When an already created table does not contain a primary key

However, sometimes an existing table may not have a defined primary key.

In this case, you can add a primary key to the table using the ALTER
TABLE statement.

The following statement creates a table without a primary key:

VARCHAR(255), start_date DATE NOT NULL,  durationDEC(5,2));

To make the event_id column the primary key, use the following ALTER
TABLE statement:


It is important to consider that if the table already has
data, before modifying the event_id column as the primary key, you must
ensure that the values ​​in event_id are unique.

Leave a Reply

Your email address will not be published.