coderz.py

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
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY
• CHECK
• DEFAULT

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.

Example:

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;

UNIQUE Constraint:

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.

Example:

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.

Primary KeyConstraint:

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.

Example:

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);

Foreign KeyConstraint:

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.

Example:

Consider a Sales table:

Sale_Id	Sale_Amount	Vendor_Name	Sale_Date	Profit
123	100	ABC	01-12-2018	20
234	200	BCD	14-06-2019	55
345	500	CDE	22-03-2020	32
456	100	EFG	25-04-2021	40

Consider a Sales_Person table:

Sales_Person_Id	Sales_Person_Name	Sales_Person_Location	Sale_Id
1	Rahul	Kolkata	234
2	Sweta	Mumbai	456
3	Atul	New Delhi	123
4	Shruti	Mumbai	345

Here,

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);

CHECKConstraint:

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.

Example:

Using CHECK constraint at Table Level:

CREATE TABLE Student
(
ID int(6) NOT NULL CHECK(ID > 0),
NAME varchar(10),
ADDRESS varchar(20)
);

Using CHECK constraint at Column Level:

ALTER table Student ADD CHECK(ID > 0);

Note: also read about SQL: AND & OR operators

Follow Me

Please follow me to read my latest post on programming and technology if you like my post.

https://www.instagram.com/coderz.py/

https://www.facebook.com/coderz.py