Constraints

SQL Constraints Overview

Constraints in SQL define rules for data stored in a database, ensuring accuracy and integrity. These constraints control what values are allowed in specific columns.

Constraint	Description
NOT NULL	Prevents NULL values in a column
UNIQUE	Ensures unique values in a column
PRIMARY KEY	Uniquely identifies each row (combines NOT NULL and UNIQUE)
FOREIGN KEY	References a record in another table
CHECK	Validates data based on a specified condition
DEFAULT	Sets a default value if none is provided
CREATE INDEX	Speeds up data retrieval for indexed columns

Details and Examples of SQL Constraints

1. NOT NULL Constraint

Ensures that a column cannot contain NULL values.

CREATE TABLE Colleges (
  college_id INT NOT NULL,
  college_code VARCHAR(20) NOT NULL,
  college_name VARCHAR(50)
);

In this example, college_id and college_code columns cannot have NULL values.

2. UNIQUE Constraint

Enforces unique values in a column, preventing duplicates.

CREATE TABLE Colleges (
  college_id INT NOT NULL UNIQUE,
  college_code VARCHAR(20) UNIQUE,
  college_name VARCHAR(50)
);

Here, both college_id and college_code must have unique values.

3. PRIMARY KEY Constraint

Combines NOT NULL and UNIQUE constraints to uniquely identify rows. Typically used as the main identifier for each row. A table can have only ONE primary key; and in the table, this primary key can consist of single or multiple columns (fields).

CREATE TABLE Colleges (
  college_id INT PRIMARY KEY,
  college_code VARCHAR(20) NOT NULL,
  college_name VARCHAR(50)
);
 
-- We can add name for it
CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)
-- the VALUE of the primary key is made up of TWO COLUMNS (ID + LastName).

The college_id column is used to uniquely identify each row in the Colleges table.

We can add composite key

CREATE TABLE EmployeesPhones (
	EmpId Int,
	Phone CHAR(11),
	PRIMARY KEY(EmpId, Phone)
)

We can add it when we are creating table Create Tables We can add it after creating tables, ALTER ADD

-- Add it
ALTER TABLE Persons  
ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName);
 
ALTER TABLE Persons  
ADD PRIMARY KEY (ID);
 
-- Drop it
ALTER TABLE Persons
DROP COnsTRAINT PK_Person;

4. FOREIGN KEY Constraint

Links a column in one table to the primary key of another table, ensuring referential integrity.

CREATE TABLE Orders (
  order_id INT PRIMARY KEY,
  customer_id INT REFERENCES Customers(id)
);

In this example, customer_id in Orders references id in the Customers table, ensuring that each customer_id corresponds to an existing customer.

To add Foreign key

-- Inline Constraint
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
 
-- Out-of-line Constraint
ALTER TABLE Orders
ADD CONSTRAINT FK_Customer FOREIGN KEY (CustomerID)
REFERENCES Customers(CustomerID);
 
 
ALTER TABLE Orders
DROP FOREIGN KEY FK_Customer
 
-- On Delete | On Update
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);
 
-- Composite Foreign Key
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)
);
 
-- Add name for Constraint
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    CONSTRAINT FK_Customer FOREIGN KEY (CustomerID) 
    REFERENCES Customers(CustomerID)
);
 
-- With Null
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT NULL,
    OrderDate DATE,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

5. CHECK Constraint

Validates that column values meet a specific condition.

CREATE TABLE Orders (
  order_id INT PRIMARY KEY,
  amount INT CHECK (amount >= 100)
);

The CHECK constraint ensures that amount in Orders is always greater than or equal to 100.

6. DEFAULT Constraint

Sets a default value for a column when no specific value is provided.

CREATE TABLE College (
  college_id INT PRIMARY KEY,
  college_code VARCHAR(20),
  college_country VARCHAR(20) DEFAULT 'US'
);

Here, if no value is specified for college_country, it defaults to “US”.

Salary FLOAT DEFAULT 10000
HiringDate DATETIME DEFAULT GETDATE()

7. CREATE INDEX Constraint

Creates an index to speed up data retrieval on a specific column.

CREATE TABLE Colleges (
  college_id INT PRIMARY KEY,
  college_code VARCHAR(20) NOT NULL,
  college_name VARCHAR(50)
);
 
-- Creating an index
CREATE INDEX college_index
ON Colleges(college_code);

The CREATE INDEX command creates an index on college_code in the Colleges table, which helps speed up data searches on that column.

🍿 CornPop

Explorer