Constraints in SQL Server

Hi All,

Hope you all are doing good.

In this article, we will know about various key constraints available in T-SQL.

Following are different key constraints available in T-SQL.

• Primary Key
• Foreign Key
• NOT NULL
• UNIQUE
• CHECK

Regarding Primary and Foreign Key, we have seen examples in our earlier posts.

Now, we will see examples for NOT NULL, UNIQUE and CHECK constraints.

NOT NULL:
This constraint specifies that value is mandatory for specific column.

Let us consider below table for our example.

We can apply NOT NULL constraint to any number columns while creating table itself.

CREATE TABLE Persons(
    person_id INT IDENTITY PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(20)
);

Add NOT NULL constraint to an existing column
To add the NOT NULL constraint to an existing column, you follow these steps:

First, update the table so there is no NULL in the column:
Syntax:    UPDATE table_name
        SET column_name = <value>
        WHERE column_name IS NULL;

Second, alter the table to change the property of the column:
Syntax:    ALTER TABLE table_name
        ALTER COLUMN column_name data_type NOT NULL;

For example, to add the NOT NULL constraint to the phone column of the Persons table, you use the following statements:

First, if a person does not have a phone number, then update the phone number to the company phone number e.g., 9090909090:

UPDATE Persons
SET phone = "9090909090"

WHER phone IS NULL;

Second, modify the property of the phone column:
ALTER TABLE Persons

ALTER COLUMN phone VARCHAR(20) NOT NULL;


Removing NOT NULL constraint
To remove the NOT NULL constraint from a column, you use the ALTER TABLE ALTER COLUMN statement as follows:
Syntax:   ALTER TABLE table_name
       ALTER COLUMN column_name data_type NULL;

For example, to remove the NOT NULL constraint from the phone column, you use the following statement:

ALTER TABLE Pesons

ALTER COLUMN phone VARCHAR(20) NULL;

UNIQUE:
SQL Server UNIQUE constraints allow you to ensure that the data stored in a column, or a group of columns, is unique among the rows in a table.

We can apply UNIQUE constraint to any number columns while creating table like below

Syntax: CREATE TABLE table_name (
             column1  data_type PRIMARY KEY,
             column2 data_type,
             column3 data_type,
             column4 data_type,
             ...,
            UNIQUE (column2,column3)
 );

or

CREATE TABLE table_name (
             column1  data_type PRIMARY KEY,
             column2 data_type UNIQUE,
             column3 data_type UNIQUE,
             column4 data_type UNIQUE,
             ...,
             columnn data_type UNIQUE,
 );

When you add a UNIQUE constraint to an existing column or a group of columns in a table, SQL Server first examines the existing data in these columns to ensure that all values are unique. If SQL Server finds the duplicate values, then it returns an error and does not add the UNIQUE constraint.

Below is the syntax to add UNIQUE constraints to existing columns
ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
UNIQUE(column1, column2,...);

Below is the syntax to delete UNIQUE constraints from table
ALTER TABLE table_name
DROP CONSTRAINT constraint_name;


CHECK:
The CHECK constraint allows you to specify the values in a column that must satisfy a Boolean expression.

Example: Apply check constraint over single column
 CREATE TABLE Products(
    product_id INT IDENTITY PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    unit_price DEC(10,2) CHECK(unit_price > 0)
);
Example: Apply check constraint over multiple columns
CREATE TABLE Products(
    product_id INT IDENTITY PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    unit_price DEC(10,2),
    discounted_price DEC(10,2),
    CHECK(unit_price > 0),
    CHECK(discounted_price > 0),
    CHECK(discounted_price > unit_price)

);

Add CHECK constraints to an existing table
To add a CHECK constraint to an existing table, you use the ALTER TABLE ADD CONSTRAINT statement.
Example: 
ALTER TABLE Products
ADD CONSTRAINT positive_price CHECK(unit_price > 0);

Remove CHECK constraints
To remove a CHECK constraint, you use the ALTER TABLE DROP CONSTRAINT statement:

Syntax: ALTER TABLE table_name
             DROP CONSTRAINT constraint_name;

Disable CHECK constraints for insert or update
To disable a CHECK constraint for insert or update, you use the following statement:
Syntax: ALTER TABLE table_name
       NOCHECK CONSTRAINT constraint_name;

Thanks for reading my article.