Thursday, June 27, 2019

SQL Server - What are Constraints in SQL?

Constraints are the rules and restrictions to apply on the type of data in a table. They specify the limit on the type of data that can be stored in a particular column in a table using constraints and also maintain the data integrity and accuracy in the table. They also ensure the unwanted data can't be inserted into tables.

Note: What data is valid and what is invalid can be defined using constraints.

How to specify constraints?
Constraints can be easily classified in the following two types-
n  Column Types Constraints
n  A column-level constraint references a single column and is defined along with the definition of the column at the time of creating the table using CREATE TABLE statement
n  Any constraint can be defined at the column level except for a COMPOSITE primary key constraints
n  Table Types Constraints :
n  A table-level constraint references one or more columns and is defined separately after the creation of the table using the Alter Command
n  All constraints can be defined at the table level except for the NOT NULL constraint.

Syntax: Naming of a Constraint
n  The general convention used for naming constraints is
                           <table name>_<column name>_<constraint type>
n  table name is the name of the table where the constraint is being defined,
n  column name is the name of the column to which the constraint applies,
n  constraint type is an abbreviation used to identify the constraint’s type

n  For example, a constraint name
                                          emp_deptno_fk refers to:
n  A constraint in table EMP on column DeptNo of type foreign key. 
                        dept_deptno_pk refers to:
n  A constraint in table DEPT on column DeptNo of type primary key

Most Popular Constraint in SQL
n  NOT NULL: This constraint ensures that the column has a value and the value is not a null value. A space or a numeric zero is not a null value and can defined at Column level only. click here to know How does Not Null constraint work?

n  UNIQUE: This constraint when specified with a column or set of columns be unique, tells that all the values in the column must be unique. That is, the values in any row of a column must not be repeated. 

n  PRIMARY KEY: Also known as the entity integrity constraint, is a field which can uniquely identify each row in a table. It creates a primary key for the table. A table can have only one primary key constraint. If a table uses more than one column as its primary key (i.e., a composite key), the key can only be declared at the table level. 

n  FOREIGN KEY: Also known as referential integrity constraint, is a field which can uniquely identify each row in another table. This constraint is used to specify a field as foreign key and it establishes a relationship with the primary key of another table.
Foreign key and referenced primary key columns need not have the same name, but a foreign key value must match the value in the parent table’s primary key value

n  CHECK: This constraint helps to validate the values of a column to meet a particular condition. That is, it helps to ensure that the value stored in a column meets a specific condition

n  DEFAULT: This constraint specifies a default value for the column when no value is specified by the user. click here to know How does Default constraint work?

To watch a series of constraints, please visit our YouTube channel -

Conclusion - Constraints are rules and regulations which simply apply on the data columns to meet some business standards and strictly attached within the column definitions. They give us a surety to us the data will be inserted as per their definition and any unwanted data will not be entertained by these SQL constraints. 

No comments:

Post a Comment