Basic Constraints

Introduction

Constraints in MS SQL are rules applied to table columns to maintain data accuracy and integrity. They help ensure that the data entered into a database meets specific conditions, which prevents incorrect, inconsistent, or duplicate data. By enforcing these rules, constraints play a critical role in maintaining the reliability of your database.

Constraints are declared at the time of table creation or later using ALTER TABLE. The main types of constraints in MS SQL are:

• NOT NULL
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY
• CHECK
• DEFAULT

Types of Constraints

1. NOT NULL Constraint

The NOT NULL constraint ensures that a column cannot store NULL values, meaning a value must always be provided for that field. This constraint is crucial when a column requires data input.

Example: Ensuring that the first_name field always has a value:

CREATE TABLE Customers (
    customer_id INT,
    first_name  NVARCHAR(100) NOT NULL,
    email NVARCHAR(100)
);

In this example, the first_name field cannot be left blank when inserting new data into the Customers table.

2. UNIQUE Constraint

The UNIQUE constraint ensures that all values in a column are distinct. Unlike the PRIMARY KEY constraint, which also enforces uniqueness, a table can have multiple UNIQUE constraints (but only one PRIMARY KEY).

Example: Making sure that no two customers share the same email address:

CREATE TABLE Customers (
    customer_id INT,
    first_name  NVARCHAR(100) NOT NULL,
    email NVARCHAR(100) UNIQUE
);

Here, the UNIQUE constraint guarantees that the email column does not contain duplicate values.

3. PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each record in a table. It combines the properties of both NOT NULL and UNIQUE, ensuring that the field cannot contain NULL and that all values are unique. Each table can only have one primary key, but the key can consist of one or more columns (composite key).

Example: Defining a customer_id as the primary key for the Customers table:

CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    first_name  NVARCHAR(100) NOT NULL,
    email NVARCHAR(100) UNIQUE
);

Here, the customer_id column is guaranteed to be unique and non-null, making it the primary identifier for customers.

4. FOREIGN KEY Constraint

A FOREIGN KEY constraint creates a relationship between two tables, linking a column in one table to the PRIMARY KEY of another. This ensures referential integrity, meaning that the foreign key value must exist in the referenced table.

Example: Creating a foreign key from the Orders table to the Customers table:

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

In this example, customer_id in the Orders table is a foreign key, linking each order to a valid customer in the Customers table.

5. CHECK Constraint

The CHECK constraint limits the values that can be placed in a column based on a specific condition. This ensures that only valid data enters the table based on the rules you define.

Example: Ensuring that the age of customers is always 18 or older:

CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    first_name  NVARCHAR(100) NOT NULL,
    age INT CHECK (age >= 18)
);

Here, the CHECK constraint ensures that any inserted or updated age must be 18 or more.

6. DEFAULT Constraint

The DEFAULT constraint automatically assigns a default value to a column if no value is provided during the insert operation. This is useful for providing automatic values without user input.

Example: Setting the default order_status to 'Pending' for new orders:

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    order_status NVARCHAR(20) DEFAULT 'Pending'
);

If a new order is inserted without specifying the order_status, the default value 'Pending' will be assigned automatically.

Combining Multiple Constraints

You can apply multiple constraints on a single column to enforce more comprehensive rules.

Example: Combining constraints to ensure data integrity:

CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    first_name  NVARCHAR(100) NOT NULL,
    email NVARCHAR(100) UNIQUE,
    age INT CHECK (age >= 18),
    created_date DATE DEFAULT GETDATE()
);

In this table:

• The customer_id is the primary key.
• The first_name cannot be null.
• The email must be unique.
• The age must be at least 18.
• The created_date is set to the current date if no value is provided.

Altering Constraints

You can modify constraints after a table is created using the ALTER TABLE command.

Example: Adding a CHECK constraint to an existing column:

ALTER TABLE Customers
ADD CONSTRAINT chk_age CHECK (age >= 18);

Here are some best practices for using SQL constraints:

Best Practices

• Use Appropriate Constraints: Choose the right constraint type (e.g., PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL) based on the data integrity needs.

• Enforce Data Integrity: Always apply constraints to ensure that data entered into the database meets the required criteria and maintains integrity.

• Limit NULL Values: Use NOT NULL constraints to prevent null entries in columns that require data, promoting data completeness.

• Be Mindful of Performance: Overusing constraints can impact performance. Assess their necessity based on the application’s data access patterns.

Conclusion

Constraints are foundational to ensuring data integrity and accuracy in MS SQL. By defining constraints such as NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and DEFAULT, you can control the kind of data entered into your tables and protect the database from anomalies. Properly using constraints leads to a more robust and reliable data model, particularly in scenarios like customer management, where accuracy and consistency are crucial.

Asked in Interview

• What is a constraint in SQL?
• Can you name and briefly explain different types of constraints in SQL?
• What happens if you try to insert a value that violates a constraint (e.g., a PRIMARY KEY, NOT NULL, or UNIQUE constraint)?
• Can a table have more than one UNIQUE constraint? Can a table have more than one PRIMARY KEY?
• What is a CHECK constraint? Can you give an example?
• Can a FOREIGN KEY reference a UNIQUE key instead of a PRIMARY KEY?
• Can you drop a column that has a UNIQUE or FOREIGN KEY constraint applied to it?
• What is the purpose of the DEFAULT constraint, and can it be applied to all data types?