Oracle/PLSQL: Primary Keys

Oracle/PLSQL: Primary Keys

---

What is a primary key?

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.

Using a CREATE TABLE statement

The syntax for creating a primary key using a CREATE TABLE statement is:

CREATE TABLE table_name
(column1 datatype null/not null,
column2 datatype null/not null,
...
CONSTRAINT constraint_name PRIMARY KEY (column1, column2, . column_n)
);

For example:

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)
);

Using an ALTER TABLE statement

The syntax for creating a primary key in an ALTER TABLE statement is:

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

For example:

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 a Primary Key

The syntax for dropping a primary key is:

ALTER TABLE table_name
drop CONSTRAINT constraint_name;

For example:

ALTER TABLE supplier
drop CONSTRAINT supplier_pk;

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

Disable a Primary Key

The syntax for disabling a primary key is:

ALTER TABLE table_name
disable CONSTRAINT constraint_name;

For example:

ALTER TABLE supplier
disable CONSTRAINT supplier_pk;

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

Enable a Primary Key

The syntax for enabling a primary key is:

ALTER TABLE table_name
enable CONSTRAINT constraint_name;

For example:

ALTER TABLE supplier
enable CONSTRAINT supplier_pk;

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

Creating Foreign Keys:
Foreign Keys
Foreign Keys with cascade delete
Foreign Keys with "set null on delete"
Dropping Foreign Keys:
Drop a foreign key
Disable/Enable Foreign Keys:
Disable a foreign key
Enable a foreign key

Oracle/PLSQL: Unique Constraints

---

What is a unique constraint?

A unique constraint is a single field or combination of fields that uniquely defines a record. Some of the fields can contain null values as long as the combination of values is unique.

Note:

In Oracle, a unique constraint can not contain more than 32 columns.

A unique constraint can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.

What is the difference between a unique constraint and a primary key?

Primary Key	Unique Constraint
None of the fields that are part of the primary key can contain a null value.	Some of the fields that are part of the unique constraint can contain null values as long as the combination of values is unique.

Oracle does not permit you to create both a primary key and unique constraint with the same columns.

Using a CREATE TABLE statement

The syntax for creating a unique constraint using a CREATE TABLE statement is:

CREATE TABLE table_name
(column1 datatype null/not null,
column2 datatype null/not null,
...
CONSTRAINT constraint_name UNIQUE (column1, column2, . column_n)
);

For example:

CREATE TABLE supplier
(	supplier_id	numeric(10)	not null,
	supplier_name	varchar2(50)	not null,
	contact_name	varchar2(50),
	CONSTRAINT supplier_unique UNIQUE (supplier_id)
);

In this example, we've created a unique constraint on the supplier table called supplier_unique. It consists of only one field - the supplier_id field.

We could also create a unique constraint 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_unique UNIQUE (supplier_id, supplier_name)
);

Using an ALTER TABLE statement

The syntax for creating a unique constraint in an ALTER TABLE statement is:

ALTER TABLE table_name
add CONSTRAINT constraint_name UNIQUE (column1, column2, ... column_n);

For example:

ALTER TABLE supplier
add CONSTRAINT supplier_unique UNIQUE (supplier_id);

In this example, we've created a unique constraint on the existing supplier table called supplier_unique. It consists of the field called supplier_id.

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

ALTER TABLE supplier
add CONSTRAINT supplier_unique UNIQUE (supplier_id, supplier_name);

Drop a Unique Constraint

The syntax for dropping a unique constraint is:

ALTER TABLE table_name
drop CONSTRAINT constraint_name;

For example:

ALTER TABLE supplier
drop CONSTRAINT supplier_unique;

In this example, we're dropping a unique constraint on the supplier table called supplier_unique.

Disable a Unique Constraint

The syntax for disabling a unique constraint is:

ALTER TABLE table_name
disable CONSTRAINT constraint_name;

For example:

ALTER TABLE supplier
disable CONSTRAINT supplier_unique;

In this example, we're disabling a unique constraint on the supplier table called supplier_unique.

Enable a Unique Constraint

The syntax for enabling a unique constraint is:

ALTER TABLE table_name
enable CONSTRAINT constraint_name;

For example:

ALTER TABLE supplier
enable CONSTRAINT supplier_unique;

In this example, we're enabling a unique constraint on the supplier table called supplier_unique.

Oracle/PLSQL: Check Constraints

---

What is a check constraint?

A check constraint allows you to specify a condition on each row in a table.

Note:

·         A check constraint can NOT be defined on a VIEW.

·         The check constraint defined on a table must refer to only columns in that table. It can not refer to columns in other tables.

·         A check constraint can NOT include a SUBQUERY.

A check constraint can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.

Using a CREATE TABLE statement

The syntax for creating a check constraint using a CREATE TABLE statement is:

CREATE TABLE table_name
(column1 datatype null/not null,
column2 datatype null/not null,
...
CONSTRAINT constraint_name CHECK (column_name condition) [DISABLE]
);

The DISABLE keyword is optional. If you create a check constraint using the DISABLE keyword, the constraint will be created, but the condition will not be enforced.

For example:

CREATE TABLE suppliers
(	supplier_id	numeric(4),
	supplier_name	varchar2(50),
	CONSTRAINT check_supplier_id
	CHECK (supplier_id BETWEEN 100 and 9999)
);

In this first example, we've created a check constraint on the suppliers table called check_supplier_id. This constraint ensures that the supplier_id field contains values between 100 and 9999.

CREATE TABLE suppliers
(	supplier_id	numeric(4),
	supplier_name	varchar2(50),
	CONSTRAINT check_supplier_name
	CHECK (supplier_name = upper(supplier_name))
);

In this second example, we've created a check constraint called check_supplier_name. This constraint ensures that the supplier_name column always contains uppercase characters.

Using an ALTER TABLE statement

The syntax for creating a check constraint in an ALTER TABLE statement is:

ALTER TABLE table_name
add CONSTRAINT constraint_name CHECK (column_name condition) [DISABLE];

The DISABLE keyword is optional. If you create a check constraint using the DISABLE keyword, the constraint will be created, but the condition will not be enforced.

For example:

ALTER TABLE suppliers
add CONSTRAINT check_supplier_name
   CHECK (supplier_name IN ('IBM', 'Microsoft', 'NVIDIA'));

In this example, we've created a check constraint on the existing suppliers table called check_supplier_name. It ensures that the supplier_name field only contains the following values:  IBM, Microsoft, or NVIDIA.

Drop a Check Constraint

The syntax for dropping a check constraint is:

ALTER TABLE table_name
drop CONSTRAINT constraint_name;

For example:

ALTER TABLE suppliers
drop CONSTRAINT check_supplier_id;

In this example, we're dropping a check constraint on the suppliers table called check_supplier_id.

Enable a Check Constraint

The syntax for enabling a check constraint is:

ALTER TABLE table_name
enable CONSTRAINT constraint_name;

For example:

ALTER TABLE suppliers
enable CONSTRAINT check_supplier_id;

In this example, we're enabling a check constraint on the suppliers table called check_supplier_id.

Disable a Check Constraint

The syntax for disabling a check constraint is:

ALTER TABLE table_name
disable CONSTRAINT constraint_name;

For example:

ALTER TABLE suppliers
disable CONSTRAINT check_supplier_id;

In this example, we're disabling a check constraint on the suppliers table called check_supplier_id.