Composite Primary Key in Oracle

      8 Comments on Composite Primary Key in Oracle
0 Flares Twitter 0 Facebook 0 Google+ 0 LinkedIn 0 Buffer 0 Email -- Filament.io 0 Flares ×

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:
, ,

0 Flares Twitter 0 Facebook 0 Google+ 0 LinkedIn 0 Buffer 0 Email -- Filament.io 0 Flares ×

8 thoughts on “Composite Primary Key in Oracle

  1. Pingback: Oracle Tables:Create Table statement.How to create tables? | SQL and PLSQL

  2. Pingback: Oracle tables: Primary Key and Composite Primary Key | SQL and PLSQL

  3. Pingback: Oracle PL/SQL:CREATE TABLE statement: create a table with primary key. | SQL and PLSQL

  4. Pingback: Oracle PL/SQL:Create Table statement | SQL and PLSQL

  5. mohan

    There is one mistake, single row primary keyed example table mentioned above will not allow same data to be inserted twice in primary column.So please give note on that.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Paged comment generated by AJAX Comment Page