Table constraints

When creating a table you can use the optional constraints. This provides more control over the data being entered into the tables. This lesson will discuss the following common constraints: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT, and AUTO_INCREMENT.

NOT NULL

The NOT NULL constraint indicates that the field within a record must have a value. By default, fields have just the NULL constraint set which means that field does not need to have a value set when creating a new record. If you want to ensure that a value is entered into a field for each record use NOT NULL. In the following code the last_name field is now required to have data.

CREATE TABLE student (
student_id INT,
first_name VARCHAR(30),
last_name VARCHAR(30) NOT NULL,
ssn INT,
age TINYINT UNSIGNED,
country VARCHAR(50),
program_id INT
);

UNIQUE

The UNIQUE constraint ensures that a record must have a unique value in a field column. For example, the ISBN number of a book cannot be reused on another book. This field would benefit from being UNIQUE. Using the same code from above, the ssn field must now have a unique value from any other record in the table, because of this it is often used with the NOT NULL statement.

CREATE TABLE student (
student_id INT,
first_name VARCHAR(30),
last_name VARCHAR(30) NOT NULL,
ssn INT NOT NULL UNIQUE,
age TINYINT UNSIGNED,
country VARCHAR(50),
program_id INT
);

PRIMARY KEY

The PRIMARY KEY constraint is a combination of  NOT NULL and UNIQUE. It ensures that a column (or combination of two or more columns) have an unique identity which helps to find a particular record in a table more easily and quickly. Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.

CREATE TABLE student (
student_id INT PRIMARY KEY,
first_name VARCHAR(30),
last_name VARCHAR(30) NOT NULL,
ssn INT NOT NULL UNIQUE,
age TINYINT UNSIGNED,
country VARCHAR(50),
program_id INT
);

Some database systems use an alternative way to set the PRIMARY KEY field. Below is the alternative method.

CREATE TABLE student (
student_id INT,
first_name VARCHAR(30),
last_name VARCHAR(30) NOT NULL,
ssn INT NOT NULL UNIQUE,
age TINYINT UNSIGNED,
country VARCHAR(50),
program_id INT,
PRIMARY KEY (student_id)
);

FOREIGN KEY

The FOREIGN KEY constraint is used to ensure the referential integrity of the data in one table to match values in another table This is used when a relationship is created between two tables. The FOREIGN KEY data in one table is often the PRIMARY KEY in the related table. The FOREIGN KEY constraint uses the REFERENCE keyword to link the tables. Using the example below, a program_id value entered into the student table (foreign key) must exist in the program table (primary key) in order for the record to be created.  When creating a FOREIGN KEY, keep in mind that both fields have to be the same data type, this includes signed or unsigned numbers.

CREATE TABLE student (
student_id INT,
first_name VARCHAR(30),
last_name VARCHAR(30) NOT NULL,
ssn INT NOT NULL UNIQUE,
age TINYINT UNSIGNED,
country VARCHAR(50),
program_id INT,
PRIMARY KEY (student_id),
FOREIGN KEY (program_id) REFERENCES program(program_id)
);

CHECK

The CHECK constraint ensures that the value in a column meets a specific condition. For example if a student must be at least 18 years old to be a student we could write the following code:

CREATE TABLE student (
student_id INT,
first_name VARCHAR(30),
last_name VARCHAR(30) NOT NULL,
ssn INT NOT NULL UNIQUE,
age TINYINT UNSIGNED,
country VARCHAR(50),
program_id INT,
PRIMARY KEY (student_id),
FOREIGN KEY (program_id) REFERENCES program(program_id),
CHECK (age >= 18)
);

DEFAULT

The DEFAULT constraint is used to specify a default value for a field when when no other value is given in the creating of a record. If most of the students in the table will be located in the United States we could make the country field default to United States using the following code:

CREATE TABLE student (
student_id INT,
first_name VARCHAR(30),
last_name VARCHAR(30) NOT NULL,
ssn INT NOT NULL UNIQUE,
age TINYINT UNSIGNED,
country VARCHAR(50) DEFAULT "United States",
program_id INT,
PRIMARY KEY (student_id),
FOREIGN KEY (program_id) REFERENCES program(program_id)
);

AUTO_INCREMENT

The AUTO_INCREMENT constraint is used with a number data type field to automatically increment the value by one. When a new record is created the database system automatically creates a value for the field. This is often used with the PRIMARY KEY constraint to ensure a unique value being applied to the field. The AUTO_INCREMENT starts counting at 1 and increments by 1. If you wish to change the starting value use AUTO_INCREMENT=n where n is the starting number in the command. Below is an example of using AUTO_INCREMENT with the primary key field.

CREATE TABLE student (
student_id INT AUTO_INCREMENT,
first_name VARCHAR(30),
last_name VARCHAR(30) NOT NULL,
ssn INT NOT NULL UNIQUE,
age TINYINT UNSIGNED,
country VARCHAR(50),
program_id INT,
PRIMARY KEY (student_id),
FOREIGN KEY (program_id) REFERENCES program(program_id)
);

Lesson Activities

  1. In a previous lesson we deleted the student table. We will now recreate it using the additional fields, data types, and constraints.
    Click to see code
    CREATE TABLE student(
    student_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(30),
    last_name VARCHAR(30),
    email VARCHAR(60),
    street VARCHAR(50),
    city VARCHAR(40),
    state CHAR(2) DEFAULT "VA",
    zip MEDIUMINT UNSIGNED,
    phone VARCHAR(20),
    birth_date DATE,
    sex ENUM('M', 'F'),
    date_enrolled TIMESTAMP,
    program_id INT UNSIGNED,
    faculty_id INT UNSIGNED,
    FOREIGN KEY (program_id) REFERENCES program(program_id),
    FOREIGN KEY (faculty_id) REFERENCES faculty(faculty_id)
    );
  2. Now lets view the structure of the student table. Use the command:
    Click to see code
    DESCRIBE student;

Questions

  • 0

    how can i use alter command to define a foreign key

    saidmuhama
    Reply

    I understand how to add constraints during table definition, thank you!!

  • 0

    Problem with foreign key

    Kurt
    Reply

    Returns error: ERROR 1215 (HY000): Cannot add foreign key constraint