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
constraint.
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
book).
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
convenience.
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
columns.
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
TABLE).
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:
MySQL-MariaDB:
CREATE TABLE transit.passenger
(PassNUMB integer,
Last_Name varchar(30),
First_Name varchar(30),
PRIMARY KEY (PassNUMB));
SQL-Server:
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
unique.
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:
CREATE TABLE academy.events(event_id INT NOT NULL, event_name
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:
ALTER TABLE academy.dbo.events ADD PRIMARY KEY(event_id);
It is important to consider that if the academy.events 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.