Home / Database / SQL Server / Guide about Database Constraints
Constraints

Guide about Database Constraints

Database Constraints:

Constraint is the rules/conditions imposed on the columns & tables to maintain the data integrity of the database.

Constraints are of 2 types,

  1. Declarative Integrity Constraint: These constraints can be imposed at the time of table or column creation.
  2. Procedural Integrity Constraint: These constraints cannot be imposed at the time of table or column creation, but can be imposed using triggers.

Declarative Integrity Constraint.

DEFAULT:

  • This constraint is used to set the default value for the column. The default value provided will be inserted if the value for the column is not provided while inserting the record to the column.
  • This constraint can only be imposed on single column & not of group/combination of columns/tables.
  • Syntax is < Column Name >< Data Type > [Constraint < Constraint Name >] Default < Default Value >

NOT NULL:

  • This constraint restrict NULL value insertion into the columns.
  • This constraint can only be imposed on single column & not of group/combination of columns/tables.
  • Syntax is < Column Name >< Data Type > Not Null

UNIQUE:

  • Unique constraint restrict the user from inserting duplicate values to the column.
  • When Unique constraint is applied on the column then by default non clustered index is automatically gets created for the column.
  • Syntax column level : < Column Name >< Data Type > [Constraint < Constraint  Name >] Unique [ Clustered/Non clustered ]
  • Syntax table level :  [Constraint < Constraint  Name >] Unique ( < Column List > ) [ Clustered/Non clustered ]

PRIMARY KEY:

  • This constraint is used for uniquely identifying the rows of column of the table.
  • Primary key constraint will restrict the user from inserting null & duplicate values to the column.
  • Only one column in the table can be assigned as a primary key column.
  • By default clustered index gets created on the primary key column.
  • Syntax column level : < Column Name >< Data Type > [Constraint < Constraint  Name >] Primary Key [ Clustered/Non clustered ]
  • Syntax table level :  [Constraint < Constraint  Name >] Primary Key < Column Name > [ Clustered/Non clustered ]

CHECK:

  • This constraints allows to specify the condition in a column.
  • User can provide the condition which will check the data & validate the data for the condition before inserting data to the column.
  • Syntax column level : < Column Name >< Data Type > [Constraint < Constraint  Name >] Check ( < Condition > )
  • Syntax table level :  [Constraint < Constraint  Name >] Check ( < Condition > )

FOREIGN KEY / REFERENTIAL INTEGRITY CONSTRAINT:

  • This constraint allows user to insert only those values which exists in the referenced column.
  • Referenced column name is required while specifying foreign key constraint on the column.
  • This constraint allows null values to be inserted to the column.
  • Syntax column level :

< Column Name >< Data Type >
[Constraint < Constraint  Name >]
References < Table Name > ( < Column Name > )
[ On Update No Action / Cascade / Set Null / Set Default ]
[ On Delete No Action / Cascade / Set Null / Set Default  ]

  • Syntax table level :

[Constraint < Constraint  Name >]
Foreign Key  ( < Column List > )
References < Table Name > ( < Column List > )
[ On Update No Action / Cascade / Set Null / Set Default ]
[ On Delete No Action / Cascade / Set Null / Set Default  ]

  • By default “No Action” is set for update & delete operation.

 

About Helpservices

About This site (allhelpservices.com) is dedicated to helping other bloggers learn the skills of blogging, wordpress, education, technology, social media sharing , SEO, Electronics, Earn Money From Blogging, Web hosting and web share their own experiences and promote the blogging medium. We started Allhelpservices in August 2015 mainly because we wanted to keep a record of what we wear learning from development and because we wanted to connect with other learners.

Check Also

Database

Database level Queries & Identity functions

Get list of tables in the Database: We can get all the tables in the ...

Leave a Reply