Nov

ALTER TABLE to ADD PRIMARY KEY in Oracle| Oracle ALTER TABLE

Primary key is a type of constraint used in Oracle tables.A primary key is used to uniquely identify each row in a table.A primary key can consist of one or more fields on a table. When more than one fields are used as a primary key, the key is called a composite key.

You can create primary keys in two ways using CREATE TABLE and ALTER TABLE statements.

1.when the table is created using CREATE TABLE statement

Step 1:

SQL> CREATE TABLE Mytable
(id number ,
 name varchar2(100),
 msg varchar2(500),
 CONSTRAINT    PK_MYTABLE    PRIMARY KEY (id ));

The above script will create a table names Mytable with primary key Mytable_id_pk using column id.

2.By changing existing table structure using ALTER TABLE statement.

Example

Step 1:
Create a table using the following script.

SQL> create table Mytable (name varchar2(100), msg varchar2(500), id number);

This will create a table named Mytable with three columns.

Step 2:

-- Create/Recreate primary, unique and foreign key constraints
SQL> alter table MYTABLE
  add constraint PK_MYTABLE primary key (ID);

This will ALTER the TABLE to create primary key PK_MYTABLE using column id.

ALTER TABLE to ADD PRIMARY KEY in Oracle| Oracle ALTER TABLE

Categories