What are Constraints in SQL?

--NOT NULL Constraint:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(100) NOT NULL
);

--CHECK Constraint:

CREATE TABLE Students (
    ID INT PRIMARY KEY,
    Name VARCHAR(100),
    Age INT CHECK (Age >= 18 AND Age <= 100),  -- This ensures Age is between 18 and 100
    Grade CHAR(1) CHECK (Grade IN ('A', 'B', 'C', 'D', 'E'))  -- This ensures valid Grade values
);
-- SQL Server Syntax with Named Constraints:
CREATE TABLE Students (
    ID INT PRIMARY KEY,  -- Primary Key constraint is unnamed by default
    Name VARCHAR(100),

    Age INT CONSTRAINT CHK_Age CHECK (Age >= 18 AND Age <= 100),  -- Named CHECK constraint

    Grade CHAR(1) CONSTRAINT CHK_Grade CHECK (Grade IN ('A', 'B', 'C', 'D', 'E')),  -- Named CHECK constraint

    City VARCHAR(50) CONSTRAINT DF_City DEFAULT ('Unknown')  -- Named DEFAULT constraint
);


--DEFAULT Constraint:

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderDate DATE DEFAULT GETDATE(),
    CustomerID INT,
    TotalAmount DECIMAL(10, 2) DEFAULT 0
);

--UNIQUE Constraint:

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100) UNIQUE,
    Price DECIMAL(10, 2)
);

--PRIMARY KEY Constraint:

CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(100)
);

--FOREIGN KEY Constraint:

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

--Default Constraint
CREATE TABLE Employees (
    ID INT PRIMARY KEY,
    Name VARCHAR(100),
    Age INT DEFAULT 30,  -- Default value for Age is 30 if not specified
    City VARCHAR(50) DEFAULT 'Unknown'  -- Default city value is 'Unknown'
);