What are constraints in SQL?
Every software application that consumes or generates data uses a database one way or another. This database is usually either an SQL database like PostgreSQL or NoSQL database like Firestore, MongoDB, and so on.
The SQL database, which is most commonly used, uses MySQL queries to perform
SQL Constraints
The MySQL databases hold user data inside tables. Each table has multiple rows and columns for different kinds of data. But how can the developer ensure that the data inside a particular column is correct? Or if the column should only save a number rather than any other data type?
The MySQL language allows the developer to enforce specific rules on particular columns of the database tables. These rules are called SQL Constraints. When a program tries to save the data provided by the user, these constraints come into action and check the accuracy of the data. If there is any violation, the whole action is aborted.
The SQL constraints are implemented on one of the two levels, column or table. The column level constraint is limited to only columns, whereas the table level sets a constraint on a complete table.
Common SQL Constraints
There are six commonly used SQL constraints:
PRIMARY KEY
The data MySQL database holds a single row called records. We can use the PRIMARY key SQL constraint to identify each record uniquely. The PRIMARY KEY constraint can not be null, and its value cannot repeat in the entire table.
CREATE TABLE Subject
(Subject_ID int NOT NULL PRIMARY KEY,
Subject_Name varchar(255) NOT NULL,);We can only have one PRIMARY KEY constraint per table.
FOREIGN KEY
The FOREIGN KEY constraint is used when we want to link two tables together based on their primary keys.
CREATE TABLE Student(Student_ID char(13)
PRIMARY KEY,Student_name
varchar(50),Father_name
varchar(50),Student_session int)
CREATE TABLE Subjects_Registered(
Course_code varchar(10) PRIMARY KEY,
Course_title varchar(20),
Credit_hours int,
Student_cnic char(13) FOREIGN KEY REFERENCES Student(Student_ID))
NOT NULL
As the name suggests, this constraint ensures that the column does not accept a null value.
CREATE TABLE Subject(
Subject_ID int NOT NULL PRIMARY KEY,
Subject_Name varchar(255) NOT NULL,
);As we can see, both the Subject_ID and Subject_Name have a NOT NULL constraint. It means their values cannot be empty.
UNIQUE
The UNIQUE constraint is similar to the PRIMARY KEY constraint. It ensures that all the values in a column are different and not duplicated.
CREATE TABLE Student(
Student_ID char(13) NOT NULL UNIQUE,
Student_name varchar(50),
Father_name varchar(50),
Student_session int
)CHECK
The CHECK constraint checks the incoming data against a defined condition. If the condition is true, the data is saved. Otherwise, the action is aborted.
CREATE TABLE Drivers(
ID int NOT NULL PRIMARY KEY,
Name varchar(30) NOT NULL,
Age int NOT NULL CHECK (AGE >= 18),
Address char(50),
);DEFAULT
With the DEFAULT constraint, we can define a default value that will be set whenever no value is provided.
CREATE TABLE Drivers(
ID int NOT NULL PRIMARY KEY,
Name varchar(30) NOT NULL,
Age int DEFAULT 18,
Address char(50),
);Free Resources