SQL Table and DDL Commands
SQL Keys Constraints:
- Primary key
- Foreign key
- Unique Key
- Composite Key
Primary Key:
- The Primary Key constraint uniquely identifies each Record in a Table.
- IT must contain unique values, but not NULL Values
- A table can have only one Primary Key.
Example 1:
CREATE TABLE Employee (
CustomerID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Email VARCHAR(100)
);
Example 2:
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY AUTO_INCREMENT, — Auto-incrementing primary key
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
DepartmentID INT, — Foreign key to Departments table (optional)
JobTitle VARCHAR(50),
HireDate DATE,
Email VARCHAR(100) UNIQUE, — Unique constraint for non-primary key email
Salary DECIMAL(10,2),
DateOfBirth DATE,
Address VARCHAR(255),
City VARCHAR(50),
State VARCHAR(50),
ZipCode VARCHAR(10),
);
How to add PRIMARY KEY on multiple columns?
ALTER TABLE Employee
ADD Constrinat pk_Employee PRIMARY KEY(EmpID,FirstName);
DROP PRIMARY KEY From a Table:
ALTER TABLE Employee
DROP PRIMARY KEY;
UNIQUE Key Constraint:
- This key is used to avoid duplicate values.
- Both Unique and PRIMARY KEY provides uniqueness for a column or set of columns.
- IT allows many unique key constraints per Table.
Example:
CREATE TABLE Employee (
ID int NOT NULL,
LastName varchar(50) NOT NULL,
FirstName varchar(50),
Age int,
UNIQUE (EMPID)
);
Foreign Key Constraint:
- It is use to link between two tables.
- It refers to the primary key of another Table
- It ensures referential integrity (data consistency between related tables).
Example:
CREATE TABLE Orders (
OrderID int NOT NULL,
OrderNumber int NOT NULL,
EmpID int,
PRIMARY KEY (OrderID),
FOREIGN KEY (EmpID) REFERENCES Employee(EmpID)
);
Composite Key:
- A primary key made up of multiple columns.
- Used when a single column cannot uniquely identify rows.
Example:
CREATE TABLE OrderItems (
OrderID INT,
ProductID INT,
Quantity INT,
PRIMARY KEY (OrderID, ProductID) — Composite primary key
);
