SQL Table Constraint Video

This video discusses 4 common types of table constraints: NOT NULL, DEFAULT, UNIQUE, and CHECK.

Embed this video using the code below

Video Transcription

In a relational database system, only values that are consistent with the data type for that column are permitted. Many times, though, this is not enough, as there may be reasons to place additional constraints on what data can be entered into a table. In this video, we look at the following 4 types of constraints: NOT NULL, DEFAULT, UNIQUE, and CHECK.

The first one is the NOT NULL constraint. A column with a NOT NULL constraint cannot be NULL. This constraint is specified when we create the table by adding the "NOT NULL" declaration after the data type. If a column in the table has a NOT NULL constraint, and no value was entered for this column when we insert data, an error will result as the NOT NULL constraint is violated. In the example shown here, STUDENT_NAME has the NOT NULL constraint, meaning that this column cannot be NULL.

The second constraint we look at is the DEFAULT constraint. When an INSERT INTO statement does not specify a value for a column that has the DEFAULT constraint, the database system will use the value specified by the DEFAULT constraint as the value. The DEFAULT constraint is specified when we create the table by adding the word "DEFAULT" and then the default value after the data type. For example, the following SQL statement:

CREATE TABLE EMPLOYEE_SALARY
(EMPLOYEE_ID INTEGER,
SALARY FLOAT DEFAULT 10000)

sets the default value of the SALARY column to 10000.

Next we attempt to insert data into the EMPLOYEE_SALARY table with the following SQL statement:

INSERT INTO EMPLOYEE_SALARY (EMPLOYEE_ID) VALUES (50);

In the new row that’s added, the SALARY column would be 10000 even though no value was specified for the SALARY column in the INSERT INTO statement. This is because 10000 is the default value for the SALARY column.

The third constraint is the UNIQUE constraint. When a UNIQUE constraint is applied to a column, that column cannot contain duplicate values. The UNIQUE constraint is specified when we create the table by adding the word "UNIQUE" after the data type. An attempt to insert data that violates this constraint will result in an error. In the example here, STUDENT_ID has the UNIQUE constraint and therefore all rows must contain a different value for STUDENT_ID.

The fourth constraint is the CHECK constraint. A CHECK constraint is used to make sure that the data entered satisfies a certain criteria, thus ensuring data quality. The CHECK constraint is specified when we create the table by adding the word "CHECK" and then the condition after the data type. For example, let’s say we have a STUDENT_GRADE table that is generated by the following SQL:

CREATE TABLE STUDENT_GRADE
(STUDENT_ID INTEGER CHECK (STUDENT_ID > 100),
GRADE INTEGER);

and we subsequently attempt to execute the following INSERT INTO statement:

INSERT INTO STUDENT_GRADE (STUDENT_ID, GRADE) VALUES (10,80);

it will result in an error because a value of 10 for STUDENT_ID violates the CHECK constraint, which requires that STUDENT_ID be greater than 100.

List of SQL Video Tutorials