When writing SQL statements Constraints enforce limits to the data or type of data that can be inserted/updated/deleted to or from a table.
SQL constraints are used to specify rules or conditions that are defined to maintain the integrity, accuracy, and consistency of the data stored in the database. for data in a table.
Types of Constraints
A NOT NULL Constraint
A Unique Constraint
A Primary Key Constraint
A Foreign Key Constraint
A (Table) Check Constraint
A Default Constraint
A Referential Integrity Constraint
A NOT NULL Constraint
By default, a column can hold NULL values.
The NOT NULL constraint enforces a column to NOT accept NULL values.
This enforces a field to always contain a value, which means that you cannot insert a new record, or update a record without adding a value to this field.
A Primary Key Constraint
The PRIMARY KEY constraint ensures that a specific column or combination of columns in a table uniquely identifies each row.
Primary keys must contain UNIQUE values 9no duplicates), and cannot contain NULL values.
A table can have only one primary key, which may consist of single or multiple fields. More than one field in a primary key is also called as Composite primary key.
A Unique Constraint
The UNIQUE constraint uses to ensure that all values in a column are different, so that values in a specific column or combination of columns are unique across all rows in the table.
Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns.
A PRIMARY KEY constraint automatically has a UNIQUE constraint. But unlike a primary key, unique constraints can allow null values.
However, it can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.
A Foreign Key Constraint
A FOREIGN KEY is a key used to establishes a relationship between two tables by connecting/linking two tables together.
A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table.
So foreign key constraint is used to create foreign key between two tables.
It helps maintain data integrity across related tables.
Check Constraint
The CHECK constraint is used to limit the value range that can be placed in a column, which means it allows only certain values for the field.
Ex: CHECK (Age>=18)
Default Constraint
The DEFAULT constraint is used to provide a default value for a column if no value is explicitly provided during data insertion.
The default value will be added to all new records IF no other value is specified.
Referential Integrity Constraint
This is a combination of primary key and foreign key constraints that ensures that the relationships between tables are maintained correctly.
It prevents actions that could result in orphaned or inconsistent data.
