What is Table Constraints in MySQL? With Code Example

Table Constraints in MYSQL

Data integrity is ensured via the application of basic rules to columns or tables in relational databases such as MySQL, known as table constraints. They guarantee the consistency, accuracy, and dependability of the data kept in the database. By establishing limits, you stop inaccurate or inconsistent data entry by establishing requirements that the data must meet. With each database update, these rules are examined.

The limitations you highlighted and how they support data integrity are explained as follows:

NOT NULL

A column is prohibited from having a NULL value by the NOT NULL constraint. It makes certain that every record must have a value for that column. For fields that identify a record or maintain the data’s logical structure, this ensures that no information is missing. MySQL will fail to insert or update a row with a NULL value in a NOT NULL field.

Example of defining a column as NOT NULL during table creation:

CREATE TABLE products (
  id INT(4),
  name VARCHAR(50) NOT NULL
);

In this example, the name column is mandatory and cannot be left empty.

You can also add the NOT NULL constraint to an existing column using ALTER TABLE:

ALTER TABLE Persons
MODIFY Age int NOT NULL;

This statement modifies the Age column in the Persons table to disallow NULL values.

PRIMARY KEY

A table’s rows (or records) are uniquely identified by the PRIMARY KEY constraint. Providing a primary method of differentiating one tuple (row) from another is its goal. Every entry in a primary key needs to have a unique value, and crucially, NULL values cannot exist. A column or set of columns should be assigned as the primary key in every database table, while MySQL does not strictly enforce this requirement. There can be only one primary key in a table.

Flight numbers, item codes, employee IDs, patient numbers, admission numbers, and account numbers are a few examples of primary keys.

Both column-level and table-level definitions of a single-column primary key are possible in MySQL. You can only define a composite (multi-column) primary key at the table level.

Example of defining a single-column PRIMARY KEY at the column level:

CREATE TABLE Employee (
  EmpID integer NOT NULL PRIMARY KEY,
  Ename char(20) NOT NULL,
  StAge int(2)
);

Here, EmpID is declared as the primary key directly within its column definition.

Example of defining a single-column PRIMARY KEY at the table level:

CREATE TABLE Student (
    StCode char(3) NOT NULL,
    Stname char(20) NOT NULL,
    StAge int(2),
    PRIMARY KEY (StCode)
);

In this case, the primary key is defined separately after all column definitions.

Example of defining a composite PRIMARY KEY (requires table level definition):

CREATE TABLE road_test (
    internal_number int(11) NOT NULL,
    date date NOT NULL,
    customer_id int(11) NOT NULL,
    customer_comments varchar(255) NOT NULL,
    PRIMARY KEY (internal_number,date,customer_id)
);

This table uses a combination of internal_number, date, and customer_id as its primary key.

Data integrity depends on the primary key since it makes sure that every row can be uniquely identified and avoids duplicate records based on the value of the primary key. For performance and the physical storage of data, the main key is employed as the clustered index in the InnoDB storage engine.

UNIQUE

UNIQUE constraints guarantee unique values in a column or set of columns. UNIQUE constraints maintain uniqueness, however unlike primary keys, a table can have many constraints and accept NULL values in columns with them.

The UNIQUE constraint ensures that individual data points, not the primary identifier, are distinct across all records, maintaining data integrity. AUTO_INCREMENT must be a primary or unique key.

Example of defining a column as UNIQUE during table creation:

CREATE TABLE Student (
  StCode char(3) NOT NULL PRIMARY KEY,
  Stname char(20) NOT NULL,
  StAdd varchar(40),
  AdmNo char(5) UNIQUE,
  StAge integer CHECK (StAge>=5)
);

Here, AdmNo is defined as UNIQUE, ensuring no two students have the same Admission Number.

Example of adding a UNIQUE constraint to an existing column:

ALTER TABLE Persons
ADD UNIQUE (ID);

This adds a unique constraint to the ID column in the Persons table.

DEFAULT

During an INSERT operation, if no specified value is supplied, the DEFAULT constraint offers a default value for a column. When creating a new record, MySQL will insert the default value for a column with a DEFAULT constraint. Unless designated as NOT NULL, a column’s default value is NULL.

This restriction can be used to automatically provide common values, prevent NULLs when they are undesirable but not strictly prohibited, or guarantee that specific fields have a reasonable backup value.

Example of setting a default value using ALTER TABLE:

ALTER TABLE Persons
ALTER City SET DEFAULT 'Sandnes';

This sets ‘Sandnes’ as the default value for the City column in the Persons table.

You can also specify a default value during table creation:

CREATE TABLE tblDoctors (
  DoctorID int(11) NOT NULL AUTO_INCREMENT,
  Title varchar(25) DEFAULT Dr,
  FirstName varchar(20) DEFAULT NULL,
  -- ... other columns
);

Here, ‘Dr’ is the default for Title, and NULL is explicitly set as the default for FirstName

CHECK

When a value is placed into a column, the CHECK constraint makes sure that it complies with a particular condition or rule. This enables domain constraints to be applied in addition to basic data type limitations. They state that it is “not included in the syllabus (recommended for advanced learning)”.

Example of defining a CHECK constraint:

CREATE TABLE Student (
  StCode char(3) NOT NULL PRIMARY KEY,
  Stname char(20) NOT NULL,
  StAdd varchar(40),
  AdmNo char(5) UNIQUE,
  StAge integer CHECK (StAge>=5)
);

This Student table definition’s CHECK (StAge>=5) constraint guarantees that the value in the StAge column is always larger than or equal to 5.

FOREIGN KEY

Table referential integrity is maintained by using the FOREIGN KEY constraint. It connects a list of columns in one table (the child table or referring table) to a list of columns in another table (the parent table or referenced table). The parent table’s PRIMARY KEY is usually referenced by the foreign key in the child table.

A foreign key is used to make sure that associations between tables are legitimate. It stops operations that would break table-to-table connections. For example, it guarantees that a value that appears in the child table’s foreign key column appears in the parent table’s referenced main key column. This keeps the child table from having orphaned records. For integers and strings, corresponding columns in the foreign key and referenced key must have comparable data types, such as size, sign, and character set/collation. The InnoDB storage engine in MySQL supports foreign key limitations.

On deletion and on update clauses like CASCADE can define actions to take when a parent table record is deleted or updated. CASCADE involves automatically deleting or updating child table records when a parent table record is destroyed or modified.

Example of defining a FOREIGN KEY during table creation:

CREATE TABLE animal (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(64),
  species VARCHAR(64),
  age INT,
  habitat_id INT,
  FOREIGN KEY (habitat_id)
    REFERENCES habitat(id)
);

Here, habitat_id in the animal table is a foreign key referencing the id column (which is the primary key) in the habitat table.

Example of adding a FOREIGN KEY using ALTER TABLE with CASCADE actions:

ALTER TABLE tblAppointment
ADD CONSTRAINT FOREIGN KEY (DoctorID) REFERENCES tblDoctors (DoctorID)
ON DELETE CASCADE ON UPDATE CASCADE;

DoctorID in tblDoctors is referenced by this foreign key constraint in tblAppointment. It also requires tblAppointment to be notified of tblDoctors deletions or modifications that affect a linked DoctorID.

Disable Foreign Key Checks

Sometimes, during operations like bulk data imports or large-scale schema changes, you might need to temporarily disable foreign key checks to prevent errors when the data temporarily violates referential integrity. This can be done using the SET FOREIGN_KEY_CHECKS statement.

SET FOREIGN_KEY_CHECKS = 0; -- Disable foreign key checks
-- Perform operations that might violate foreign key constraints
SET FOREIGN_KEY_CHECKS = 1; -- Re-enable foreign key checks

It’s crucial to re-enable foreign key checks immediately after the operation to ensure data integrity is maintained.

In conclusion, MySQL table constraints like NOT NULL, PRIMARY KEY, UNIQUE, DEFAULT, CHECK, and FOREIGN KEY define and enforce data rules. These limitations dramatically improve database quality and reliability, providing accurate, consistent, and legitimate data.