• Sharebar

Composite primary key is a special type of primary key comprises a set of columns.In relational database a primary key is a candidate key to uniquely identify each row in a table.
A unique key or primary key comprises a single column or set of columns (COMPOSITE Primary KEY).
No two distinct rows in a table can have the same value or combination of values(if it is composite primary key) in those columns.
Primary keys are defined through the PRIMARY KEY constraint (ANSI SQL Standard).
The examples given below explains both Primary key and Composite primary keys.
The below given example uses CREATE TABLE statement to create a table with a single column primary key.

SQL> create table MYTABLE(
name VARCHAR2(50),
id NUMBER,
salary NUMBER(8,2),
CONSTRAINT MYTABLE_ID PRIMARY KEY (id));

Table created

Now let us INSERT few records into MYTABLE.

SQL> insert into MYTABLE values ('CCC',1,2548.21);
SQL> insert into MYTABLE values ('ADS',2,3548.21);
SQL> insert into MYTABLE values ('GDS',2,1548.21);

SQL> select * from MYTABLE ORDER BY SALARY;

NAME   ID  SALARY
GDS   2    1548.21
CCC   1    2548.21
ADS   2    3548.21

The below given example uses CREATE TABLE statement to create a table with a multiple column primary key (COMPOSITE Primary KEY).

SQL> drop table mytable;
Table dropped

SQL> create table MYTABLE(
name VARCHAR2(50),
id NUMBER,
salary NUMBER(8,2),
CONSTRAINT MYTABLE_NAME_ID_PK PRIMARY KEY (name,id));

Table created

Now let us INSERT few records into MYTABLE.

SQL> insert into MYTABLE values ('CCC',1,2548.21);
SQL> insert into MYTABLE values ('ADS',2,3548.21);
SQL> insert into MYTABLE values ('GDS',2,1548.21);

SQL> select * from MYTABLE ORDER BY SALARY;

NAME   ID  SALARY
GDS   2    1548.21
CCC   1    2548.21
ADS   2    3548.21

Related Articles,Oracle/PLSQL: Primary Key and Composite Primary Key
ALTER TABLE to ADD PRIMARY KEY in Oracle.
SQL MIN Function | MIN() function in SQL

Technorati Tags:
, ,

 
Be Sociable, Share!
SEO Powered by Platinum SEO from Techblissonline