WikiGalaxy
Understanding SQL Constraints
Introduction to SQL Constraints:
SQL constraints are rules applied to table columns to ensure the accuracy and reliability of the data within the database. They enforce data integrity and prevent invalid data entry.
Primary Key Constraint:
A primary key constraint uniquely identifies each record in a table. It must contain unique values and cannot contain NULLs. Example:
CREATE TABLE Employees (
employee_id INT PRIMARY KEY,
name VARCHAR(100),
department VARCHAR(50)
);
Foreign Key Constraint:
A foreign key constraint is used to link two tables together. It ensures that the value in one table corresponds to a value in another table. Example:
CREATE TABLE Orders (
order_id INT PRIMARY KEY,
order_date DATE,
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);
Unique Constraint:
The unique constraint ensures that all values in a column are different. Example:
CREATE TABLE Products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100) UNIQUE,
price DECIMAL(10, 2)
);
Not Null Constraint:
The NOT NULL constraint ensures that a column cannot have a NULL value. Example:
CREATE TABLE Customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(100) NOT NULL,
email VARCHAR(100)
);
Check Constraint:
The CHECK constraint ensures that all values in a column satisfy a specific condition. Example:
CREATE TABLE Employees (
employee_id INT PRIMARY KEY,
age INT CHECK (age >= 18),
salary DECIMAL(10, 2)
);
Default Constraint:
The DEFAULT constraint provides a default value for a column when no value is specified. Example:
CREATE TABLE Orders (
order_id INT PRIMARY KEY,
order_date DATE DEFAULT GETDATE(),
status VARCHAR(20) DEFAULT 'Pending'
);
Composite Key Constraint:
A composite key is a primary key composed of multiple columns. Example:
CREATE TABLE Enrollment (
student_id INT,
course_id INT,
enrollment_date DATE,
PRIMARY KEY (student_id, course_id)
);
Index Constraint:
An index is used to speed up the retrieval of rows from a table. Example:
CREATE INDEX idx_customer_name
ON Customers (customer_name);
Console Output:
All constraints applied successfully.
