Table Constraints

1. The PRIMARY KEY, NOT NULL, and UNIQUE Constraints

In the prior tutorial, we made use of the PRIMARY KEY constraint, but it is important to break down what this actually means. The PRIMARY KEY constraint helps to uniquely identify a row within a table by enforcing entity integrity. In order to do so, it applies two other constraints of the NOT NULL constraint and the UNIQUE constraint.

The NOT NULL constraint ensures that a value has to exist for this column. When it is crucial for us to have the data, the NOT NULL constraint will not allow the attribute to not have a value. For instance, in our artist table, the name of the artist would be a good example of one that we would not want to have any empty values for. Otherwise, we could not identify who the artist is.

In the employee table, many of the columns could be set up to be required values using the NOT NULL constraint. For example, the hire_date would be one value that should always exist, as the hire_date would need to exist for any employee hired into the company. To add a NOT NULL constraint to a column, we would list it beside the data type like this:

CREATE TABLE contact(
        contact_id int PRIMARY KEY,
        username VARCHAR(50) NOT NULL,
        password VARCHAR(50) NOT NULL
);

Above, we would have both the username and password not permitted to have no value in the table.

The other constraint that the PRIMARY KEY constraint uses is the UNIQUE constraint. Similar to the NOT NULL constraint, it can be set up on its own. The UNIQUE constraint creates a unique index on the column. We can use the UNIQUE constraint to ensure that we do not have duplicate values in the column. The exception is that an attribute in the column could be empty. For example:

CREATE TABLE newsletter( email VARCHAR(50) UNIQUE );

In the example above, we would have a unique email but could potentially permit no value to be inserted. Of course, this is not ideal as it is the only attribute in the table.

With the primary key, it combines these two constraints so that the value cannot be empty with NOT NULL but also must be distinct with the UNIQUE constraint. By doing so, we can then use the primary key to uniquely identify any row within the table. In the same example with the newsletter, having the email be the primary key would avoid us having empty values.

CREATE TABLE newsletter( email VARCHAR(50) PRIMARY KEY );

A primary key does not have to be for a single column as it can combine multiple columns together as a special type of primary key called a composite key. A composite key takes the combination of two or more columns together to uniquely identify a row within a table.

2. The FOREIGN KEY Constraint

The FOREIGN KEY constraint is an important one to help define referential integrity. It is used to link two tables together. The foreign key in one table refers to the primary key in another table. For example, in the customer table, the customer_id is the primary key to uniquely identify each customer:

The invoice table contains a foreign key to the customer table:

From a coding standpoint, the foreign key would look like this in the invoice table creation:

CONSTRAINT invoice_customer_id_fkey FOREIGN KEY (customer_id) REFERENCES customer (customer_id);

To break it down, the invoice_customer_id_fkey is the constraint name. The column name is then identified with what column the foreign key is applied to. Then we define the table that it is referencing along with the parent key or primary key of that table.

What this means is that we cannot delete a customer from the customer table if at least one invoice row references that customer. This is the default behavior of a foreign key. If we tried to delete from the invoice table, we would get an error like:

In addition, if we tried to insert or update the customer_id to a value that didn’t exist in the customer table, we would get an error like this:

The foreign key enforced referential integrity to ensure that any value for an attribute/column must exist in the referenced table.

3. The DEFAULT and CHECK Constraints

The DEFAULT constraint is one that assigns a value to an attribute whenever a new row is added to a table if a value is not set for it. This can be useful to set a base value to an attribute. For example, in our track table:

We can set the unit_price default value to be 0.99 so that there is a default price set if none was passed, since most of the records have that value already set.

The CHECK constraint is one that can be used to validate data when an attribute is entered. It is one that we’ll cover in more detail in an upcoming tutorial. We could do checks of items such as:

• Check that the unit_price in the track table has a value of >=0 as there should be no negative price.
• Check that the hire_date in the employee table is greater than January 01, 2000 as that was the date that the company opened.
• Check that the customer's email has a standard email format.

try it

Your turn! Open the SQL tool by clicking on the LAUNCH DATABASE button below. Then enter in one of the examples above and see how it works. Next, try your own choices for which columns you want the query to provide.