SQL Constraints

Introduction

This blog, we will learn the various available constrains in SQL, the syntax for declaration of constraint and examples of SQL constraint.

Getting Started with SQL Constraints

The Constraints in SQL are the rules enforced on data columns on the table. These are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the database.

Constraints could be column level or table level. Column level constraints are applied only to one column, whereas table level constraints are applied to the whole table.

List of SQL Constraints

Below has listed out the constrains available in SQL.

1. PRIMARY Key Constraint: Primary key in SQL uniquely identified each rows/records in a database table.
2. FOREIGN Key Constraint: Foreign key in sql uniquely identified a rows/records in any another database table.
3. CHECK Constraint: The CHECK constraint ensures that all values in a column satisfy certain conditions.
4. UNIQUE Constraint: Ensures that all values in a column are different.
5. DEFAULT Constraint: Provides a default value for a column when none is specified.
6. NOT NULL Constraint: Ensures that a column cannot have NULL value.
7. INDEX Constraint: Use to create and retrieve data from the database very quickly.

SQL Constraints Syntax

 --constraint with table creation synax   
 CREATE TABLE table_name   
  (   
  column_name data_type(size) CONSTRAINT constraint_id constraint_name (column_name)   
  )   
  --when constraint adds to exist table_name  
  ALTER TABLE Table_name ADD CONSTANT constraint_name(column_name)  

Example of SQL Constraints

 --Primary Key constraint Example   
  CREATE TABLE TestTable   
  (   
  ID INT CONSTRAINT TestTable_ID_PK PRIMARY KEY (ID)   
  )   
  --FOREIGN KEY Example  
  CREATE TABLE table_name   
  (   
  column_name data_type(size) CONSTRAINT constraint_id constraint_name (column_name) REFERENCES reference_tablename(reference_table_columnname)  
  );   
  CREATE TABLE TestTable   
  (   
  ID INT CONSTRAINT TestTable_ID_FK FOREIGN KEY (ID) REFERENCES TestTable1(ID)  
  );   
  --Check constraint Example  
  CREATE TABLE TestTable   
  (   
  Age INT CONSTRAINT TestTable_AGE_CK CHECK (Age>=18)   
  )   
 --UNIQUE constraint Example  
  CREATE TABLE TestTable   
  (   
  FirstName varchar(255),  
  LastName varchar(255) NOT NULL,  
  CONSTRAINT TestTable_Name_UK UNIQUE (FirstName,LastName)   
  )   
  --UNIQUE constraint Example  
  CREATE TABLE TestTable   
  (   
  FirstName varchar(255),  
  LastName varchar(255) NOT NULL,  
  CONSTRAINT TestTable_DName_UK UNIQUE (FirstName,LastName)   
  )   
  -- DEFAULT constraint Example  
  CREATE TABLE TestTable   
  (   
  FirstName varchar(255) CONSTRAINT TestTable_FirstName_UK DEFAULT 'NA'  
  )   

Related Articles

1. SQL Statements
2. SQL Notification
3. Create User-Defined Table Type
4. Create Create User-Defined Data Type in SQL

Thanks