Constraints specify rules for the data in a table’s fields. We have already discussed two of the constraints, NOT NULL and DEFAULT. In this post we will look at four more, UNIQUE, PRIMARY KEY, FOREIGN KEY, and CHECK.
UNIQUE uniquely defines a record in a table. You can have many UNIQUE constraints in a table, and you can combine fields to create a UNIQUE constraint. If we created a table of friends and we wanted to make sure that the nickname we give each friend is unique, we can create a UNIQUE constraint when creating the table.
CREATE TABLE “friends” ( “id” INT GENERATED BY DEFAULT AS IDENTITY, “FirstName” VARCHAR(20), “LastName” VARCHAR(20), “Nickname” VARCHAR(40), UNIQUE (“Nickname”));
You can also name the constraint using the following format.
CREATE TABLE “friends” ( “id” INT GENERATED BY DEFAULT AS IDENTITY, “FirstName” VARCHAR(20), “LastName” VARCHAR(20), “Nickname” VARCHAR(40), CONSTRAINT “uc_nickname” UNIQUE (“Nickname”));
This naming convention works for all of the constraints. Just use the format “CONSTRAINT “constraint_name” <CONSTRAINT_TYPE> (<constraint_definition>)”.
The PRIMARY KEY also uniquely identifies a record, but also acts as the identity of the record. While you can have many UNIQUE constraints, you can have only one PRIMARY KEY. Unlike UNIQUE, a PRIMARY KEY cannot contain NULL. Just like UNIQUE, you can combine fields to create a PRIMARY KEY. The following SQL covers both PRIMARY KEY and FOREGIN KEY, which we will discuss next.
CREATE TABLE “phone” ( “number” VARCHAR(15) PRIMARY KEY, “friend” INT, FOREIGN KEY (“friend”) REFERENCES “friends”(“id”));
Above we define “number” as the PRIMARY KEY for the table. This keeps us from duplicating phone numbers.
FOREIGN KEY (reference above SQL)
The FOREIGN KEY in one table points to the PRIMARY KEY in another, creating a link between the two tables. They are used to prevent actions that would destroy the link between two tables. If you try to delete a record linked to another table through a FOREIGN KEY, you will get an SQL error. FOREIGN KEY also prevents invalid data in the field. If the corresponding PRIMARY KEY does not exist in the linked table, you will get an SQL error.
In the SQL above we create a FOREIGN KEY named “friend” that links the phone table to the friends table. The link is through the friends table’s “id” field linking to the phone table’s “friend” field. Note that the two types are compatible.
CHECK is used to restrict the value range for a single field. You can also limit the value of a field based on the value of another field. Let’s say we now want to track how many children each friend has.
ALTER TABLE “friends” ADD “children” INT;
We can now add a check to make sure the number entered is a positive number or zero.
ALTER TABLE “friends” ADD CHECK (“children” >= 0);
This CHECK will prevent the entry of a value less than 0.
Table constraints allow you to define rules for the control of data in the table’s records. UNIQUE and PRIMARY KEY define unique values for the record, but the PRIMARY KEY also acts as the identity of the record. A FOREIGN KEY links two tables together by attaching the FOREIGN KEY to the PRIMARY KEY in another table. CHECK limits the value of a field based on a condition or the value of another field.