SQL & My SQL Tutorial
- What is SQL?
- SQL Microsoft
- SQL Management Tools
- SQL Developer
- Docker Commands
- Composite Key in SQL
- SQL Constraints
- Transactions in SQL Server
- SQL Server Data Types
- SQL Update Join
- SQL Operators
- SQL Clauses
- SQL Commands
- SQL Alter Command
- Distinct Keyword in SQL
- SQL Statements
- SQL Index
- SQL Injections
- Wildcards in SQL
- Alter and Truncate Command in SQL
- SQL Null Functions – ISNULL, IFNULL, Combine, & NULLIF
- SQL Sequence
- How to Find Duplicate Records in SQL
- Primary and Foreign Key in SQL with Examples
- COUNT Function
- SUM Function in SQL
- Dynamic SQL
- Database Tuning
- Pseudocolumn in Oracle SQL
- Triggers in SQL
- Embedded SQL in DBMS
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, which can give assurance to flexible future upgrades. These constraints are designed carefully and prudently, as it results in the overall smart design of the database.
In SQL, we have many different kinds of SQL constraints. Let us look at the following few constraint:
- NOT NULL
- PRIMARY KEY
- FOREIGN KEY
Different Kinds of SQL Constraints
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. Therefore, 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.
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 >= 50000 AND salary <= 90000), address VARCHAR(20) );
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) );
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 unique, we use a UNIQUE constraint 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 a group of columns in a table, but we can declare many individual columns 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) );
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. For example, let us consider two tables, Employee and Departments. Suppose we a column called depicted in Employee and a departed in Departments. Then, 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) );
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. If we have inserted a column with the constraint NOT NULL and the constraint DEFAULT, we do not need to define a default value explicitly. 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), );
CREATE TABLE Employee ( empid INT NOT NULL, name VARCHAR(20) NOT NULL, dob DATE, mobile VARCHAR(10) NOT NULL, address VARCHAR(20) );