‘CREATE TABLE’ : create table with composite primary key

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

CREATE TABLE statement can be used to create table objects in database. It is possible to add constraints like primary key ,foreign key while table creation.Primary key is the unique identifier for a row of data.One table cannot contain duplicate primary key values.Primary key also can be a combination of columns (COMPOSITE Primary Key).

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

>Also Read,

Oracle PL/SQL:CREATE TABLE statement: create a table with primary key.
Oracle PL/SQL:Create Table
Oracle/PLSQL: Foreign Keys with ON DELETE CASCADE option
ORA-02449: unique/primary keys in table referenced by foreign keys
ORA-02292: integrity constraint violated – child record found
ORA-02291: integrity constraint violated – parent key not Found
Oracle/PLSQL: Foreign Keys | Oracle Referential Integrity
Oracle/PLSQL: Composite Primary Key
Oracle/PLSQL: Primary Key and Composite Primary Key

Technorati Tags:
, ,

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

One thought on “‘CREATE TABLE’ : create table with composite primary key

  1. Pingback: Oracle Tables: Create Table as Select | SQL and PLSQL

Leave a Reply

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

Paged comment generated by AJAX Comment Page