SQL Domain Constraints (NOT NULL, Check, UNIQUE)

Overview of SQL Integrity constraints

Integrity constraints ensure that changes made to the database by authorized users do not result in a loss of data consistency; it ensures that the data insertion, updating, and other processes have to be performed in such a way that data integrity is not affected, it guard against accidental damage to the database

Integrity constraints are usually identified as part of the database schema, design process, and declared as part of the create table command used to create relations,

Advertisement

Integrity constraints can also be added to an existing relation by using the command alter table

 

Types of Integrity constraints include

  1. Domain Constraint
  2. Entity Constraint
  3. Referential Integrity Constraint
  4. Key Constraint

 

SQL Domain Constraint

Domain constraint is used to restrict the values to be inserted in the column or relation, domain constraint defines a valid set of values for a table attribute also specifies all the possible values that the attribute can hold like integer, character, date, time, string, etc.

 

Type of SQL Domain Constraints

  1. NOT NULL constraint
  2. Check constraint
  3. UNIQUE constraint

 

SQL NOT NULL Constraint

To prevent NULL value to be entered into a column NOT NULL constraint is used, Once a NOT NULL constraint has been defined for a particular column, any insert or update operation that attempts to place a null value in that column will fail

 

SQL NOT NULL constraint with create table

Example 1: Write SQL query to create student table with the not null constraint on student id and student name columns

CREATE TABLE student
(
    student_id int not null,
    student_name varchar(20) not null,
    student_address varchar(30),
    student_emailid    varchar(20)
 )
  • In the above query, SQL NOT NULL constraint has applied on two columns student id and student name with SQL Create table query
  • If we do not specified NOT NULL constraint with column name , by default column declared as allow NULL value

OUTPUT:

Exec  sp_columns student

SQL Domain Constraints (NOT NULL, Check, UNIQUE)

We need to use SQL insert statement to check NOT NULL constraint on two columns student id and student name

Advertisement
INSERT INTO student (student_address, student_emailid)
VALUES ('surat', 'sqlconstraint@gmail.com')

In the above query, we have try to insert the data in the two columns student address and student emailed , but SQL will raise an exception of constraint violation

 

SQL NOT NULL constraint with alter table

Example 2: Write SQL query to alter the column student_emailid of student table to set SQL NOT NULL constraint

ALTER TABLE student  ALTER COLUMN student_emailid VARCHAR(25) NOT NULL;
  • In the above query, SQL NOT NULL constraint is applied by modifying existing column student_emailid of student table using alter table
  • In the query Constraint name need to specified after specified the data types of particular column , ALTER TABLE and ALTER COLUMN are the keywords used to alter the table definition

OUTPUT:

exec  sp_columns student

SQL Domain Constraints (NOT NULL, Check, UNIQUE)

INSERT INTO student (student_address) VALUES ('surat');

In the above query, we have try to insert the record in the student table by specifying single column value of student address, but SQL will raise an exception of constraint violation as student id, student name and student emilid column value cannot be null

 

SQL Check Constraint

CHECK constraint is one of the types of domain integrity constraint which restricts the values that are accepted by one or more columns. We can create a CHECK constraint with any logical (Boolean) expression that returns TRUE or FALSE based on the logical operators

 

SQL check constraint with create table

Example 3: Write SQL query to create table student with check integrity constraint to check student age must be greater than 17

Advertisement
CREATE TABLE student
(
      student_id int not null,
     student_name varchar(20) not null,
    student_address varchar(30),
student_emailid    varchar(20),
student_age int check (student_age>17)
     )
  • In the above query, SQL check constraint is applied on the student age column to check for the column value before inserting record
  • Column student id and student name has SQL NOT NULL constraint
  • If we try to enter less than 17 value in student_age column it shows error as ‘ check constraint violated ‘

OUTPUT:

exec sp_columns student

SQL Domain Constraints (NOT NULL, Check, UNIQUE)

 

SQL check constraint with create table to check with range value

To check for the multiple conditions, we need to used logical operator to serve as conjunctions for multiple conditions in a statement

Example 4: Write SQL query to create table student with check integrity constraint to check student age must be greater than 17 and less than 30 years

CREATE TABLE student
(
      student_id int not null,
     student_name varchar(20) not null,
    student_address varchar(30),
student_emailid    varchar(20),
student_age int check (student_age>17 and student_age <30)
     )
  • In the above query, SQL check constraint is applied on the student age column to check student age must be between the range of 17 to 30
  • Logical operator AND is used as the conjunction between two check conditions ,
  • Logical operator and will determine that both component conditions must be TRUE for given value of student age column

OUTPUT:

exec sp_columns student

SQL Domain Constraints (NOT NULL, Check, UNIQUE)

 

SQL check constraint with alter table

Example 5: Write SQL query to modify definition of already created table student by Appling check constraint on student id column

ALTER TABLE student
ADD CONSTRAINT CHK_studentid CHECK (student_id>100 AND student_id < 200);
  • In the above query, we have applied SQL CHECK constraint on student id column specify that student id must be between 100 to 200
  • ADD CONSTRAINT is the keyword used to add the constraint in already created table student
  • CHK_studentid is the constraint name

OUTPUT:

Advertisement

SQL Domain Constraints (NOT NULL, Check, UNIQUE)

We need to use SQL insert statement to verify dropping of the SQL Check constraint on student_id column

INSERT INTO student (student_id, student_name, student_address, student_emailid, student_age)
VALUES (202, 'dhiren', 'Surat', 'dhiren@gmail.com', 31)

When we execute above query, SQL return error message of check constraint violated as student id column value is not in-between 100 to 200

 

DROP a SQL Check Constraint

We can drop SQL Check constraint using SQL alter table statement with DROP CONSTRAINT keyword

Example 6: Write SQL query to remove the check constraint applied on student_id column

ALTER TABLE student DROP CONSTRAINT CHK_studentid
  • In the above query, SQL ALTER table statement is used to drop SQL Check constraint
  • once the  above query executed successfully , user can enter any numerical data into student_id column

OUTPUT:

SQL Domain Constraints (NOT NULL, Check, UNIQUE)

 

SQL Unique key Constraint

SQL Unique constraint is used to ensure that no duplicate values entered in the specific columns, the data must be unique for all records for particular column which contains unique constraint

Advertisement

The index is automatically created with SQL Unique constraint

 

SQL Unique key constraint with create table

Example 7: Write SQL query to create new table student result with the unique constraint on result ID column

CREATE TABLE student_result ( result_id int unique, semester int , student_id int, examdate date, totalmarks int,obtainedmark int,percentage decimal)

In the above query, SQL Unique constraint is applied on result_id column to add the unique value in the result_id column for each record

OUTPUT:

To check the result of above query we need to insert data in student_result table using SQL insert statement

INSERT INTO student_result (result_id, semester, student_id, examdate, totalmarks, obtainedmark, percentage)
VALUES (101, 2, 203, '02-20-2000', 100, 85, 85)

When we execute above query it will insert one new record into student_result table with record_id as 101

Advertisement

SQL Domain Constraints (NOT NULL, Check, UNIQUE)

To check for the unique constraint , we need to execute next insert query with result_id 101

INSERT INTO student_result (result_id, semester, student_id, examdate, totalmarks, obtainedmark, percentage)
VALUES        (101, 3, 103, '02-20-2000', 100, 65, 65)

SQL Domain Constraints (NOT NULL, Check, UNIQUE)

When we execute above insert query , SQL execution error raised as ‘ Violation of  UNIQUE KEY constraint’

 

SQL Unique key constraint with alter table

Example 7: Write SQL query to alter existing table student_result add the unique constraint on student ID column

ALTER TABLE student_result ADD UNIQUE (student_id);

In the above query, SQL Unique key constraint is applied on the student_id column of existing table student_result to make sure exery record must have unique value in the student_id column

OUTPUT:

INSERT INTO student_result(result_id, semester, student_id, examdate, totalmarks, obtainedmark, percentage)
VALUES (102, 4, 301, '02-20-2000', 100, 63, 63)

SQL Domain Constraints (NOT NULL, Check, UNIQUE)
To check for the unique constraint on student_id column , we need to execute next insert query with student_id 301 or 203

Advertisement
INSERT INTO student_result (result_id, semester, student_id, examdate, totalmarks, obtainedmark, percentage)
VALUES        (105, 3, 301, '02-20-2000', 100, 70, 70)

SQL Domain Constraints (NOT NULL, Check, UNIQUE)

 

Summary

This article of SQL Integrity constraints, we have covered overview of SQL integrity constraint with its types, The article specifically focused on domain constraint which is one of the type of integrity constraint with its three types of constraint applied on column level NOT NULL, CHECK and UNIQUE constraints discussed practical examples, SQL NOT NULL constraint with create table and alter table, SQL Check constraint with create table and alter table statements , SQL Unique constraint with create table and alter table statement

 

References

SQL Domain Constraints

 

Didn't find what you were looking for? Perform a quick search across GoLinuxCloud

If my articles on GoLinuxCloud has helped you, kindly consider buying me a coffee as a token of appreciation.

Buy GoLinuxCloud a Coffee

For any other feedbacks or questions you can either use the comments section or contact me form.

Thank You for your support!!

Leave a Comment

X