Constraints in MySQL

The MySQL DBMS offers a useful suite of methods to ensure data consistency.

Primary key constraints forbid duplicate values in one or more columns of a table. Foreign key constraints (using the InnoDB storage engine) ensure consistency of tuple (row) references across tables. Table check constraints are not supported, nor are general SQL assertions.

Keys

A primary key is a set of attributes whose values should be unique within a table. A table cannot have more than one set of attributes specified as the primary key, and the columns of a primary key cannot contain null values. Primary keys are optional and can be defined in CREATE TABLE or ALTER TABLE statements.

Non-primary candidate keys can be specified using unique constraints. The attributes for the unique constraint should also be specified as being not null. Here is an example of how primary key and unique constraints are specified in MySQL:

CREATE TABLE Product (
	ProdNum           INTEGER NOT NULL,
	Name              CHAR(100) NOT NULL,
	ProdVer           DECIMAL(4,2) NOT NULL,
	UnitPrice         DECIMAL(6,2),
	PRIMARY KEY(ProdNum),
	UNIQUE(Name, ProdVer));

Below we show how to specify a primary key by altering a table. This assumes that the Product table has already been created, but the primary key has not yet been put in:

ALTER TABLE Product ADD PRIMARY KEY (ProdNum);

Foreign Keys

A foreign key is a field (or fields) that points to the primary key of another table. The purpose of the foreign key is to ensure referential integrity of the data. In other words, only values that are supposed to appear in the database are permitted.

For example, say we have two, the above Product table that includes all product data, and an Order table that includes all product orders. The constraint here is that all orders must be associated with a product that is already in the Product table. In this case, we will place a foreign key on the Order table and have it relate to the primary key of the Product table. This way, we can ensure that all orders in the Order table are related to a product in the Product table. In other words, the Order table cannot contain information on a product that is not in the Product table.

Below we show how to specify the foreign key when creating the Order table:

CREATE TABLE Order (
	OrderNum	INTEGER,
	OrderTime	TIMESTAMP,
	ProdNum		INTEGER,
	Quantity	INTEGER,
	PRIMARY KEY (OrderNum),
	FOREIGN KEY (ProdNum) references Product(ProdNum));

Below we show how to specify a foreign key by altering a table. This assumes that the Order table has already been created, but the foreign key has not yet been put in:

ALTER TABLE Order
ADD FOREIGN KEY (ProdNum) REFERENCES Product(ProdNum);

There is one very important limitation regarding the foreign key constraint support in MySQL. While MySQL supports several storage engines for storing tables, only the InnoDB storage engine supports foreign key constraints. Therefore, for any table with foreign key constraints, you MUST use the InnoDB engine by adding the phrase "ENGINE = INNODB" at the end of the CREATE TABLE statement like the following.

CREATE TABLE Order (
	OrderNum	INTEGER,
	OrderTime	TIMESTAMP,
	ProdNum		INTEGER,
	Quantity	INTEGER,
	PRIMARY KEY (OrderNum),
	FOREIGN KEY (ProdNum) references Product(ProdNum)) ENGINE=INNODB;

Another (minor) difference from SQL92 is that even if you have a single-attribute foreign key, you cannot specify the foreign key simply as "REFERENCES table(attr)" immediately after the attribute definition (this is allowed in SQL92). All foreign keys should be specified separately as "FOREIGN KEY(attrs) REFERENCES table(attrs)".

Check Constraints

In SQL92, check constraints specify conditions that each tuple in the table should satisfy. For example, the following addition of a check constraint added to the Product table ensures that Quantity in Order table must be between 1 and 1,000:

CREATE TABLE Order (
	OrderNum	INTEGER,
	OrderTime	TIMESTAMP,
	ProdNum		INTEGER,
	Quantity	INTEGER,
	PRIMARY KEY (OrderNum),
	FOREIGN KEY (ProdNum) references Product(ProdNum),
        CHECK(Quantity >= 1 AND Quantity <= 1000));