SQL Composite Key
A composite key is a combination of two or more columns that together uniquely identify each row in a table. Unlike a single-column primary key, uniqueness is guaranteed only when all columns in the key are considered together ÔÇö individually, they do not.
Complete Python Course with Advance topics:-
SQL Tutorial:-
Key Characteristics
- Made of two or more columns.
- Ensures uniqueness only when columns are combined.
- Columns can be of different data types.
- Can act as a primary key or candidate key.
Syntax
CREATE TABLE table_name (
column_a DATA_TYPE,
column_b DATA_TYPE,
column_c DATA_TYPE,
PRIMARY KEY (column_a, column_b)
);Examples Across Databases
-- MySQL
CREATE TABLE STUDENT_COURSE (
StudentID INT,
CourseID VARCHAR(50),
EnrollmentDate DATE,
PRIMARY KEY (StudentID, CourseID)
);
-- Oracle
CREATE TABLE EMPLOYEE_PROJECT (
EmpID INT,
ProjectID VARCHAR(30),
StartDate DATE,
PRIMARY KEY (EmpID, ProjectID)
);
-- SQL Server
CREATE TABLE ORDER_DETAILS (
OrderID INT,
ProductID NVARCHAR(50),
Quantity INT,
PRIMARY KEY (OrderID, ProductID)
);Why Use a Composite Key?
- Eliminates Redundancy: Prevents duplicate records.
- Ensures Integrity: Enforces uniqueness across multiple attributes.
- Optimizes Queries: Enables efficient multi-column indexing.
- Real-World Fit: A student can enroll in many courses ÔÇö only the combination is unique.
Download New Real Time Projects:- Click here
Complete Advance AI topics:-
Conclusion
Composite keys are essential when single-column primary keys cannot guarantee uniqueness. They are a practical way to enforce data integrity for multi-attribute datasets. For more SQL insights, stay tuned to .
sql composite key
composite key vs candidate key
composite primary key
sql server composite key
sql composite foreign key
sql composite unique key
composite key in dbms
composite key in sql w3schools
