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.
Also Read: How to Create a Database In MySQL?
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
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.
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
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
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.
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
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!