Complete Guide to SQL Keys and Constraints

In SQL, keys are used to uniquely identify rows in a table, while constraints are rules applied to enforce data integrity and accuracy.

1. Primary Key

• Uniquely identifies each record in a table.
• Cannot be NULL or duplicate.

Example:

						CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(100),
    Age INT
);
					

2. Foreign Key

• Creates a link between two tables.
• Must match a primary key in another table.

Example :

										CREATE TABLE Enrollments  (
    EnrollmentID  INT PRIMARY KEY,
    StudentID INT,
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID) 
    );
									

3. Candidate Key

• Any column (or combination) that can qualify as a primary key.
• There can be multiple candidate keys, but only one is chosen as the primary key.

Example :

In a table with Email and StudentID, both may uniquely identify students:

										CREATE TABLE Users  (
    UserID INT,     
    Email VARCHAR(100), 
    PRIMARY KEY (UserID) 
    );
									

-- Here, Email is also a candidate key

4. Alternate Key

• A candidate key not chosen as the primary key.

Example :

Example: If UserID is the primary key, then Email becomes the alternate key.

5. Composite Key

• A primary key made up of two or more columns.

Example :

						CREATE TABLE CourseRegistration  (
    StudentID INT,
    CourseID INT,
    PRIMARY KEY (StudentID, CourseID)
);
									

6. Super Key

• Any combination of columns that can uniquely identify a row.
• All candidate keys are super keys, but not all super keys are candidate keys.

Example :

In a table with StudentID and Email, both together form a super key.

7. Unique Key

• Ensures that all values in a column are different.
• Allows one NULL value (in most databases).

Example :

						CREATE TABLE Teachers  (
    TeacherID INT PRIMARY KEY,
    Email VARCHAR(100) UNIQUE
);
									

Other Important SQL Constraints

Constraints enforce rules at the column or table level.

1. NOT NULL Constraint

• Ensures that a column cannot have NULL values.

Example :

						CREATE TABLE Employees   (
    EmployeeID INT NOT NULL, 
    Name VARCHAR(100) NOT NULL 
);
									

2. CHECK Constraint

• Ensures values in a column satisfy a specific condition.

Example :

						CREATE TABLE Products ( 
    ProductID INT PRIMARY KEY, 
    Price DECIMAL CHECK (Price > 0) 
); 
 
									

3. DEFAULT Constraint

• Assigns a default value to a column if no value is provided.

Example :

						CREATE TABLE Orders ( 
    OrderID INT PRIMARY KEY, 
    Status VARCHAR(20) DEFAULT 'Pending' 
); 
 
									

4. INDEX

• Not a constraint, but helps speed up data retrieval.
• Can be created on one or more columns.

Example :

						CREATE INDEX idx_name ON Students(Name);