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;


If you like dEexams.com and would like to contribute, you can also 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.


Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.


Python if , elif and else

Python Conditions and If statements

  • 0
Python for beginners

Learning Python Part 1

  • 3
Struct Alignment and Padding

Struct Alignment and Padding in C++ And C

  • 0
Friend function

Friend function C++

  • 0
Pointers

C++ Pointers

  • 0
Structures

C++ Structures

  • 0
Types of Inheritance in C++

Inheritance and access specifiers C++

  • 0
Java date pattern

Java Date Pattern Syntax

  • 0
Java Date and Calendar

Java Date formats

  • 0
JAVA Data Type

Data types in Java

  • 0
Java unreachable code

Unreachable Code Error in Java

  • 0

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.