SQL Constraints

SQL constraints are used to specify rules for data in a table. These rules help to ensure the accuracy and reliability of the data in the database. If a rule defined by a constraint is violated, the constraint enforces the violation by not allowing the invalid data to be inserted or updated.

Types of SQL Constraints

Several types of constraints can be applied to table columns:

1. NOT NULL Constraint

Ensures that a column cannot have a NULL value.

Example:

Creating a table with a NOT NULL constraint:

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY NOT NULL,
    CustomerName VARCHAR(255) NOT NULL,
    ContactName VARCHAR(255)
);

2. UNIQUE Constraint

Ensures that all values in a column are unique. Both NULL and unique values are allowed.

Example:

Adding a UNIQUE constraint to an existing table:

ALTER TABLE Customers
ADD CONSTRAINT UQ_CustomerName UNIQUE (CustomerName);

3. PRIMARY KEY Constraint

A combination of the NOT NULL and UNIQUE constraints. A table can have only one primary key, which typically identifies each row in a table. A primary key constraint automatically creates a clustered index on the column(s) it is applied to.

Example:

Defining a primary key during table creation:

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(255) NOT NULL,
    Price DECIMAL(10, 2)
);

A composite primary key (using multiple columns) is also possible:

Example:

CREATE TABLE OrderDetails (
    OrderID INT,
    ProductID INT,
    Quantity INT,
    PRIMARY KEY (OrderID, ProductID),
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

4. FOREIGN KEY Constraint

Used to link two tables together. It is a field (or collection of fields) in one table, where it refers to the primary key in another table. This creates a relationship between the two tables and enforces referential integrity.

Example:

Creating a foreign key constraint:

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderDate DATE,
    CustomerID INT,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

Foreign key constraints can also specify actions when the referenced primary key is updated or deleted:

5. CHECK Constraint

Used to limit the range of values that can be placed in a column. It ensures that all values in a column satisfy a specific condition.

Example:

Ensuring that the 'Price' is not negative:

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(255) NOT NULL,
    Price DECIMAL(10, 2),
    CHECK (Price >= 0)
);

Multiple conditions can be combined using AND or OR.

6. DEFAULT Constraint

Used to set a default value for a column. If no value is specified for the column when a new record is inserted, the default value is used.

Example:

Setting a default value for 'OrderDate':

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderDate DATE DEFAULT GETDATE(),
    CustomerID INT,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
Tip: Constraints are crucial for maintaining data integrity. Always consider the business rules and apply appropriate constraints to ensure data quality and prevent common errors.