81 ) SQL : Constraints in sql

 

SQL Constraints: Comprehensive Types and Implementation (SSMS)

Constraints are rules used in SQL Server to enforce data integrity and ensure that invalid data cannot be inserted into tables. They act as gatekeepers at the column or table level.

1. Domain Constraints (Data Type & Not Null)

• Purpose: Restricts the type, format, and nullability of data allowed in a column, ensuring that only valid data types and non-null values are entered.

• SQL Example:

SQL

CREATE TABLE Departments (
    DepartmentID INT NOT NULL,               -- NOT NULL constraint
    DepartmentName VARCHAR(50) NOT NULL     -- Domain/Data Type and NOT NULL
);

2. Entity Constraints (Primary Key)

• Purpose: Uniquely identifies each record in a table. It enforces entity integrity by ensuring no duplicate rows exist and no NULL values are permitted in the key column(s).

• SQL Example:

SQL

CREATE TABLE Employees (
    EmployeeID INT NOT NULL,
    FirstName VARCHAR(50),
    CONSTRAINT PK_Employee PRIMARY KEY (EmployeeID)
);

3. Referential Integrity Constraints (Foreign Key)

• Purpose: Maintains logical relationships between tables. It ensures that a value in a child table must match an existing value in the parent table's primary key.

• SQL Example:

SQL

CREATE TABLE Orders (
    OrderID INT NOT NULL,
    EmployeeID INT,
    OrderDate DATE,
    CONSTRAINT PK_Order PRIMARY KEY (OrderID),
    CONSTRAINT FK_Employee_Order FOREIGN KEY (EmployeeID) 
        REFERENCES Employees (EmployeeID)
);

4. Uniqueness Constraints (Unique)

• Purpose: Ensures that all values in a column or combination of columns are unique across the table, preventing duplicate entries (such as duplicate email addresses).

• SQL Example:

SQL

CREATE TABLE Users (
    UserID INT NOT NULL,
    EmailAddress VARCHAR(100),
    CONSTRAINT UQ_Email UNIQUE (EmailAddress)
);

5. Check Constraints (Check)

• Purpose: Enforces domain-level business logic by restricting the allowable range or pattern of values in a column using a conditional expression.

• SQL Example:

SQL

CREATE TABLE Products (
    ProductID INT NOT NULL,
    Price DECIMAL(10,2),
    CONSTRAINT CK_Product_Price CHECK (Price > 0)
);

6. Default Constraints (Default)

• Purpose: Assigns a predetermined fallback value to a column automatically when an INSERT statement does not explicitly provide a value.

• SQL Example:

SQL

CREATE TABLE CustomerAccounts (
    AccountID INT NOT NULL,
    AccountStatus VARCHAR(20) DEFAULT 'Active'
);