PRIMARY KEYS

This Oracle tutorial explains how to create, drop, disable, and enable a primary key in Oracle with syntax and examples.

WHAT IS A PRIMARY KEY IN ORACLE?

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.

NOTE

• 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

You can create a primary key in Oracle with the CREATE TABLE statement.

Syntax

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

Let's look at an example of how to create a primary key using the CREATE TABLE statement in Oracle:

CREATE TABLE supplier
(
  supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50),
  CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);

In this example, we've created a primary key on the supplier table called supplier_pk. It consists of only one field - the supplier_id field.

We could also create a primary key with more than one field as in the example below:

CREATE TABLE supplier
(
  supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50),
  CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name)
);

CREATE PRIMARY KEY - USING ALTER TABLE STATEMENT

You can create a primary key in Oracle with the ALTER TABLE statement.

Syntax

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

Let's look at an example of how to create a primary key using the ALTER TABLE statement in Oracle.

ALTER TABLE supplier
ADD CONSTRAINT supplier_pk PRIMARY KEY (supplier_id);

In this example, we've created a primary key on the existing supplier table called supplier_pk. It consists of the field called supplier_id.

We could also create a primary key with more than one field as in the example below:

ALTER TABLE supplier
ADD CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name);

DROP PRIMARY KEY

You can drop a primary key in Oracle using the ALTER TABLE statement.

Syntax

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

Let's look at an example of how to drop a primary key using the ALTER TABLE statement in Oracle.

ALTER TABLE supplier
DROP CONSTRAINT supplier_pk;

In this example, we're dropping a primary key on the supplier table called supplier_pk.

DISABLE PRIMARY KEY

You can disable a primary key in Oracle using the ALTER TABLE statement.

Syntax

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

Let's look at an example of how to disable a primary using the ALTER TABLE statement in Oracle.

ALTER TABLE supplier
DISABLE CONSTRAINT supplier_pk;

In this example, we're disabling a primary key on the supplier table called supplier_pk.

ENABLE PRIMARY KEY

You can enable a primary key in Oracle using the ALTER TABLE statement.

Syntax

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

Let's look at an example of how to enable a primary key using the ALTER TABLE statement in Oracle.

ALTER TABLE supplier
ENABLE CONSTRAINT supplier_pk;

In this example, we're enabling a primary key on the supplier table called supplier_pk.

Using the LIKE Condition

Use the LIKE condition to perform wildcard searches of valid search string values.

Search conditions can contain either literal characters or numbers:

–    % denotes zero or many characters.

–    _ denotes one character.

SELECT       first_name

FROM         employees

WHERE        first_name LIKE 'S%' ;

Using the IN Condition

Use the IN membership condition to test for values in a list:

SELECT employee_id, last_name, salary, manager_id

FROM   employees

WHERE  manager_id IN (100, 101, 201) ;

EMPLOYEE_ID	LAST_NAME	SALARY	MANAGER_ID
201	Edi	1000	100
202	Laki	2000	100
203	John	1500	101
204	Haan	3000	201

Using Comparison Conditions

SELECT last_name, salary

FROM   employees

WHERE  salary <= 3000 ;

Last_name	Salary
Edin	2400
John	2300

Using the BETWEEN Condition

Use the BETWEEN condition to display rows based on a range of values:

SELECT last_name, salary

FROM   employees

WHERE  salary BETWEEN 2500 AND 3500 ;

Last_name	Salary
Edin	2400
John	2300

Comparison Conditions

Operator	Meaning
=	Equal to
>	Greater than
>=	Greater than or equal to
<	Less than
<=	Less than or equal to
<>	Not equal to
BETWEEN ...AND...	Between two values (inclusive)
IN(set)	Match any of a list of values
LIKE	Match a character pattern
IS NULL	Is a null value

Character Strings and Dates

•      Character strings and date values are enclosed by single quotation marks.

•      Character values are case-sensitive, and date values are format-sensitive.

•      The default date format is DD-MON-RR.

•      SELECT last_name, job_id, department_id FROM   employees WHERE  last_name = 'Jenny' ;

Limiting the Rows That Are Selected

Restrict the rows that are returned by using the WHERE clause:

SELECT *|{[DISTINCT] column|expression [alias],...}

FROM   table

[WHERE condition(s)];

The WHERE clause follows the FROM clause.

SELECT employee_id, last_name, job_id, department_id

FROM   employees

WHERE  department_id = 80 ;

EMPLOYEE_ID	LAST_NAME	JOB_ID	DEPARTMENT_ID
1000	PARK	PP1	80
1001	LAKI	PP2	80