SQL Constraints
SQL Constraints: Types of SQL Constraints Explained
Constraints are the rules or restrictions that are applied to columns in a table. These rules are applied to block and restrict the date type that is permitted to be input into the particular column.
Constraints ensure the reliability and accuracy level of the data within the table and within the database.
The implementation of Constraints can be applied either on particular column level or as a whole on the table level.
Column Level constraints are applicable only to a single column and hence their scope limits to that particular column only however, when a constraint is applied on a table level, its scope will apply to all columns within that particular table.
Following constraints are commonly used and are available in SQL.
- Constraint: NOT NULL
NOT NULL constraint is applied to ensure that no NULL value can be saved to a column.
- Constraint: DEFAULT
DEFAULT constraint is applied to provide a default or base value to a column. This value can be overwritten through the Front-end. The basic purpose of this constraint is to ensure that column value is not NULL.
- Constraint: UNIQUE
UNIQUE constraint is applied to ensure that all values entered into a column, are different or unique.
- PRIMARY Key:
PRIMARY Key is a used to set a unique parameter for each rows or records in a table. Usually, an ID column is generated and marked as Primary Key, which holds a uniquely sequenced integer value.
- FOREIGN Key:
FOREIGN Key is used as a reference key between two tables. This key helps to link and ensure the integrity of the data in one table and to match its values in another table
- CHECK Constraint:
The CHECK constraint is used to ensure that all the values input into a column, satisfy certain defined criteria.
- INDEX:
INDEX is utilized to accelerate the execution of SQL queries. It creates and retrieves data to and from a database at a high speed.
Constraints can be defined and set either at the time of table creation using CREATE TABLE statement or can be modified later by using ALTER TABLE statement.
Syntax:
CREATE TABLE tbl_name
(
col_name_1 data_type (size) constraint_name,
col_name_2 data_type (size) constraint_name,
col_name_3 data_type (size) constraint_name,
….
);
Dropping Constraints:
Any defined constraint, can be dropped with the ALTER TABLE command with the DROP CONSTRAINT keyword.
For Example:
The following command can be used to drop the primary key constraint in the CUSTOMERS table:
ALTER TABLE CUSTOMERS DROP CONSTRAINT CUSTOMERS_PK;
Integrity Constraints:
Integrity constraints are utilized to guarantee the precision and consistency of data stored in relational database. Data integrity is taken care of in a relational database through the idea of referential integrity.
There are numerous sorts of integrity constraints that assume a part in referential integrity (RI). These constraints comprise of Primary Key, Foreign Key, Unique Constraints and other limitations specified above.
More Related Articles For You
- Architecture of DBMS
- CODDS RULE DBMS
- Database Models in DBMS
- Relational DBMS Concepts
- Keys and Types of keys in Database
- Database Normalization
- Generalization, Specialization and Aggregation Concepts in DBMS
- ERD Diagram Tutorial with Examples in DBMS
- Introduction to SQL
- How to Create Query in SQL, Create Table, Delete Table and User Insert Info Statements
- Alter Query Command in SQL, Add, Modify and Drop Column in Table
- TCL Commands in SQL
- Truncate Query, Drop and Rename Query in SQL
- All DML Statement in SQL, Select Statement, Delete, Insert and Update Statement
- Data Control Language DCL Revoke and Grant Command in SQL
- Select Statement or Select Query in SQL Explained with Examples
- Distinct Keyword Explained in SQL
- WHERE Statement or WHERE Clause in SQL Statement Explained
- AND & OR Operators in SQL
- LIKE Operator in SQL
- ORDER BY Clause Sorting Explained in SQL
- Group By Clause in SQL
- Having Clause Explained in SQL
- SQL Aggregate Functions
- SQL Aliases Use and Purpose in SQL
- SQL Joins Types, Use and Purpose
- SQL Sequence Syntax and Use with Examples
- SQL View
- SET Operation in SQL, UNION, UNION ALL, Intersect and Minus