What are the SQL Constraints?
- SQL constraints are rules that limit the types of data that can be inserted or updated into a table.
- Constraints are used to enforce data integrity and ensure the accuracy and consistency of data.
- Constraints could be either on a column level or a table level.
- The column-level constraints are applied only to one column, whereas the table-level constraints are applied to the whole table.
Types of SQL Constraints:
The following are the most used constraints that can be applied to a table.
- NOT NULL
- PRIMARY KEY
- FOREIGN KEY
NOT NULL Constraint:
This constraint ensures that a column does not contain any null values. When we apply this constraint to a column, it means that the column must have a value in every row. This constraint can be specified using the ALTER and MODIFY statements.
The query below generates a table Student with the fields ID and NAME set to NOT NULL. That is, we must provide values for these two fields each time we insert a new row.
CREATE TABLE Student ( ID int(6) NOT NULL, NAME varchar(10) NOT NULL, ADDRESS varchar(20) );
We can do so by using ALTER command:
ALTER TABLE Student MODIFY ID int NOT NULL , NAME varchar NOT NULL;
This constraint ensures that each column or group of columns in a table has unique values. When you apply this constraint to a column, it means that no two rows in the table can have the same value in that column. This constraint can be specified using the ALTER and MODIFY statements.
CREATE TABLE Student ( ID int(6) NOT NULL UNIQUE, NAME varchar(10), ADDRESS varchar(20) );
The field ID in the above query is specified as UNIQUE.
This constraint is used to distinguish each row in a table. A primary key is a column or combination of columns that have been designated as the table’s unique identifier. Primary key constraints enforce uniqueness by preventing two rows in a table from having the same primary key value.
We are using the Primary Key constraint at Table level:
CREATE TABLE Student ( ID int(6) PRIMARY KEY, NAME varchar(10), ADDRESS varchar(20) );
We are using the Primary Key constraint at Column level:
ALTER table Student ADD PRIMARY KEY (ID);
This constraint is used to ensure that data in one table matches data in another. A foreign key is a column or column combination that refers to the primary key in another table. The foreign key constraint ensures that the values in the referencing column(s) match the values in the primary key column that is being referenced.
Consider a Sales table:
Consider a Sales_Person table:
The ‘Sale_Id’ column in the ‘Sales_person’ table refers to the ‘Sale_Id’ in the ‘Sales’ table.
The ‘Sale_Id’ in the ‘Sales’ table is the PRIMARY KEY.
The ‘Sale_Id’ in the ‘Sales_Person’ table is the FOREIGN KEY.
Notice that every value for ‘Sale_Id’ present in the ‘Sales_Person’ table is also available in the ‘Sale_Id’ column in the ‘Sales’ table. This is so due to the PRIMARY KEY – FOREIGN KEY relationship defined between the two tables.
Let us take a look at the query at Table level :
CREATE TABLE Sales_Person ( Sales_Person_Id int NOT NULL, Sales_Person_Name varchar(255), Sales_Person_Location varchar(255), Sale_Id int NOT NULL, PRIMARY KEY (Sales_Person_Id), FOREIGN KEY (Sale_Id) REFERENCES Sales(Sale_Id) );
At column level:
ALTER TABLE Sales_Person ADD FOREIGN KEY (Sale_Id) REFERENCES Sales(Sale_Id);
This constraint ensures that the values in a column meet certain criteria. When we apply this constraint to a column, it means that the data in that column must meet a specific condition or set of conditions.
CHECK constraint at Table Level:
CREATE TABLE Student ( ID int(6) NOT NULL CHECK(ID > 0), NAME varchar(10), ADDRESS varchar(20) );
CHECK constraint at Column Level:
ALTER table Student ADD CHECK(ID > 0);
Note: also read about SQL: AND & OR operators
Please follow me to read my latest post on programming and technology if you like my post.
Staying up to the mark is what defines me. Hi all! I’m Rabecca Fatima a keen learner, great enthusiast, ready to take new challenges as stepping stones towards flying colors.