What is Composite Key in Oracle?
Well, as we all know about Primary key as of now. Primary key is a column that uniquely identifies column in a table. But sometimes it happens that a single column is not sufficient to identify tuples in a table. This is where Composite key comes into consideration. Using two or more column as a Primary key so as to make it unique is a methodology on which Composite key actually work upon.
For example, we have Subject ID, that can never be considered as Primary key if we have lot of student having same subject. But, it will work if we consider Student ID as well as Subject ID together as a Primary key. It will be nothing but a composite key.
Composite Key Syntax in Oracle
Create Table Table_Name ( column_1 datatype(size), column_2 datatype(size)..., PRIMARY KEY(column_1, column_2) );
Composite Primary Key in Oracle with Example
We want to make a table name Stuu, wherein we want the Roll_no and Subject_ID to be a part of composite key. To follow the same follow the code as below:
--Composite Key-- create table stuu ( Name varchar(20), Section Varchar(10), Roll_no Number (20) , Subject_ID Number(20), Subject Varchar(20), PRIMARY KEY(Roll_no,Subject_ID) );