Types of Integrity Constraints in SQL: Everything You Need to Know

SQL Primary key

A primary key is a field in a database table that uniquely identifies each row/record. This is also one type of Integrity Constraint. Primary keys must have distinct values. Null values are not allowed in a primary key column. A table can only have one primary key, which can be made up of one or more fields. It creates a composite key when several fields are used as a primary key

Syntax

Create table table_name

(  

Column_name1 datatype NOT NULL,  

Column_name2 datatype,

.....

Column_namendatatype,

 PRIMARY KEY (column_name1)  

)  

Code 

create table student(id int not null,name varchar(20),marks int,grade varchar(5),primary key(id));

select * from student;

The image depicted below demonstrates the table created with id, name, marks, and grade fields.

Input

Output

Explanation

By using the above query, it will create the table named student with appropriate fields. Id belongs to the not-null constraints, the name belongs to varchar datatype, marks belong to varchar datatype, and finally, the id field is set to primary key constraints.

Our Free Courses with Certificate

Introduction to SQL	Enroll Now!
SQL Injection For Beginners	Enroll Now!
Fundamentals of Database: What is SQL?	Enroll Now!
PostgreSQL: Become an SQL developer	Enroll Now!
Introduction to the Fundamentals of Databases	Enroll Now!

Foreign key

Foreign keys help ensure the consistency of your data while providing some ease. This is also a type of integrity constraint. You are responsible for keeping track of inter-table dependencies and preserving their consistency from within your applications if you don't use international keys. In certain situations, doing so isn't even that difficult. It’s only a matter of adding a few more delete sentences to ensure that all is in order.

Syntax

CREATE TABLE table_name1

(

 Column_name1 datatype NOT NULL,

 Column_name2 datatype ,

 ...

Column_nameNdatatype ,

  PRIMARY KEY (Column_name1)

)

CREATE TABLE table_name2

(

  Column_name1 datatype NOT NULL,

  Column_name2 datatype  NOT NULL,

  Column_name3 datatype  ,

  ....

Column_nameNdatatype ,

  PRIMARY KEY (Column_name1, Column_name2),

  FOREIGN KEY (Column_name1) REFERENCES table_name2 (Column_name1) ON DELETE CASCADE

)

Code

CREATE TABLE studnew

(

stu_id   INT NOT NULL,

stu_name varchar(20),

stu_class Varchar(20),

  PRIMARY KEY (stu_id)

)

CREATE TABLE classnew

(

stu_id   INT NOT NULL,

class_id INT NOT NULL,

  PRIMARY KEY (stu_id, class_id),

  FOREIGN KEY (stu_id) REFERENCES stud (stu_id) ON DELETE CASCADE

)

Select * from studnew

Input

Output

Become The Highest-Paid Business Analysis Expert

With Business Analyst Master's ProgramExplore Now

NOT NULL CONSTRAINTS

The not null constraint tells a column that it can't have any null values in it. This is also a type of integrity constraint. This forces a field to always have a value, meaning you can't create a new record or change an existing one without adding a value to it.

Syntax

Create table table_name

(  

Column_name1 datatype NOT NULL,  

Column_name2 datatype,

......

Column_namendatatype,

)  

Code

create table student1(id int not null,name varchar(20),marks int,grade varchar(5));

select * from student1;

Input

Output

Unique Key

A collection of one or more table fields/columns that uniquely identify a record in a database table is known as a unique key. This is also a type of integrity constraint. It’s similar to a primary key, but it can only accept one null value and cannot have duplicate values. Both the special key and the primary key ensure that a column or group of columns is unique. A Unique key is generated automatically.

Syntax

CREATE TABLE Table_name (

    Column_Name1 DataType NOT NULL UNIQUE,

    Column_Name2 DataType NOT NULL,

    Column_Name3 DataType,

         .......

Column_NameNDataType

);

Code

CREATE TABLE Student_DB (

S_ID int NOT NULL UNIQUE,

L_Name varchar(255) NOT NULL,

F_Name varchar(255),

Age int);

Input

Output

Want a Top Software Development Job? Start Here!

Full Stack Developer - MERN StackExplore Program

Primary Key Vs. Unique Key

When it comes to primary keys and unique keys, it is essential to follow a few rules to create a database. The following guidelines ensure consistency in the database model.

• It should specify the Primary Key for each table in the database. This will not only boost database efficiency overall but will also ensure that data is linked and consistent.
• Include an id column in each table. You should use it as a primary key column, with an unsigned integer form and identity set to true (1,1). As you add rows, the RDBMS will automatically generate primary key values. Using integers as Primary Keys also increases efficiency.
•  Aside from Primary Keys, which contain unique values, it should specify all attributes as UNIQUE. You may define this property for a single attribute or a group of attributes.

Become The Highest-Paid Business Analysis Expert

With Business Analyst Master's ProgramExplore Now

Dropping Constraints

The ALTER TABLE command is used to remove constraints.

Unless the restrict drop option is listed, when a primary/unique key is dropped, international keys referencing the key being dropped are also dropped. When the related tables/schemas/databases are removed, constraints are also removed. The cascade/restrict drop options are supported by the drop commands.

DROP a UNIQUE Constraint

The following syntax and examples help to drop unique constraint from the table:

Syntax

ALTER TABLE TABLE_NAME DROP CONSTRAINT UNIQUE _KEY FIELD

Code

ALTER TABLE Student_DB

 DROP CONSTRAINT  UKlname;

Input

Output

Want a Top Software Development Job? Start Here!

Full Stack Developer - MERN StackExplore Program

Dropping Primary Key Constraints

You can achieve most dropping constraints by using alter command. To take out the primary key declared in the table, make use of the following code. This will take out the primary key assigned to a particular field.

Code

ALTER TABLE shops

drop pkkey;

Input

Output

Dropping Foreign key Constraint

Code

ALTER TABLE Orders

DROP FOREIGN KEY FK_pid;

 ALTER TABLE Orders

DROP CONSTRAINT FK_pid;

Input

Output

Gain expertise in the latest Business analytics tools and techniques with the Business Analyst Certification Program. Enroll now!

Conclusion

SQL integrity constraint specifies a requirement that each row in a database table must satisfy. A predicate constraint is needed. It may refer to a single table column or multiple table columns. Depending on the existence of NULLs, the predicate's result may be TRUE, FALSE, or UNKNOWN. The constraints evaluate to UNKNOWN if the predicate evaluates to UNKNOWN.

Primary Key Constraints, Foreign Key Constraints, Unique Key Constraints, NOT NULL Constraints, Comparison between Primary Key and Unique Key Constraints, and Dropping the Constraints with syntax, example, and screenshots are given by executing the codes in this article.

While SQL is an old language, it is still very important today as businesses all over the world gather vast quantities of data. SQL is regularly among the most-requested tech skills, and mastering it will greatly enhance your skill set.

Simplilearn's SQL Certification Training Course is here for you to gain expertise in SQL programming language. This SQL certification course will teach you everything you need to know about SQL databases and how to integrate them into your applications. Learn how to properly organise your database, write successful SQL statements and clauses, and scale your SQL database. This course covers SQL basics, all related query tools and SQL commands, an industry-recognized course completion certificate, and lifetime access to self-paced learning.

Database and relational management, query tools and SQL commands, group by clause, subqueries, aggregate functions, tables and joins, views, transaction control, data manipulation, and procedures are only a few of the skills covered.

Start learning for free today's most in-demand skills. This course focuses on the development of strong core skills that will aid in your career success. Experts from the area will instruct you. Get instant access to over 300 job-ready skills in today's most in-demand fields. Learn from anywhere, on any laptop, when working or studying. Explore free courses here. Free guides on a number of career paths, wages, interview tips, and more are available.

Have any questions for us? Leave them in the comments section of this article, and our experts will get back to you on them, as soon as possible!