SQL Constraint | Types, Dropping & Integrity Constraints


1. Objective

In this SQL tutorial, we will study the various SQL Constraint. First, we will see a brief introduction of SQL Constraint, then we will look types of SQL Constraint. Moreover, we will learn dropping and integrity constraints in SQL. Along, with this, we will discuss constraint types like Foreign key, Primary key, Unique and some more with their syntax 

So, let us start SQL Constraint.

SQL constraint

SQL Constraint | Types, Dropping & Integrity Constraints

2. SQL Server Constraint

Constraints in SQL Server are basically the rules enforced to the data columns of the table, it can be used to set the limit to how much data can go into the table which therefore ensures the data reliability and accuracy in the database. They can be on a column level or on a table level. Column level constraints apply to a single column, but table-level constraints apply to the entire table.

Do you know about SQL RDBMS Concept

3. Constraints in SQL(Structured Query Language)

Following are the SQL Constraints:

  • NOT NULL: This constraint tells that we cannot store a null price during a column. That is, if a column is specified as NOT NULL then we’ll not be ready to store null during this particular column from now on.
  • UNIQUE: This constraint once nominal with a column, tells that each one the values within the column should be unique. That is, the values in any row of a column should not be repeated.
  • PRIMARY KEY: A primary key SQL Constraint is a field which may uniquely determine every row during a table. And this constraint is used to specify a field during a table as the primary key.
  • FOREIGN KEY: A remote key’s a field which may unambiguously determine every row in another table. And this constraint is employed to specify a field as the Foreign key.
  • CHECK: To meet an exact condition, check constraint validate the values of the column. That is, it helps to confirm that the value stored during a column meets a selected condition.
  • DEFAULT: This constraint specifies a default value for the column once no value is specified by the user.

4. How to Specify SQL Constraint?

We can specify constraint SQL at the time of making the table using create TABLE statement. We will in a position to conjointly specify the constraints when creating a table using the ALTER TABLE statement.

Have a look at SQL Operators

The syntax for SQL Constraint:

CREATE TABLE sample_table
(
column1 data_type(size) constraint_name,
column2 data_type(size) constraint_name,
column3 data_type(size) constraint_name,
....
);

Sample_table: Name of the table to be created.

Data_type: Style of data that may be stored within the field.

Constraint_name: Constraint name is the name of a Constraint. Examples are – NOT NULL, UNIQUE, PRIMARY KEY etc.

5. SQL Constraint Types

Let’s discuss types of SQL Server Constraints in detail:

SQL Constraint

Types of SQL Constraints

a. NOT NULL SQL Constraint

If we tend to specify a field in an exceedingly in a table to be NOT NULL. Then the field can ne’er settle for the null value. That is, you’ll be not allowed to insert whole new row within the table while not specifying any worth to this field.

Let’s discuss SQL Expressions

The SQL Constraint query below creates a table Student with the fields ID and NAME as NOT NULL.

CREATE TABLE Student
(
ID int(6) NOT NULL,
NAME varchar(10) NOT NULL,
ADDRESS varchar(20)
);

b. UNIQUE SQL Constraint

The unique SQL constraint helps to identify each row uniquely, which means a particular column and rows should have unique values. One or more columns can be unique.

Example of Unique SQL Constraint, query creates a tale Student where the field ID is specified as UNIQUE.

CREATE TABLE Student
(
ID int(6) NOT NULL UNIQUE,
NAME varchar(10),
ADDRESS varchar(20)
);

c. SQL PRIMARY KEY

Primary Key in SQL is a field which uniquely identifies each row in the table, it is the combination of NOT NULL and UNIQUE SQL Constraints. If a field in a table is specified as a primary key, then the field can not contain NULL values. Also for this field, the rows should contain unique values.

Do you know about SQL Data Types

CREATE TABLE Student
(
ID int(6) NOT NULL UNIQUE,
NAME varchar(10),
ADDRESS varchar(20),
PRIMARY KEY(ID)
);

d. SQL Server FOREIGN KEY

SQL Foreign Key is a field in a table which uniquely identifies each row of another table, which means this field points to the primary key of another table, thus creating a link between the two tables.

Orders

O_IDORDER_NOC_ID
122533
233253
345212
485321

Customers

C_IDNAMEADDRESS
1RAMESHDELHI
2SURESHNOIDA
3DHARMESHGURGAON

As we can see clearly that the field C_ID in Orders table is the primary key in Customers table, i.e. in the customer table, Primary key identifies each row. Therefore, it is a Foreign Key in the Orders table.

Let’s revise the SQL RDBMS Database System

The syntax of SQL Foreign Key:

CREATE TABLE Orders
(
O_ID int NOT NULL,
ORDER_NO int NOT NULL,
C_ID int,
PRIMARY KEY (O_ID),
FOREIGN KEY (C_ID) REFERENCES Customers(C_ID)
)

e. CHECK Constraint in SQL

Using this constraint, we can specify a condition for a field, which should be satisfied at the time of entering values for this field.

CREATE TABLE Student
(
ID int(6) NOT NULL,
NAME varchar(10) NOT NULL,
AGE int NOT NULL CHECK (AGE >= 18)
);

f. DEFAULT Constraint in SQL

Default SQL constraint is used to provide a default value for the fields. That means, when entering new records in the table no value is specified by the users for this fields then the default value gets assigned to them.

CREATE TABLE Student
(
ID int(6) NOT NULL,
NAME varchar(10) NOT NULL,
AGE int DEFAULT 18
);

6. Dropping Constraints in SQL

Any SQL constraint that you simply have defined are often dropped using the ALTER TABLE command with the DROP CONSTRAINT option.

Do you know about SQL Create and Drop Database

For example, to drop the first key constraint within the staff table, you’ll use the subsequent command.

ALTER TABLE employees DROP CONSTRAINT EMPLOYEES_PK;

Some implementations could provide shortcuts for dropping bound constraints. For example, to drop the primary key constraint for a table in Oracle, you’ll use the following command.

ALTER TABLE employees DROP PRIMARY KEY;

Some implementations allow you to disable constraints. Rather than permanently dropping a constraint from the database, you will wish to temporarily disable the constraint so enable it later.

7. Data Integrity Constraints in SQL

Integrity constraints are used to ensure accuracy and consistency of the data during a relational database. Data integrity is handled in a relational database through the concept of referential integrity.

Let’s learn about SQL Joins

There are many types of integrity constraints that play a role in referential Integrity (RI). These constraints embrace Primary Key, Foreign Key, unique Constraints and different constraints that are mentioned higher than.

So, this was all in SQL Constraint. Hope you like our explanation.

8. Conclusion

Hence, in this Constraint SQL tutorial, we learned about the various Constraints in SQL. First, we saw a brief intro of the SQL constraint. Then we move towards types in SQL Constraint. Moreover, we discussed SQL Foreign Key, SQL primary Key. Also, we learned Drop Constraint SQL Server and Integrity Constraint in SQL server. Still, if any doubt regarding SQL Constraint, ask in the comment tab.

See also –

Distinct Keywords in SQL 

For reference