SQL

SQL Keys

SQL Keys

This page covers the implementation of SQL keys.

The term keys refer to constraints. Constraints implement data rules inside a table.

There are two types of keys, primary key and foreign key. In addition, SQL has the constraints unique and identity . Both are relevant to the method of primary key.

The syntax for constraints is similar to data types (constraints go after data types). The methods within this tutorial are:

  • primary keys
  • foreign keys
  • unique
  • identity

Primary key

Primary keys play the role of an identifier.

The primary keys limit the specific column to only unique and not null values.

In other words, primary key columns must contain unrepeatable records (values). In addition, each table can have only one primary key.

As such, the primary key constraint is often used within the scope of id numbers (e.g. students ids, employees ids, and so on).

Create table with primary key

Let’s create a table “student_info”, with 4 columns: id, first name, last name, and age.

CREATE TABLE student_info (
	id int NOT NULL PRIMARY KEY,
	firstName varchar(50),
	lastName varchar(50),
	age int
	);

The id column now has a constraint primary key. And as such, can have only one student with unique id of 1, one student with unique id of 2, and so on.

Unique and not null are two additional constraints. Primary keys are automatically defined as unique, but not null have to be included.

Alter table with primary key

Primary keys can also be added through an alter statement.

Please note that a not null constraint must already exist (added in the initial table creation).

ALTER TABLE student_info
ADD PRIMARY KEY (id);

Foreign key

Foreign keys play the role of a link between tables.

Such keys create a relationship between column/s from one table to the primary key of another.

In many programming languages, there concept of parent/child relationship exists. The usage of foreign keys operates similarly.

A table with a primary key refers to a parent table, and a table with a foreign key refers to a child table.

The implementation of a foreign key constraint ensures that a value in the child table is updated only if it is related to the parent table.

Create table with foreign key

For instance, let’s create a second table “grades” with a primary key “id” and an additional column “studentID” as a foreign key (which links with the primary key “id” from the “student_info” table). 

CREATE TABLE grades (
	id int NOT NULL PRIMARY KEY,
	subjectName varchar(255),
	grade char(1),
	passed bit,
	studentID int FOREIGN KEY REFERENCES student_info(id)
	);
idsubjectNamegradepassedstudentID

The last column “studentID” is now linked with the primary key column “id” in the “student_info” table.

Alter table with foreign key

Foreign keys can also be added through an alter statement.

ALTER TABLE grades
ADD FOREIGN KEY (studentID) REFERENCES student_info(id);

Unique

The “unique” constraint is very similar to a primary key. Both offer uniqueness (no duplicates) to the column’s values.

The difference:

  • Primary keys has the “unique” constraint embedded. They can also apply once per table.
  • Unique constraints can apply more than once per table.

Create table with unique constraint

Let’s create a table with four columns (id, first name, last name, and age). The column “id” has the “unique” constraint.

CREATE TABLE student_info (
	id int NOT NULL UNIQUE,
	firstName varchar(50),
	lastName varchar(50),
	age int
	);

With the constraint “unique“, student ids can only have unique values (cannot have duplicates).

Alter table with unique constraint

Unique constraints can also be added through an alter statement.

 ALTER TABLE student_info
 ADD UNIQUE (id);

Identity

The “identity” constraint is closely related to the primary key.

It adds an automatic incremental generator for the particular column. As such, it generates unique values every time a new value is added.

For example: The “id” column contains the “identity” constraint. We insert new student information and the id automatically adds 1. We now add a second student data, and consequently the id of the second student increases to 2. And so on and so forth.

This constraint requires 2 values, seed and increment.

  • Seed value sets the start record value (e.g. 0 or 1)
  • Increment sets the incrementing step value (e.g. 0, 1, 2 or 0, 3, 6, 9)

Create table with identity constraint

Let’s create a table with 4 columns. We add the constraint to the “id” column.

CREATE TABLE student_info (
	id int IDENTITY(1, 1) NOT NULL PRIMARY KEY,
	firstName varchar(50),
	lastName varchar(50),
	age int
	);

The automatic incremental generator of student ids starts from 1 and increments with 1 step for each new record.

Next: SQL Constraints

Related Posts