Constraints in SQL Server

A constraint is a property assigned to a column or the set of columns in a table that prevents certain types of inconsistent data values from being placed in the column(s). Constraints are used to enforce the data integrity. This ensures the accuracy and reliability of the data in the database.

The following categories of the data integrity exist:

1.Entity Integrity :

Entity Integrity ensures that there are no duplicate rows in a table.

2.Domain Integrity :

Domain Integrity enforces valid entries for a given column by restricting the type, the format, or the range of possible values.

3.Referential integrity :

Referential integrity ensures that rows cannot be deleted, which are used by other records (for example,corresponding data values between tables will be vital).

4.User-Defined Integrity :

User-Defined Integrity enforces some specific business rules that do not fall into entity, domain, or referential integrity categories. Each of these categories of the data integrity can be enforced by the appropriate constraints.

SQL Server supports the following constraints:

  1. PRIMARY KEY
    UNIQUE
    FOREIGN KEY
    CHECK
    NOT NULL

A PRIMARY KEY constraint

IT is a unique identifier for a row within a database table. Every TABLE should have a primary key constraint to uniquely identify each row and only one primary key constraint can be created for each table. The primary key constraints are used to enforce entity integrity.

Here is the syntax to define ID attribute as a primary key in a CUSTOMERS table.

CREATE TABLE CUSTOMERS(
       ID   INT              NOT NULL,
       NAME VARCHAR (20)     NOT NULL,
       AGE  INT              NOT NULL,
       ADDRESS  CHAR (25) ,
       SALARY   DECIMAL (18, 2),       
       PRIMARY KEY (ID)
);

Delete Primary Key:

You can clear the primary key constraints from the table, Use Syntax:

ALTER TABLE CUSTOMERS DROP PRIMARY KEY ;

A UNIQUE constraint

You can also use following syntax, which supports naming the constraint in multiple columns as well:

ALTER TABLE CUSTOMERS
   ADD CONSTRAINT uniqueConstraint UNIQUE(AGE, SALARY)

IT enforces the uniqueness of the values in a set of columns, so no duplicate values are entered. The unique key constraints are used to enforce entity integrity as the primary key constraints.

DROP a UNIQUE Constraint:

To drop a UNIQUE constraint, use the following SQL:

ALTER TABLE CUSTOMERS
   DROP CONSTRAINT myUniqueConstraint;

A FOREIGN KEY constraint

prevents any actions that would destroy link between tables with the corresponding data values. A foreign key in one TABLE points to a primary key in another table. Foreign keys prevent actions that would leave rows with foreign key values when there are no primary keys with that value. The foreign key constraints are used to enforce referential integrity.

A FOREIGN KEY is a column whose values are derived from the PRIMARY KEY or UNIQUE KEY of some other table.

CREATE a FOREIGN KEY Constraint:

ALTER TABLE FOREIGNKEY_TABLE_NAME
   ADD FOREIGN KEY (Customer_ID) REFERENCES CUSTOMERS (ID);

DROP a FOREIGN KEY Constraint:

To drop a FOREIGN KEY constraint, use the following SQL:

ALTER TABLE ORDERS
   DROP FOREIGN KEY;

 

Leave a Reply

Your email address will not be published. Required fields are marked *