SQL Constraints. Foreign key constraints.

The syntax in CREATE TABLE

Constraint on single column:

CREATE TABLE table_name(
    column_name data_type(size) constraint_name,
    ...
);
        

you can define constraints on more than one column, by using constraint on table, like:

CREATE TABLE author(
    id int(4) key auto_increment,
    fname varchar(200),
    lname varchar(200),
    UNIQUE(fname,lname)
)
        

NOT NULL

Indicates that a column cannot store NULL value

CREATE TABLE book (
    id SMALLINT(3) UNSIGNED NOT NULL,
    ...
);
        

DEFAULT

Specifies a default value for a column

CREATE TABLE book (
    pub_year MEDIUMINT(4) DEFAULT NULL,
    ...
);
    

UNIQUE

Ensures that each row for a column must have a unique value

CREATE TABLE book (
    book_name VARCHAR(250) NOT NULL UNIQUE,
    ...
);
        

Some vendors, MySQL as well, allows a UNIQUE column to be set with DEFAULT NULL values, as by definition a NULL value is not equal to a NULL value. Anyway, it is not recommended to have UNIQUE and DEFAULT NULL column.

CHECK

self-learning section:

PRIMARY KEY

A combination of a NOT NULL and UNIQUE. Ensures that a column (or combination of two or more columns) have a unique identity which helps to find a particular record in a table more easily and quickly

CREATE TABLE book (
    id SMALLINT(3) UNSIGNED NOT NULL PRIMARY KEY,
    ...
);
        

What is a FOREIGN KEY?

A FOREIGN KEY in one table points to a PRIMARY KEY in another table.

FOREIGN KEY relationships involve a parent table that holds the central data values, and a child table with identical values pointing back to its parent.

The FOREIGN KEY clause is specified in the child table. The parent and child tables must use the same storage engine. They must not be TEMPORARY tables.

CREATE TABLE author (
    id SMALLINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
    ...
);
        

CREATE TABLE book (
    id SMALLINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
    author_id SMALLINT(3) UNSIGNED NOT NULL,
    ...
);
        

book.author_id will be used as a FOREIGN KEY to author.id

FOREIGN KEY Constraint

Ensure the referential integrity of the data in one table to match values in another table

MySQL supports FOREIGN KEY Constraint only for InnoDB tables

FOREIGN KEY Constraint

The syntax

[CONSTRAINT constraint_name]
FOREIGN KEY [fk_name] (index_columns)
  REFERENCES tbl_name (index_columns)
  [ON DELETE action]
  [ON UPDATE action]
  [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
        

The CONSTRAINT clause, if given, supplies a name for the foreign key constraint. If you omit it, InnoDB creates a name.

FOREIGN KEY indicates the indexed columns in the child table that must match index values in the parent table. fk_name is the foreign key ID. If given, it is ignored unless InnoDB automatically creates an index for the foreign key; in that case, fk_name becomes the index name.

REFERENCES names the parent table and the index columns in that table to which the foreign key in the child table refers. The index_columns part of the REFERENCES clause must have the same number of columns as the index_columns that follows the FOREIGN KEY keywords.

FOREIGN KEY Constraint: ON DELETE

ON DELETE specifies what happens to the child table when parent table rows are deleted.

For MySQL InnoDB tables ON DELETE NO ACTION and ON DELETE RESTRICT are the same as omitting the ON DELETE clause, as the foreign key constraints are checked immediately.

ON DELETE CASCADE causes matching child rows to be deleted when the corresponding parent row is deleted. In essence, the effect of the delete is cascaded from the parent to the child. This enables you to perform multiple-table deletes by deleting rows only from the parent table and letting InnoDB delete the corresponding rows from the child table.

ON DELETE SET NULL causes index columns in matching child rows to be set to NULL when the parent row is deleted. If you use this option, all the indexed child table columns named in the foreign key definition must be defined to permit NULL values

FOREIGN KEY Constraint: ON UPDATE

ON UPDATE enables you to specify what happens to the child table when parent table rows are updated.

If ON UPDATE clause is not present, the default behaviour is to reject any inserts or updates in the child table that result in foreign key values that don’t have any match in the parent table index, and to prevent updates to parent table index values to which child rows point.

The possible action values are the same as for ON DELETE and have similar effects.

FOREIGN KEY Constraint: Guidelines

You must create the parent table index explicitly before defining the foreign key relationship.

InnoDB automatically creates an index on foreign key columns (the referencing columns) in the child table if the CREATE TABLE statement does not include such an index.

The automatically created index will be a non unique index and will include only the foreign key columns.

You should define the index in the child table explicitly if you want it to be a PRIMARY KEY or UNIQUE index, or if it should include other columns in addition to those in the foreign key.

Corresponding columns in the parent and child indexes must have compatible types.

Corresponding character columns must be the same length.

Corresponding integer columns must have the same size and same range (signed or unsigned).

For string columns, you must index the entire column, not just a leading prefix of it.

FOREIGN KEY Constraint: Examples

books_db schema

View or Download

books_db sample data

View or Download

Now let's delete an author from the author table:

DELETE FROM author WHERE lname='Vonnegut' and fname='Kurt';
            

And check what happens with the books in book table:

SELECT author.fname, author.lname, book_name FROM book, author WHERE book.author_id=author.id;
            

Yes, we see (view) that all Vonnegut's books records are automatically deleted from book table, because of the constraint we've set.

FOREIGN KEY Constraint: Exercises

Create the next books_db schema and insert the books_db sample data

Set ON UPDATE constraint on fk_AuthorID which will force the MySQL to automatically update all rows in book table, when a corresponding row in author table has changed.

Set the author.id to be 100 for Charles Dodgson author row.

Check what happens with author_id in the respective rows in book table.