AppDividend
Latest Code Tutorials

SQL Constraints Tutorial With Example | Understand SQL Constraints

0

SQL Constraints Tutorial With Example | Understand SQL Constraints is today’s topic. SQL Constraints are rules used to limit a type of data that can go into the table, to maintain the accuracy and the integrity of a data inside the table. Constraints can be divided into the following two types,

  1. Column level constraints: Limits only the column data.
  2. Table-level constraints: Limits whole table data.

SQL Constraints

Constraints are the set of rules enforced on the data columns of the table. These are used to limit the type of data that can go into the table. It ensures the accuracy, integrity, and reliability of the data in the SQL database.

Constraints in SQL are used to make sure that an integrity of the data is maintained in the database.

See the following most used constraints that can be applied to the table.

  1. NOT NULL
  2. UNIQUE
  3. PRIMARY KEY
  4. FOREIGN KEY
  5. CHECK
  6. DEFAULT

#NOT NULL Constraint in SQL

NOT NULL constraint restricts the column from having the NULL value. Once a NOT NULL constraint is applied to the column, you cannot pass the null value to that column. It enforces the column to contain a proper value. Empty will should not be there in that column for every table.

Generally, the ID column in the SQL Table contains the NOT NULL constraint.

See the following query of NOT NULL constraint.

CREATE TABLE Singer (
 id int NOT NULL, 
 Name varchar(60) NOT NULL, 
 Song varchar(60)
);

The above query will declare that an id and Name field of the Singer table will not take NULL value.

#PRIMARY KEY Constraint

We have covered Primary Key in this blog. The primary key is a field in the table which is used for uniquely identifying the row in the table. If the column has a primary key constraint, then it will contain the unique values and will not able to hold any NULL values.

The following SQL statement creates the table named investors and specifies an id column as a primary key. That means id field does not allow the NULL or duplicate values.

CREATE TABLE investors (
    id INT NOT NULL PRIMARY KEY,
    name VARCHAR(30) NOT NULL,
    portfolio amount INT,
    fund_name VARCHAR(50)
);

In the above query, we will create the investors’ table in which id column is the primary key. You can not add NULL value in the Primary Key column. So, id column has now two SQL Constraints. One point to remember that a primary key length cannot exceed more than 900 bytes.

#UNIQUE Constraint

UNIQUE constraint ensures that a field or the column will only have unique values. The UNIQUE constraint field will not have any duplicate data. The unique constraint can be applied at the column level or the table level.

The UNIQUE Constraint prevents two records from having identical values in a column.

#UNIQUE constraint in Table-level

Here we have the simple CREATE query to create the table, which will have the column app_id with unique values. See the following query.

CREATE TABLE Apps(
  app_id int NOT NULL UNIQUE, 
  Name varchar(60), 
  Price int
);

#UNIQUE constraint in Column-level

Here, we have to use the Alter table query to assign the Unique constraint to a particular field.

See the following query.

ALTER TABLE Apps ADD UNIQUE(app_id);

The above query specifies that the app_id field of Apps table will only have a unique value.

#Foreign Key Constraint

We have also covered the Foreign Key constraint here in this blog. SQL foreign key is used to form a link between two tables, which makes it a referencing key. FOREIGN KEY is used to relate the two tables. The FOREIGN KEY constraint is also used to restrict the actions that would destroy the links between tables. Let’s see its use, with the help of the two tables.

Investors

i_id investor_Name address
101 KRUNAL Noida
102 ANKIT Delhi
103 RUSHABH Rohtak

Employees

e_id employee_age i_id
10 26 101
11 24 103
12 26 102

In the above, there are two tables.

  1. Investors
  2. Employees

Now, the above two tables are connected through the Foreign key called i_id.

Investors table has a primary key called i_id and Employees table has a foreign key called i_id.

If you try to insert incorrect data, the DBMS will return the error and will not allow you to insert any data.

So, it will prevent to add any malicious values are manipulative values. You need to be really very careful when editing, updating, or deleting the data between those tables.

If it goes wrong, then the entire system will behave unnaturally and buggy.

#Using FOREIGN KEY constraint at Table Level

See the following query.

CREATE table Employees(
    e_id int PRIMARY KEY, 
    employee_age varchar(60) NOT NULL,
    i_id int FOREIGN KEY REFERENCES Investors(i_id)
);

So, in the above query, we have defined the relationship between the tables using i_id.

The i_id column in the Employees table reference from Investors tables.

#Using FOREIGN KEY constraint at Column Level

See the following query.

ALTER table Employees 
ADD FOREIGN KEY (i_id) REFERENCES Investors(i_id);

Here, we have used Alter query to add the Foreign Key constraint to the Employees table.

#Behaviour of Foreign Key Column on Delete

There are two ways to maintain the integrity of data in the Child table when the particular record is removed from the main table. When the two tables are connected through Foreign key, and certain data in the main table is removed, for which the record exists in a child table, then we must have some kind of mechanism to save that integrity of the data in the child table.

    1. On Delete Cascade: It will remove a record from a child table if that value of a foreign key is deleted from a main table.
    2. On Delete Null: It will set all the values in that record of the child, the table as NULL, for which a value of a foreign key is deleted from a main table.
    3. If we don’t use any of the above, then we cannot delete the data from a main table for which the data in child table exists. We will get an error if we try to do so.
ERROR : Record in child table exist

#Check Constraint

CHECK constraint is used to restrict a value of the column between the range.

It performs check on the values, before storing them into the database. Its like condition was checking before saving data into the column.

#Using CHECK constraint at Table Level

CREATE table Student(
    id int NOT NULL CHECK(id > 0),
    Name varchar(60) NOT NULL,
    Age int
);

The above query will restrict the id value to be higher than zero.

#Using CHECK constraint at Column Level

See the following query.

ALTER table Student ADD CHECK(id > 0);

#DEFAULT Constraint

The DEFAULT constraint provides the default value to a column when the INSERT INTO statement does not provide the specific value.

#Using DEFAULT constraint at Table Level

CREATE TABLE CUSTOMERS(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   ADDRESS  CHAR (100) ,
   SALARY   DECIMAL (18, 2) DEFAULT 100000.00,       
   PRIMARY KEY (ID)
);

#Using DEFAULT constraint at Column Level

If the table has been created already, then, we can use the Alter table statement to assign the default constraint. See the following query.

ALTER TABLE CUSTOMERS
ALTER SALARY SET DEFAULT 100000;

Finally, SQL Constraints Tutorial With Example | Understand SQL Constraints is over.

Leave A Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.