Abilities to define and enforce database constraints is one of the advantages that a Database management system provides, along with ability to control redundancy, restrict unauthorized access and persistently store, converse complex object of programming data structures.
Database designer is responsible for identifying database constraints. This is crucial for maintaining the integrity, accuracy and consistency of data.
Constraints on database can be divided into three types:
1. Implicit constraints (inherent constraints):
Implicit constraints make a relational data table different from a file or a table.
Examples:
- no duplicate row exists in a relational data table is an implicit constraint.
- each value in a row is not divisible into components or in composite type of value ( atomic value )
- unknown value is represented by NULL data type
2. Explicit constraints
This constraints are expressed when a schema is created. They presents in most database applications.
Domain constraints
Ensures the data stored in a column is of a specified type (e.g., integer, varchar) or values of a column are within a certain range
CREATE TABLE members(
member ID INT,
age INT CHECK(age >=18 AND age <80));Key constraints
Within each relational data table, there is only one Primary Key which is a column or combination of columns uniquely identifies each row in the table. Primary Key can be selected from multiple candidate keys ( alternative keys that also uniquely identifies a row )
CREATE TABLE members(
member ID INT PRIMARY KEY,
Email VARCHAR(50) UNIQUE);Null constraints
This specify if null values are permitted in an attribute
CREATE TABLE members(
member ID INT PRIMARY KEY,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL);Entity Integrity constraints
No primary key value can be null. In other words, every row in a relational data table must have a non-null value for Primary Key. As we can see in above code, we do not need to specify NOT NULL for PRIMARY KEY attribute
Referential Integrity constraints
When a row in one table refers to another row in another table, the row in the other table must exist. In other words, when you define a Foreign Key in a table, that foreign key must match to the other entity’s Primary Key. This constraint ensures consistent relationships between tables. In SQL, we can specify the referential action in case that the foreign key is deleted or updated on the other table using keywords ON UPDATE or ON DELETE, follows by options such as CASCADE, RESTRICT,SET NULL.
CREATE TABLE students(
studentID INT PRIMARY KEY,
classID INT,
FOREIGN KEY(classID) REFERENCES classes(classID));3. Application-based constraints (Check constraints/ sematic integrity constraints)
This normally are not expressed in the database model but in the application programs.They ensure that attributes in a table meet specific requirements/conditions. In some cases, they can be specified as assertions in SQL.
For example, maximum working hours of an employee is 40 hours per week, salary number is always >0
CREATE TABLE workers(
workerID INT PRIMARY KEY,
weeklyhour INT CHECK(weekyhour <=40));