Introduction to SQL Constraints
SQL Constraints are the guidelines used to create restrictions to be implemented on the contents of the table or database, so that the records should obey these rules in order to be placed in that table. The guidelines are designed by the business personnel or the database architect, in an attempt to organize the data in the database, that can give assurance to flexible future upgrades. These constraints are designed carefully and prudently, as it results in overall smart design of the database.
In SQL, we have many different kinds of constraints. Let us look at the following few constraints in this article.
- NOT NULL
- CHECK
- UNIQUE
- PRIMARY KEY
- FOREIGN KEY
- DEFAULT
Different Kinds of SQL Constraints
1. NOT NULL Constraint
This constraint is used when you do not want any value in that particular column to be a Null value. This means that we cannot insert a Null value for that column while inserting a new row in the table. Every field in this column always has a non-Null value. A null value means that a particular field has been left empty, and values such as zero or blank space do not come under Null values.
Let us look at an example to create a table called Employee, having 5 columns, where empid, name and mobile columns do not accept NULL values.
CREATE TABLE Employee (
empid INT NOT NULL,
name VARCHAR(20) NOT NULL,
dob DATE,
mobile VARCHAR(10) NOT NULL,
address VARCHAR(20)
);
2. CHECK Constraint
This constraint limits the values that can be entered in that particular column of the table. To understand this better, let us take the example of passing marks in an exam. The range of values for these marks can only be from 35 to 100. To ensure that only values in this range are entered, we can create a CHECK constraint.
Let us look at an example of creating a CHECK constraint on the salary of employees.
CREATE TABLE Employee (
empid INT NOT NULL,
name VARCHAR(20) NOT NULL,
dob DATE,
mobile VARCHAR(10) NOT NULL,
salary INT CHECK (salary >= 15000 AND salary <= 30000),
address VARCHAR(20)
);
3. UNIQUE Constraint
This constraint is applied to ensure that the particular column accepts only unique values, and repetitive values are not allowed with such a constraint on the column. We can create multiple UNIQUE constraints on various columns in a table. A UNIQUE constraint allows NULL values to be entered.
Let us look at an example of enforcing the UNIQUE constraint. In this example, we are creating a column called mobile, in the table Employee, which is to be unique and cannot accept the same mobile number twice.
CREATE TABLE Employee (
empid INT NOT NULL,
name VARCHAR(20) NOT NULL,
dob DATE,
mobile VARCHAR(10) NOT NULL UNIQUE,
address VARCHAR(20)
);
4. PRIMARY KEY Constraint
This constraint is used to identify a particular column or a group of columns that can uniquely identify a row in the table. With the PRIMARY KEY constraint in place, any row cannot have a duplicate value. We cannot have NULL as the value for such a column. Even though both a PRIMARY KEY constraint and a UNIQUE constraint impose that the values are to be unique, we use a UNIQUE constraint for when we do not want to declare the column as Primary Key, but still want the values in that column to be unique. We can have only a single PRIMARY KEY column or group of columns in a table, but we can declare many individual columns to be UNIQUE.
Let us look at an example of the Employee table, creating unique employee IDs, therefore we can declare the empid column to be the PRIMARY KEY.
CREATE TABLE Employee (
empid INT NOT NULL PRIMARY KEY,
name VARCHAR(20) NOT NULL,
dob DATE,
mobile VARCHAR(10) NOT NULL UNIQUE,
address VARCHAR(20)
);
5. FOREIGN KEY Constraint
This constraint helps the data in one table to establish a relationship with the data in another table in the database. Foreign Key can be a single column or a set of columns. Let us consider two tables, Employee and Departments. Suppose we a column called depicted in Employee and a departed in Departments. We can reference the departed of Employee to the departed of Departments if the columns match. In this case, the column in Employee becomes a foreign key reference to the column in the Department table, which is a primary key.
CREATE TABLE Employee (
empid INT NOT NULL PRIMARY KEY,
name VARCHAR(20) NOT NULL,
dob DATE,
mobile VARCHAR(10) NOT NULL UNIQUE,
address VARCHAR(20),
depicted INT FOREIGN KEY REFERENCES Department(depicted)
);
6. DEFAULT Constraint
This constraint is used to specify the default value for a particular column in the table. This way if there is no value inserted for that column explicitly, the database engine can always refer to the default value specified and insert that in the column. Suppose we have inserted a column with the constraint NOT NULL and also with the constraint DEFAULT, then we do not need to explicitly define a default value. Even without giving a default value, the particular row will be inserted in the table.
Let us look at an example where we have entered the place in the address as default to have the value ‘India’.
CREATE TABLE Employee (
empid INT NOT NULL PRIMARY KEY,
name VARCHAR(20) NOT NULL,
dob DATE,
mobile VARCHAR(10) NOT NULL UNIQUE,
address VARCHAR(20) DEFAULT ‘India’,
depicted INT FOREIGN KEY REFERENCES Department(depicted),
);
Conclusion
SQL constraints help the developer by specifying restrictions and rules for the data that is to be inserted in the table. Constraints can be applied at the column level, just to the particular column or at the table level, where the constraints are applied to the complete table. These constraints restrict the kind of information that can be inserted in the table. This guarantees the correctness and consistency of the data in the table. In case of any violations of the rules specified by the constraints, the action is terminated.
Recommended Articles
This is a guide to SQL Constraints. Here we discuss the introduction to SQL Constraints along with different kinds of SQL Constraints which include Not Null, Check, Unique, Primary, Foreign and Default Constraints. You can also go through our other suggested articles to learn more–
