EnjoY | Database Research And Development: SQL Server : SQL Integrity Constraints or Constraints

Monday, September 21, 2020

SQL Server : SQL Integrity Constraints or Constraints

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

SQL Integrity Constraints or Constraints

Constraints are some rules that enforce on the data to be enter into the database table. Basically constraints are used to restrict the type of data that can insert into a database table. Constraints can be defined in two ways:

    1. Primary Key
    2. Unique 
    3. Check
    4. Not Null
    5. Foreign
  1. Column Level

    The constraints can be specified immediately after the column definition with the CREATE TABLE statement. This is called column-level constraints.

  2. Table Level

    The constraints can be specified after all the columns are defined with the ALTER TABLE statement. This is called table-level constraints.

Types of SQL Constraints

In Microsoft SQL Server we have six types of constraints

  1. Primary Key Constraints

    Primary key is a set of one or more fields/columns of a table that uniquely identify each record/row in database table. It can not accept null, duplicate values.

    Primary key constraint at column level

     CREATE TABLE table_name
    (
    col1 datatype [CONSTRAINT constraint_name] PRIMARY KEY,
    col2 datatype
    ); 
    Primary key constraint at table level
     ALTER TABLE table_name
    ADD[CONSTRAINT constraint_name] PRIMARY KEY (col1,col2)
    

  2. Unique Key Constraints

    Unique key is a set of one or more fields/columns of a table that uniquely identify each record/row in database table. It is like Primary key but it can accept only one null value and it can not have duplicate values

    Unique key constraint at column level

     CREATE TABLE table_name
    (
    col1 datatype [CONSTRAINT constraint_name] UNIQUE,
    col2 datatype
    ); 
    Unique key constraint at table level
     ALTER TABLE table_name
    ADD[CONSTRAINT constraint_name] UNIQUE (col1,col2)
    

  3. Foreign Key Constraints

    Foreign Key is a field in database table that is Primary key in another table. It can accept multiple null, duplicate values.

    Foreign key constraint at column level

     CREATE TABLE table_name
    (
    col1 datatype [CONSTRAINT constraint_name] REFERENCES referenced_table_name(referenced_table_column_name),
    col2 datatype
    ); 
    Foreign key constraint at table level
     ALTER TABLE table_name
    ADD[CONSTRAINT constraint_name] REFERENCES referenced_table_name(referenced_table_col)
    

  4. Not Null Constraints

    This constraint ensures that all rows in the database table must contain value for the column which is specified as not null means a null value is not allowed in that column.

    Not Null constraint at column level

     CREATE TABLE table_name
    (
    col1 datatype [CONSTRAINT constraint_name] NOT NULL, 
    col2 datatype
    );
    
    Not Null constraint at table level
     ALTER TABLE table_name
    ALTER COLUMN col1 datatype NOT NULL
    

  5. Check Constraints

    This constraint defines a business rule on a column in the database table that each row of the table must follow this rule.

    Check constraint at column level

     CREATE TABLE table_name
    (
    col1 datatype [CONSTRAINT constraint_name] CHECK (condition), 
    col2 datatype
    ); 
    Check constraint at table level
     ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK(condition) 

No comments:

Post a Comment

It’s all about friendly conversation here at small review :) I’d love to be hear your thoughts!

Be sure to check back again because I do make every effort to reply to your comments here.

Featured Post

SQL Server : SELECT all columns to be good or bad in database system

This article is half-done without your Comment! *** Please share your thoughts via Comment *** In this post, I am going to write about one o...

Popular Posts