Database Systems - Table Column Constraints - NOT NULL - DEFAULT - UNIQUE - CHECK - CONSTRAINT

You can have NULL values in any column in your table, except for primary key columns. Use the keywords NOT NULL in your CREATE TABLE statement to prevent the column from being NULL.

CREATE TABLE Movie (
ID SMALLINT UNSIGNED,
Title VARCHAR(100) NOT NULL,
ReleaseDate DATE,
Budget DECIMAL(10,2),
PRIMARY KEY(ID)
);

When you insert a row into a table, then data without a specified value will be set to NULL by default. Use the DEFAULT constraint to provide a different default value, other than NULL.

CREATE TABLE Movie (
ID SMALLINT UNSIGNED,
Title VARCHAR(100) DEFAULT ‘Unknown Title’,
ReleaseDate DATE,
Budget DECIMAL(10,2) DEFAULT 0.00,
PRIMARY KEY (ID)
);

To make sure that all column values are different, use the UNIQUE constraint. A constraint for a single column is called a column-level constraint, and for multiple columns is called a table-level constraint. MySQL helps to improve query performance by creating an index for each UNIQUE constraint.

CREATE TABLE Movie (
ID SMALLINT UNSIGNED,
Title VARCHAR(100),
Director VARCHAR(80) UNIQUE,
ReleaseYear INT,
Budget DECIMAL(10,2),
UNIQUE (Title, ReleaseYear),
PRIMARY KEY (ID)
);

If you need to specify a min or max limit, or range, for a columns value, use the CHECK constraint. For example check if year is greater than or equal to 2000 to verify that the Year is within this decade. Or check if age less than 20 to make sure a person’s age is valid. The constraint is violated if the check comes back FALSE or UNKNOWN for NULL values. This CHECK constraint may be either column-level or table-level constraint.

CREATE TABLE Person (
ID SMALLINT UNSIGNED,
Name VARCHAR(100) NOT NULL,
Age INT,
Gender VARCHAR(6) CHECK (Gender IN (‘Male’, ‘Female’, ‘Trans’)),
PRIMARY KEY(ID),
CHECK (Age LESS_THAN 120)
);
NOTE: replace LESS_THAN with angled bracket

You can even use the BETWEEN keyword to check that a value is between a specific range. The following example checks and only allows for teenagers in the database. This can be done similarly using greater than or equal to and less than or equal to operators.

CREATE TABLE Person (
ID SMALLINT UNSIGNED,
Name VARCHAR(100) NOT NULL,
Age INT CHECK (Age BETWEEN 13 AND 19)
PRIMARY KEY(ID)
);

The CONSTRAINT keyword lets you assign a name for your constraint. If you don’t use CONSTRAINT, then MySQL will just auto-assign a default name for you. Creating a name for your constraint is helpful for database programmers to identify why they are getting certain violations when they make any updates to database. You can create CONSTRAINTS when the table is created, or added later via the ALTER TABLE command.

MySQL supports a drop constraint with DROP INDEX ConstraintName for a UNIQUE constraint and DROP CHECK ConstraintName for a CHECK constraint.

CREATE TABLE Driver (
ID SMALLINT UNSIGNED,
Name VARCHAR(100) NOT NULL,
Age INT,
PRIMARY KEY(ID),
CONSTRAINT drivingAge CHECK (Age LESS_THAN_OR_EQUAL_TO 16)
);

In the example, if you try to insert a row where the Driver’s age is less than 16, then you will get a constraint violation.

Subscribe to Appficial for more programming videos coming soon. Also, don't forget to click LIKE and comment on the video if it helped you out!