Constraints in SQL

 Admin   
286 View    Jan 30 2020 11:30PM

Constraints are the guidelines enforced on the information columns of a table. These are used to limit the sort of data that can pass right into a table. This ensures the accuracy and reliability of the information within the database.

Constraints may be either on a column degree or a desk level. The column stage constraints are implemented most effective to 1 column, whereas the table level constraints are applied to the whole desk.

Following are a number of the most typically used constraints available in SQL. These constraints have already been discussed in SQL - RDBMS Concepts chapter, however it’s well worth to revise them at this point.

·        NOT NULL Constraint − Ensures that a column cannot have NULL value.

 

·        DEFAULT Constraint − Provides a default value for a column when none is specified.

 

·        UNIQUE Constraint − Ensures that all values in a column are different.

 

·        PRIMARY Key − Uniquely identifies each row/record in a database table.

 

·        FOREIGN Key − Uniquely identifies a row/record in any of the given database table.

 

·        CHECK Constraint − The CHECK constraint ensures that all the values in a column satisfies certain conditions.

 

·        INDEX − Used to create and retrieve data from the database very quickly.

 

 

Constraints can be specified when a table is created with the CREATE TABLE statement or you can use the ALTER TABLE statement to create constraints even after the table is created.

 

Dropping Constraints

 

Any constraint that you just have defined may be dropped using the ALTER TABLE command with the DROP CONSTRAINT option.

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

 

ALTER TABLE EMPLOYEES DROP CONSTRAINT EMPLOYEES_PK;

Some implementations may provide shortcuts for dropping certain constraints. For example, to drop the primary key constraint for a table in Oracle, you can use the following command.

ALTER TABLE EMPLOYEES DROP PRIMARY KEY;

Some implementations allow you to disable constraints. Instead of permanently dropping a constraint from the database, you may want to temporarily disable the constraint and then enable it later.

Integrity Constraints

 

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

There are many types of integrity constraints that play a role in Referential Integrity (RI). These constraints include Primary Key, Foreign Key, Unique Constraints and other constraints which are mentioned above.