An Introduction to Understanding Integrity Constraints

When authorized users make changes to the database, integrity constraints ensure that the data remains consistent. When designing the database schema, integrity constraints are added. It defines constraints in SQL DDL commands such as 'CreateTable' and 'Alter Table.'

Integrity Constraints are the protocols that a table's data columns must follow. These are used to restrict the types of information that can be entered into a table. This means that the data in the database is accurate and reliable. You may apply integrity Constraints at the column or table level. The table-level Integrity constraints apply to the entire table, while the column level constraints are only applied to one column.

The following are some types of constraints:

  • The NOT NULL constraint prevents a column from having a NULL value.
  • When no value is defined for a column, the DEFAULT Constraint provides a default value.
  • A UNIQUE Constraint ensures that any value in a column is unique.
  • Each row/record in a database table is uniquely identified by the PRIMARY Key.
  • A FOREIGN KEY recognizes a row/record in any database table uniquely.

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

IntegrityConstraints_1

Output

IntegrityConstraints_2

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.

FREE Course: Introduction to Data Analytics

Mastery to Data Analytics Basics is a Click Away!Start Learning
FREE Course: Introduction to Data Analytics

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

IntegrityConstraints_3.

Output

IntegrityConstraints_4

Free Course: Business Analysis Basics

Master the Fundamentals of Business AnalysisEnroll Now
Free Course: Business Analysis Basics

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

IntegrityConstraints_5

Output

IntegrityConstraints_6

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

IntegrityConstraints_7.

Output

IntegrityConstraints_8

IntegrityConstraints_9.

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.

Post Graduate Program in Business Analysis

In partnership with Purdue UniversityVIEW COURSE
Post Graduate Program in Business Analysis

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

IntegrityConstraints_10

Output

IntegrityConstraints_11

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

IntegrityConstraints_12

Output

IntegrityConstraints_13

Free Course: Introduction to SQL

Learn MySQL, PostgreSQL and SQL ServerEnroll Now
Free Course: Introduction to SQL

Dropping Foreign key Constraint

Code

ALTER TABLE Orders

DROP FOREIGN KEY FK_pid;

 ALTER TABLE Orders

DROP CONSTRAINT FK_pid;

Input

IntegrityConstraints_14 

Output

IntegrityConstraints_15. 

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!

About the Author

SimplilearnSimplilearn

Simplilearn is one of the world’s leading providers of online training for Digital Marketing, Cloud Computing, Project Management, Data Science, IT, Software Development, and many other emerging technologies.

View More
  • Disclaimer
  • PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.