Types of Constraints in SQL | NOT NULL, UNIQUE And PRIMARY KEY
Summary: You will learn in this article, the various types of Constraints in SQL such as SQL NOT NULL, UNIQUE, SQL PRIMARY KEY, SQL FOREIGN KEY, SQL CHECK, DEFAULT and CREATE INDEX. These types of Constraints in SQL play an important role, Let’s understand all types of Constraints in SQL with syntax and example. I hope you will enjoy it!
What is constraints in sql?
These Constraints are rules and regulations which are applied to the data. They are used for the purpose of validating data. Furthermore, it specifies conditions that the data must satisfy.
These SQL constraints are simply used to specify rules for the data in a table. it can be used to limit the type of data that can be stored in a specific or particular column of a table. Constraints make sure that the data stored is valid. Therefore, this ensures the reliability & accuracy of the data in the database.
Constraints in SQL could be either at the column level or at the table level.
The Column Level:- The Column level constraints are applied only to one column
The Table Level:- Whereas the table level constraints are applied to the entire table.
How Many Types of Constraints are Present in SQL Server?
The given below following are some of the most commonly used constraints available in SQL.
- NOT NULL
- UNIQUE
- PRIMARY KEY
- FOREIGN KEY
- CHECK
- DEFAULT
1) NOT NULL Constraint
This constraint can’t store a null value in a column. That is, if a column is specified as NOT NULL then we will not be able to store null in this particular column anymore.
By default, NULL values can store in a column. suppose that if we don’t enter any value in a particular column, it will store NULL if the default value has not been specified. Therefore, the NULL value is omitted in different operations in a database. Thus, to ensure that a column doesn’t hold a NULL value, NOT NULL constraint is used.
When NOT NULL constraint applied to a column ensures that the particular or specific column always contains a value, i.e., we can’t insert a new record, or update a record while not providing a value for that field.
NOT NULL Constraint Example
|
1 2 3 4 5 6 |
CREATE TABLE Students ( Student_Id INT(3) NOT NULL, Student_Name VARCHAR(50) NOT NULL, Student_Father VARCHAR(50), Student_Address VARCHAR(70), ); |
As per above SQL Query, it will create a table Students with fields Student_Id, Student_Name, and Student_Father, Student_Address. Here we can see that the Student_Id, and Student_Name, are specified with NOT NULL constraint that means they can’t contain NULL value while Student_Father and Student_Address may contain NULL value.
Suppose that if Students table has already been created, then to add a NOT NULL constraint to the Student_Id and Student_Name column use following syntax:
|
1 |
ALTER TABLE Students MODIFY Student_Id INT(3) NOT NULL; |
|
1 |
ALTER TABLE Students ADD [CONSTRAINT notNullStudent] NOT NULL(Student_Id); |
|
1 |
INSERT INTO Students(Student_Id,Student_Name) VALUES(111,"Kapil Doe"); |
This SQL query will store values in specified fields and store NULL in Student_Father and Student_Address field. Therefore, As per SQL statement Student_Father and Student_Address field doesnot have a NOT NULL constraint, thus no error will be generated.
2) UNIQUE Constraint
UNIQUE constraint in SQL ensures that all the values stored in a column are totally different from each other. Furthermore, the UNIQUE Constraint prevents two records from having similar values in a column, which means that the values must not be repeated.
We can the UNIQUE SQL constraint in a single column or multiple columns.
This constraint helps us to uniquely identify each row in the table means that for a particular column, all the rows should have unique values. Furthermore, We can have more than one UNIQUE column in a table.
UNIQUE Constraint Example
|
1 2 3 4 5 6 |
CREATE TABLE Students ( Student_Id INT(3) NOT NULL, Student_Name VARCHAR(50) NOT NULL, Student_Father VARCHAR(50), Student_Address VARCHAR(70), ); |
The above sql statement will create a table Students(Student_Id, Student_Name, Student_Father and Student_Address). The Student_Id has a UNIQUE constraint thus each row of Student_Id field will have a different value. Furthermore, if the Students table has already been created, then to add a UNIQUE constraint to the Student_Id column:
|
1 |
ALTER TABLE Students MODIFY Student_Id INT(3) UNIQUE; |
OR
|
1 |
ALTER TABLE Students ADD [CONSTRAINT myUniqueConstraint] UNIQUE(Student_Id); |
To Drop a SQL UNIQUE constraint, then use the following below SQL query:
|
1 |
ALTER TABLE Students DROP [UNIQUE Student_Id | CONSTRAINT myUniqueConstraint]; |
3) PRIMARY KEY Constraint
A primary key constraint plays an important role in the database table, it uniquely identifies each row/record in a database table. Furthermore, Primary keys must contain unique values. A PK-primary key column can’t have NULL values.
Therefore, a table can have only one PK-primary key, which may consist of single or multiple fields. When you will used multiple fields as a primary key, they are known as composite keys.
The field with a PK-PRIMARY KEY constraint can be used to uniquely identify or recognized each record of a table in several database operations.
Furthermore, if a field in a table is the PK (primary key), then the field will not be able to contain NULL values similarly as all the rows should have unique values for this field. Thus, in other words, we can say that this is the combination of NOT NULL and SQL UNIQUE constraints.
PRIMARY KEY Constraint Example
|
1 2 3 4 5 6 |
CREATE TABLE Students ( Student_Id INT(3) PRIMARY KEY, Student_Name VARCHAR(50) NOT NULL, Student_Father VARCHAR(50), Student_Address VARCHAR(70), ); |
The above SQL query will create a table Students(Student_Id, Student_Name, Student_Father and Student_Address) with Student_Id field as PRIMARY KEY constraint.
Furthermore, to create a PRIMARY KEY constraint on the "Student_Id" column when Students table already exists, then use the following below SQL syntax:
|
1 |
ALTER TABLE Students MODIFY Student_Id INT(3) PRIMARY KEY; |
|
1 |
ALTER TABLE Students ADD [CONSTRAINT PK_Student_Id] PRIMARY KEY (Student_Id); |
To Drop PRIMARY KEY Constraints from a table, use the following syntax:
|
1 |
ALTER TABLE Students DROP [PRIMARY KEY | CONSTRAINT PK_Student_Id]; |
4) FOREIGN KEY Constraint
FK-Foreign Key Constraint is used to link or connect two tables. A foreign key can establish a relationship between the data in two tables.
A Foreign Key (FK) is a column or a combination of columns whose values match a Primary Key (PK) in a different table.
The table containing the FOREIGN KEY is known as the child table, and the table containing the candidate key is known as the parent table.
This key is used to enforce referential integrity. It is used to prevent actions that would destroy links or connections between tables. Furthermore, it also prevents invalid data from being inserted into the FK-foreign key column, because it has to be one of the values contained within the table it points to.
This Foreign Key is a field in a table that can uniquely identify each row of another table. thus, that is this field points to the PK-primary key of another table. This sometimes creates a kind of link between the tables.
FOREIGN KEY Constraint Example
|
1 2 3 4 5 6 7 |
CREATE TABLE Students ( Student_Id INT(3) PRIMARY KEY, Student_Name VARCHAR(30), Student_Father VARCHAR(50), Student_Address VARCHAR(70), Dept_Id INT(6) REFERENCES Department(Dept_Id) ); |
Dept_Id as FOREIGN KEY which links or connect Students table to Department table.
Therefore, to create a FK-FOREIGN KEY constraint on the “Dept_Id” column when Students table already exists, then use the following below SQL syntax:
|
1 |
ALTER TABLE Students ADD [CONSTRAINT fKey] FOREIGN KEY (Dept_Id) REFERENCES Department(Dept_Id); |
To Drop FOREIGN KEY Constraints from a table, use the following syntax:
|
1 |
ALTER TABLE Students DROP [FOREIGN KEY Dept_Id | CONSTRAINT fKey]; |
5) CHECK Constraint
A CHECK constraint in SQL is used to limit the value range that can be placed in a column. Furthermore, using check constraint, we can specify conditions for a field, which is able to be evaluated at the time of entering the data into a column.
Therefore, if the condition evaluates to false, the record violates the constraints and it’ll not be entered within the table.
Furthermore, if we define a CHECK constraint in SQL on a single column it permits only certain values for this column. whereas, if we define a CHECK constraint in SQL on a table it can limit the values in certain columns based on values in other columns in the row.
When you are using the CHECK constraint then we can specify a condition for a field, which should be satisfied at the time of entering values for this field.
CHECK Constraint Example
|
1 2 3 4 5 6 |
CREATE TABLE Students ( Student_Id INT(3) PRIMARY KEY, Student_Name VARCHAR(30), Student_Father VARCHAR(50), AGE int NOT NULL CHECK (AGE >= 20) ); |
Therefore, to create a CHECK constraint on the Age column when Students table already exists, then use the following below SQL syntax:
|
1 |
ALTER TABLE Students ADD [CONSTRAINT CHK_Age] CHECK (Age > 20); |
To Drop a CHECK constraint, use the following below SQL statement:
|
1 |
ALTER TABLE Students DROP [CONSTRAINT CHK_Age | CHECK Age]; |
6) DEFAULT Constraint
The SQL DEFAULT constraint is utilized for the purpose of providing a default value for the fields of a table. it implies that at the time of entering new records in the table suppose that if the user doesn’t specify any value for these fields then the default value will be assigned to them. The user has to define these default values.
DEFAULT Constraint Example
|
1 2 3 4 5 |
CREATE TABLE Students ( Student_Id INT(3), Student_Name varchar(50) NOT NULL, AGE int DEFAULT 20 ); |
The above SQL query will create a table Students(Student_Id, Student_Name, Age) with DEFAULT Constraint applied to Age field, thus if no value is entered into Age field, it will automatically store the default value such as 20.
Furthermore, to create a SQL DEFAULT constraint on the Age column when Students table already exists, then use the following below SQL syntax:
|
1 |
ALTER TABLE Students ADD CONSTRAINT CHK_Age DEFAULT 20 FOR Age; |
To Drop a SQL DEFAULT constraint, use the following below SQL syntax:
|
1 |
ALTER TABLE Students ALTER Age DROP DEFAULT; |
Conclusion of Types of Constraints in SQL
You have learned in this article, the types of constraints in SQL, i.e. NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT and CREATE INDEX. so, this concludes our list of basically used SQL constraints. Happy Learning! I hope you will enjoy the Types of Constraints in the SQL article.
About Author
