All Constrains

What we covered in last chapters, including the Default values, not null and primary key are so called constraints in SQL. Constraints specify rules for data in a table.

Covered Constraints

We've already covered these constraints:

NOT NULL

NOT NULL can be used to ensure that the field can never be empty.

Example Usage:

MySQL

CREATE TABLE cars (platenumber INT NOT NULL);

DEFAULT

DEFAULT can be used to set a default value for a field which value is not set when inserting data into the table.

Example Usage:

MySQL

CREATE TABLE cars (model TEXT DEFAULT "unspecified");

PRIMARY KEY

PRIMARY KEY marks one column in a table to be the unique identifier. Column with the primary key is always unique and cannot be null.

Example Usage:

MySQL

CREATE TABLE cars (carId INT NOT NULL, PRIMARY KEY(carId));

FOREIGN KEY

FOREIGN KEY is used to form a relationship between two tables and to prevent actions that would destroy links between the linked tables. One or multiple fields can be made as a foreign key. Foreign key always refers to the primary key in another table.

Example Usage:

MySQL

CREATE TABLE cars (
    carId INT NOT NULL AUTO_INCREMENT,
    ownerId INT NOT NULL,
    FOREIGN KEY (ownerId) REFERENCES users(userId)
)

New Constraints

There are also 3 other constrains that we have not yet covered. These are the UNIQUE, CHECK, and INDEX.

UNIQUE

UNIQUE can be used to set field values to be unique.

Example Usage (Setting One Field as Unique):

MySQL

CREATE TABLE cars (
    carId INT NOT NULL AUTO_INCREMENT,
    platenumber VARCHAR(50) NOT NULL,
    carRegistrationNumber INT NOT NULL,
    UNIQUE (plateNumber),
    PRIMARY KEY(carId));

Example Usage (Setting Multiple Fields as Unique):

MySQL

CREATE TABLE cars (
    carId INT NOT NULL AUTO_INCREMENT,
    platenumber VARCHAR(50) NOT NULL,
    carRegistrationNumber INT NOT NULL,
    UNIQUE (plateNumber),
    UNIQUE (carRegistrationNumber),
    PRIMARY KEY(carId));

Testing the Behavior:

MySQL

INSERT INTO cars (plateNumber, carRegistrationNumber) VALUES
("ABC-123", 512325123),
("ABC-123", 012437272);

Should print out something like "Duplicate entry 'ABC-123' for key 'platenumber'".

CHECK

CHECK can be used to limit the value range that can be placed in a column. Check is a less commonly used constraint as the business logic of your application should be placed in your code instead of in your database.

Example Usage:

MySQL

CREATE TABLE cars (
    carYear INT NOT NULL,
    CHECK (carYear > 1900));

Testing the Behavior:

MySQL

INSERT INTO cars (carYear) VALUES (1850);

Should print out something like 'CONSTRAINT CONSTRAINT_1 failed for test.cars'.

INDEX

INDEX can be used to add index to one or multiple columns. Adding an index makes searching the field that has an index quicker. Indexes work in the background, users cannot see the indexes. When you set primary key, foreign key or unique for a column, it automatically gets applied an index.

Info

Index should also only be applied when required as having too many indexes slows down updates of your data.

Great place for adding indexes is each ID which are not keyed any way (like with primary keys). For example in your lookup table (like orders) you could have mealId and customerId, which you could both add the INDEX keywords for.

Example Usage:

MySQL

CREATE TABLE cars (
    carId INT NOT NULL AUTO_INCREMENT,
    platenumber INT NOT NULL,
    carRegistrationNumber INT NOT NULL,
    INDEX (plateNumber),
    PRIMARY KEY(carId));

Conclusion & Learn More

In this chapter we learned about all the constraints that can be used to set rules for data in a table: