Constraints in SQL

We use constraints in SQL to limit what type of data can go into any given table. This way, we can ensure the reliability and accuracy of the data present in the table. Thus, if there occurs any violation between the data action and the constraint, the action is aborted as a result.

In this article, we will take a look at the Constraints in SQL according to the GATE Syllabus for CSE (Computer Science Engineering). Read ahead to learn more.

Table of Contents

What are Constraints in SQL?
Commonly Used Constraints
- Dropping Constraints
- Integrity Constraints
Practice Questions on Constraints in SQL
FAQs

What are Constraints in SQL?

The constraints refer to the rules that are enforced on a table’s data columns. We use these to limit what kinds of data can go into the given table. It helps us ensure the reliability and accuracy of the data present in the database.

Now, a constraint could be either on a table level or a column level. And while we apply the constraints on the whole table, we apply the column level constraints only on one column.

Commonly Used Constraints

Here are some of the very common types of constraints that are available in SQL:

1. DEFAULT Constraint – It gives a default value for the given column when none of these is specified.

2. NOT NULL Constraint − It basically ensures that a given column cannot have a NULL value.

3. PRIMARY Key − This type of constraint identifies every row/record uniquely in the given database table.

4. UNIQUE Constraint − It ensures that all the values present in a table’s column are different.

5. INDEX − It is used to create data and retrieve it from the given database very quickly.

6. CHECK Constraint − CHECK constraint in SQL ensures that all values present in a column satisfy certain conditions.

7. FOREIGN Key − It identifies a row/record uniquely in any of these given database tables.

Now, we can specify the constraints whenever we create a table with the CREATE TABLE statement. On the other hand, we can also use the ALTER TABLE statement if we want to create constraints after we create the table.

Dropping Constraints

We can drop the constraint that we have defined using the ALTER TABLE command along with the option of DROP CONSTRAINT. For instance, if we want to drop the primary key constraints present in the STUDENTS table, then we can use the command given as follows:

Certain implementations may provide us with certain shortcuts for dropping specific constraints. For instance, for a table in Oracle, if we want to drop the primary key constraint, then we can use the command as follows:

ALTER | TABLE | STUDENTS | DROP | PRIMARY KEY;

Now, some of the implementations allow us to disable certain constraints. And instead of dropping a constraint permanently from the database, we may want to disable the constraint temporarily and then finally enable it later on.

Integrity Constraints

We use the integrity constraints to ensure the consistency and accuracy of the data present in a relational database. In a relational database, we can handle data integrity through the very concept of referential integrity.

There are various types of integrity constraints that play an integral role in RI (Referential Integrity). Now, these constraints include the Primary Keys, Foreign Keys, Unique Constraints and also other constraints that are mentioned above.

Practice Questions on Constraints in SQL

1. Which of these is NOT a constraint in SQL?

A. Union

B. Check

C. Not Null

D. Primary Key

Answer: A. Union

2. Which of these refers to a constraint that can only be defined at the column level?

A. PRIMARY KEY

B. CHECK

C. NOT NULL

D. UNIQUE

Answer: C. NOT NULL

FAQs

What are constraints in SQL?

We use the SQL constraints to specify what rules should be there for the data in a table. The constraints also limit the types of data that can easily go into a table. It ensures the reliability and accuracy of the data present in the table. Thus, if there occurs any violation between the data action and the constraint, the action is aborted as a result.

What is a constraint key in SQL?

A primary key constraint identifies each record uniquely in a table. The primary keys must always contain UNIQUE values. They cannot contain NULL values. Also, a table can only have ONE primary key. This primary key in a table can consist of multiple or single columns (fields).

Keep learning and stay tuned to get the latest updates on GATE Exam along with GATE Eligibility Criteria, GATE 2023, GATE Admit Card, GATE Syllabus, GATE Previous Year Question Paper, and more.

Also Explore,