Try it here
Subscribe
Oracle / PLSQL: Primary Keys

Oracle / PLSQL : create, drop, disable, and enable a primary key

_oracle_/_plsql_:_create,_drop,_disable,_and_enable_a_primary_key

In Oracle, a primary key is a single field or combination of fields that uniquely defines a record. None of the fields that are part of the primary key can contain a null value. A table can have only one primary key.

  • In Oracle, a primary key can not contain more than 32 columns.
  • A primary key can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.

Create Primary Key - Using CREATE TABLE statement

The syntax to create a primary key using the CREATE TABLE statement in Oracle/PLSQL is:

CREATE TABLE table_name
(
  column1 datatype null/not null,
  column2 datatype null/not null,
  ...

  CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ... column_n)
);

Example

CREATE TABLE emp
(
  emp_id numeric(10) NOT NULL,
  emp_name varchar2(50) NOT NULL,
  mgr_id numeric(10) NOT NULL,
  CONSTRAINT emp_pk PRIMARY KEY (emp_id)
);
 

A primary key with more than one field

CREATE TABLE emp ( emp_id numeric(10) NOT NULL, emp_name varchar2(50) NOT NULL, mgr_id numeric(10) NOT NULL, CONSTRAINT emp_pk PRIMARY KEY (emp_id,emp_name) );

Create Primary Key - Using ALTER TABLE statement

The syntax to create a primary key using the ALTER TABLE statement in Oracle/PLSQL is:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ... column_n);

Example

ALTER TABLE emp
ADD CONSTRAINT emp_pk PRIMARY KEY (emp_id);

A primary key with more than one field

ALTER TABLE emp ADD CONSTRAINT emp_pk PRIMARY KEY (emp_id,emp_name);

Drop Primary Key

The syntax to drop a primary key using the ALTER TABLE statement in Oracle/PLSQL is:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

Example

ALTER TABLE emp
DROP CONSTRAINT emp_pk;

Disable Primary Key

The syntax to disable a primary key using the ALTER TABLE statement in Oracle/PLSQL is:

ALTER TABLE table_name
DISABLE CONSTRAINT constraint_name;

Example

ALTER TABLE emp
DISABLE CONSTRAINT emp_pk;

Enable Primary Key

The syntax to enable a primary key using the ALTER TABLE statement in Oracle/PLSQL is:

ALTER TABLE table_name
ENABLE CONSTRAINT constraint_name;

Example

ALTER TABLE emp
ENABLE CONSTRAINT emp_pk;

Writer profile pic

Admin on May 04, 2019 at 12:05 am


If you like dEexams.com and would like to contribute, you can write your article here or mail your article to admin@deexams.com . See your article appearing on the dEexams.com main page and help others to learn.



Post Comment

Comments( 0)

WEB TECHNOLOGY

Articles

×

Forgot Password

Please enter your email address below and we will send you information to change your password.