An introduction to MySQL column and table constraints

Column constraints

Column constraints are constraints attached to a single column. They are used to determine whether a proposed value for a column is valid or not. Column constraints are evaluated after the input is validated against basic type requirements (like making sure a value is a whole number for int columns).

Column constraints are great for expressing requirements that are limited to a single field. They attach the constraint condition directly to the column involved. For instance, we could model the age restriction in a person table by adding a constraint after the column name and data type:

CREATE TABLE person (
    . . .
    age INT CHECK (age >= 0),
    . . .
);

This snippet defines a person table with one of the columns being an int called age. The age must be greater than or equal to zero. Column constraints are easy to understand because they are added as additional requirements onto the column they affect.

Table constraints

The other type of constraint is called a table constraint. Table constraints can express almost any restrictions that a column constraint can, but can additionally express restrictions that involve more than one column. Instead of being attached to a specific column, table constraints are defined as a separate component of the table and can reference any of the table's columns.

The column constraint we saw earlier could be expressed as a table constraint like this:

CREATE TABLE person (
    . . .
    age INT,
    . . .
    CHECK (age >= 0)
);

The same basic syntax is used, but the constraint is listed separately. To take advantage of the ability for table constraints to introduce compound restrictions, we can use the logical AND operator to join multiple conditions from different columns.

For example, in a banking database, a table called qualified_borrowers might need to check whether individuals have an existing account and the ability to offer collateral in order to qualify for a loan. It might make sense to include both of these in the same check:

CREATE TABLE qualified_borrowers (
    . . .
    account_number INT,
    acceptable_collateral BOOLEAN,
    . . .
    CHECK (account_number IS NOT NULL AND acceptable_collateral = true)
);

Here, we use the CHECK constraint again to check that the account_number is not null and that the loan officer has marked the client as having acceptable collateral by checking the acceptable_collateral column. A table constraint is necessary since multiple columns are being checked.

Now is a good time to mention that although we'll mainly be using the CREATE TABLE SQL command in these examples to create a new table, you can also add constraints to an existing table with ALTER TABLE. When using ALTER TABLE, new constraints cause the values currently in the table to be checked against the new constraint. If the values violate the constraint, the constraint cannot be added.

Default constraint names

When you create constraints using the syntax above, MySQL automatically chooses a reasonable, but vague, name. In the case of the qualified_borrowers table above, MySQL would name the constraint qualified_borrowers_chk_1:

INSERT INTO qualified_borrowers VALUES (123, false);

ERROR 3819 (HY000): Check constraint 'qualified_borrowers_chk_1' is violated.

This name gives you information about the table and type of constraint when a constraint is violated. In cases where multiple constraints are present on a table, however, more descriptive names are helpful to help troubleshooting.

Custom constraint names

You can optionally specify the name for your constraints by preceding the constraint definition with the CONSTRAINT keyword followed by the name.

The basic syntax for adding a custom name is this:

CONSTRAINT <constraint_name> <constraint_type_and_details>

For example, if you wanted to name the constraint in the qualified_borrowers table loan_worthiness, you could instead define the table like this:

CREATE TABLE qualified_borrowers (
    . . .
    account_number INT,
    acceptable_collateral BOOLEAN,
    . . .
    CONSTRAINT loan_worthiness CHECK (account_number IS NOT NULL AND acceptable_collateral = true)
);

Now, when we violate a constraint, we get our more descriptive label:

INSERT INTO qualified_borrowers VALUES (123, false);

ERROR 3819 (HY000): Check constraint 'loan_worthiness' is violated.

You can name column constraints in the same way:

CREATE TABLE teenagers (
    . . .
    age INT CONSTRAINT is_teenager CHECK (age >= 13 AND age <= 19),
    . . .
);

Check constraints

Check constraints are a general purpose constraint that allows you to specify an expression involving column or table values that evaluates to a boolean.

You've already seen a few examples of check constraints earlier. Check constraints begin with the keyword CHECK and then provide an expression enclosed in parentheses. For column constraints, this is placed after the data type declaration. For table constraints, these can be placed anywhere after the columns that they interact with are defined.

For example, we can create a film_nominations table that contains films that have been nominated and are eligible for a feature length award for 2019:

CREATE TABLE film_nominations (
    title VARCHAR(250),
    director VARCHAR(250),
    release_date DATE CHECK ('2019-01-01' <= release_date AND release_date <= '2019-12-31'),
    length INT,
    votes INT,
    CHECK (votes >= 10 AND length >= 40)
);

We have one column check restraint that checks that the release_date is within 2019. Afterwards, we have a table check constraint ensuring that the film has received enough votes to be nominated and that the length qualifies it for the "feature length" category.

When evaluating check constraints, acceptable values evaluate as being true. If the new record's values satisfy all type requirements and constraints, the record will be added to the table:

INSERT INTO film_nominations VALUES (
    'A great film',
    'Talented director',
    '2019-07-16',
    117,
    45
);

Query OK, 1 row affected (0.01 sec)

Values that evaluate to false produce an error indicating that the constraint was not satisfied:

INSERT INTO film_nominations VALUES (
    'A poor film',
    'Misguided director',
    '2019-10-24',
    128,
    1
);

ERROR 3819 (HY000): Check constraint 'film_nominations_chk_2' is violated.

In this case, the film has satisfied every condition except for the number of votes required. MySQL rejects the submission since it does not pass the final table check constraint.

Not null constraints

The NOT NULL constraint is much more focused. It guarantees that values within a column are not null. While this is a simple constraint, it is used very frequently.

CREATE TABLE national_capitals (
    country VARCHAR(250) NOT NULL,
    capital VARCHAR(250) NOT NULL
);

INSERT INTO national_capitals VALUES (
    NULL,
    'London',
);

ERROR 1048 (23000): Column 'country' cannot be null

CREATE TABLE national_capitals (
    country VARCHAR(250),
    capital VARCHAR(250),
    CHECK (country IS NOT NULL AND capital IS NOT NULL)
);

Unique constraints

The UNIQUE constraint tells MySQL that each value within a column must not be repeated. This is useful in many different scenarios where having the same value in multiple records should be impossible.

For example, columns that deals with IDs of any kind should, by definition, have unique values. A social security number, a student or customer ID, or a product UPC (barcode number) would be useless if they were not able to differentiate between specific people or items.

A UNIQUE constraint can be specified at the column level:

CREATE TABLE supplies (
    supply_id INT UNIQUE,
    name VARCHAR(250),
    inventory INT
);

They can also be specified as table constraints:

CREATE TABLE supplies (
    supply_id INT,
    name VARCHAR(250),
    inventory INT,
    UNIQUE (supply_id)
);

One of the advantages of using UNIQUE table constraints is that it allows you to perform uniqueness checks on a combination of columns. This works by specifying two or more columns that MySQL should evaluate together. The values in individual columns may repeat but the combination of values specified must be unique.

As an example, let's look back at the national_capitals table we used before:

CREATE TABLE national_capitals (
    country VARCHAR(250) NOT NULL,
    capital VARCHAR(250) NOT NULL,
);

If we wanted to make sure that we don't add multiple records for the same pair, we could add UNIQUE constraints to the columns here:

CREATE TABLE national_capitals (
    country VARCHAR(250) NOT NULL UNIQUE,
    capital VARCHAR(250) NOT NULL UNIQUE
);

This would ensure that both the countries and capitals are only present once in each table. However, some countries have multiple capitals. This would mean we may have multiple entries with the same country value. These wouldn't work with the current design:

INSERT INTO national_capitals VALUES (
    'Bolivia',
    'Sucre'
);
INSERT INTO national_capitals VALUES (
    'Bolivia',
    'La Paz'
);

ERROR 1062 (23000): Duplicate entry 'Bolivia' for key 'national_capitals.country'

If we still want to make sure we don't end up with duplicate entries while allowing for repeated values in individual columns, a unique check on the combination of country and capital would suffice:

CREATE TABLE national_capitals (
    country VARCHAR(250),
    capital VARCHAR(250),
    UNIQUE (country, capital)
);

Now, we can add both of Bolivia's capitals to the table without an error:

INSERT INTO national_capitals VALUES (
    'Bolivia',
    'Sucre'
);
INSERT INTO national_capitals VALUES (
    'Bolivia',
    'La Paz'
);

Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)

However, attempting to add the same combination twice is still caught by the constraint:

INSERT INTO national_capitals VALUES (
    'Bolivia',
    'Sucre'
);
INSERT INTO national_capitals VALUES (
    'Bolivia',
    'Sucre'
);

Query OK, 1 row affected (0.00 sec)
ERROR 1062 (23000): Duplicate entry 'Bolivia-Sucre' for key 'national_capitals.country'

Primary key constraints

The PRIMARY KEY constraint serves a special purpose. It indicates that the column can be used to uniquely identify a record within the table. This means that it must be reliably unique and that every record must have a value in that column.

Primary keys are recommended for every table but not required, and every table may only have one primary key. Primary keys are mainly used to identify, retrieve, modify, or delete individual records within a table. They allow users and administrators to target the operation using an identifier that is guaranteed by MySQL to match exactly one record.

Let's use the supplies table we saw before as an example:

CREATE TABLE supplies (
    supply_id INT UNIQUE,
    name VARCHAR(250),
    inventory INT
);

Here we've identified that the supply_id should be unique. If we wanted to use this column as our primary key (guaranteeing uniqueness and a non-null value), we could simply change the UNIQUE constraint to PRIMARY KEY:

CREATE TABLE supplies (
    supply_id INT PRIMARY KEY,
    name VARCHAR(250),
    inventory INT
);

This way, if we needed to update the inventory amounts for a specific supply, we could target it using the primary key:

INSERT INTO supplies VALUES (
    38,
    'nails',
    5
);
UPDATE supplies set inventory = 10 WHERE supply_id = 38;

Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

While many tables use a single column as the primary key, it is also possible to create a primary key using a set of columns, as a table constraint.

The national_capitals table is a good candidate to demonstrate this. If we wanted to create a primary key using the existing columns, we could replace the UNIQUE table constraint with PRIMARY KEY:

CREATE TABLE national_capitals (
    country VARCHAR(250),
    capital VARCHAR(250),
    PRIMARY KEY (country, capital)
);

Foreign key constraints

Foreign keys are columns within one table that reference column values within another table. This is desirable and often necessary in a variety of scenarios where tables contain related data. This ability for the database to easily connect and reference data stored in separate tables is one of the primary features of relational databases.

For example, you may have a orders table to track individual orders and a customers table to track contact info and information about your customers. It makes sense to put this information separately since customers may have many orders. However, it also makes sense to be able to easily link the records in these two tables to allow more complex operations.

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    first_name VARCHAR(250),
    last_name VARCHAR(250),
    phone_number BIGINT,
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    order_date DATE,
    customer BIGINT UNSIGNED,
    FOREIGN KEY (customer) REFERENCES customers(customer_id)
);

INSERT INTO orders VALUES (
    100,
    '2019-11-19',
    300
);

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`prisma`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer`) REFERENCES `customers` (`customer_id`))

INSERT INTO customers VALUES (
    300,
    'Jill',
    'Smith',
    5551235677
);
INSERT INTO orders VALUES (
    100,
    '2019-11-19',
    300
);

Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)

CREATE TABLE example (
    . . .
    FOREIGN KEY (column1, column2) REFERENCES other_table (column1, column2)
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    order_date DATE,
    customer INT,
    FOREIGN KEY (customer) REFERENCES customers(customer_id) ON DELETE CASCADE
);