SQL-Constraints

SQL Constraints

SQL constraints are used to define rules for the data in a table. Constraints can be used as column level or table level.

1. Column level constraints apply to a table column.
2. Table level constraints apply to the whole table.

Constraints are used to restrict the limit of data type that can go into a table. This ensures the accuracy and reliability of the data in the table. If there is any violation between the constraint and the data action, the action is aborted. That means data will not take place in table.

SQL Create Constraints

SQL Constraints can be specified when the table is created with the CREATE TABLE statement, or after the table is created with the ALTER TABLE statement.

CREATE TABLE Statement

Syntax

CREATE TABLE table_name (column1 datatype constraint,column2 datatype 
constraint,column3 datatype constraint, ....)

After the table is created with the ALTER TABLE statement, You can also add constraints.

ALTER TABLE Statement

ALTER TABLE table_name 
ADD CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ... column_n); 

Types of SQL Constraints

The below defines the different types Constraints which can be used as column level or table level

The following constraints are mainly used as column level or table level in SQL Server:

1. NOT NULL - If you apply NOT NULL constraint of any table columns, columns will NOT accept NULL values .
2. UNIQUE - If you apply NOT NULL constraint of any table column, ensures that all values in a column are different.
3. PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table.
4. FOREIGN KEY - The foreign key establishes the relationship between the two tables and p revents actions that would destroy links between tables.
5. CHECK - Ensures that the values in a column satisfies a specific condition or adding logic.
6. DEFAULT - It is used to sets a default value for a column if no value is specified.
7. CREATE INDEX - It is used to create and retrieve data from the database very quickly. So we can also create index on table.