Learning Oracle SQL and PL/SQL: CONSTRAINTS AND INDEXES

Showing posts with label CONSTRAINTS AND INDEXES. Show all posts

Monday, April 25, 2016

INDEXES

This Oracle tutorial explains how to create, rename and drop indexes in Oracle with syntax and examples.

WHAT IS AN INDEX IN ORACLE?

An index is a performance-tuning method of allowing faster retrieval of records. An index creates an entry for each value that appears in the indexed columns. By default, Oracle creates B-tree indexes.

CREATE AN INDEX

Syntax

The syntax for creating an index in Oracle/PLSQL is:

CREATE [UNIQUE] INDEX index_name
  ON table_name (column1, column2, ... column_n)
  [ COMPUTE STATISTICS ];

UNIQUE: It indicates that the combination of values in the indexed columns must be unique.
index_name: The name to assign to the index.
table_name: The name of the table in which to create the index.
column1, column2, ... column_n: The columns to use in the index.
COMPUTE STATISTICS: It tells Oracle to collect statistics during the creation of the index. The statistics are then used by the optimizer to choose a "plan of execution" when SQL statements are executed.

Example

Let's look at an example of how to create an index in Oracle/PLSQL.

For example:

CREATE INDEX supplier_idx
  ON supplier (supplier_name);

In this example, we've created an index on the supplier table called supplier_idx. It consists of only one field - the supplier_name field.

We could also create an index with more than one field as in the example below:

CREATE INDEX supplier_idx
  ON supplier (supplier_name, city);

We could also choose to collect statistics upon creation of the index as follows:

CREATE INDEX supplier_idx
  ON supplier (supplier_name, city)
  COMPUTE STATISTICS;

CREATE A FUNCTION-BASED INDEX

In Oracle, you are not restricted to creating indexes on only columns. You can create function-based indexes.

Syntax

The syntax for creating a function-based index in Oracle/PLSQL is:

CREATE [UNIQUE] INDEX index_name
  ON table_name (function1, function2, ... function_n)
  [ COMPUTE STATISTICS ];

UNIQUE: It indicates that the combination of values in the indexed columns must be unique.
index_name: The name to assign to the index.
table_name: The name of the table in which to create the index.
function1, function2, ... function_n: The functions to use in the index.
COMPUTE STATISTICS: It tells Oracle to collect statistics during the creation of the index. The statistics are then used by the optimizer to choose a "plan of execution" when SQL statements are executed.

Example

Let's look at an example of how to create a function-based index in Oracle/PLSQL.

For example:

CREATE INDEX supplier_idx
  ON supplier (UPPER(supplier_name));

In this example, we've created an index based on the uppercase evaluation of the supplier_name field.

However, to be sure that the Oracle optimizer uses this index when executing your SQL statements, be sure that UPPER(supplier_name) does not evaluate to a NULL value. To ensure this, add UPPER(supplier_name) IS NOT NULL to your WHERE clause as follows:

SELECT supplier_id, supplier_name, UPPER(supplier_name)
FROM supplier
WHERE UPPER(supplier_name) IS NOT NULL
ORDER BY UPPER(supplier_name);

RENAME AN INDEX

Syntax

The syntax for renaming an index in Oracle/PLSQL is:

ALTER INDEX index_name
  RENAME TO new_index_name;

index_name: The name of the index that you wish to rename.
new_index_name: The new name to assign to the index.

Example

Let's look at an example of how to rename an index in Oracle/PLSQL.

For example:

ALTER INDEX supplier_idx
  RENAME TO supplier_index_name;

In this example, we're renaming the index called supplier_idx to supplier_index_name.

COLLECT STATISTICS ON AN INDEX

If you forgot to collect statistics on the index when you first created it or you want to update the statistics, you can always use the ALTER INDEX command to collect statistics at a later date.

Syntax

The syntax for collecting statistics on an index in Oracle/PLSQL is:

ALTER INDEX index_name
  REBUILD COMPUTE STATISTICS;

index_name: The index in which to collect statistics.

Example

Let's look at an example of how to collect statistics for an index in Oracle/PLSQL.

For example:

ALTER INDEX supplier_idx
  REBUILD COMPUTE STATISTICS;

In this example, we're collecting statistics for the index called supplier_idx.

DROP AN INDEX

Syntax

The syntax for dropping an index in Oracle/PLSQL is:

DROP INDEX index_name;

index_name: The name of the index to drop.

Example

Let's look at an example of how to drop an index in Oracle/PLSQL.

For example:

DROP INDEX supplier_idx;

In this example, we're dropping an index called supplier_idx

CHECK CONSTRAINTS

This Oracle tutorial explains how to use the check constraints in Oracle with syntax and examples.

WHAT IS A CHECK CONSTRAINT IN ORACLE?

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 SQL 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 SQL Subquery.
A check constraint can be defined in either a SQL CREATE TABLE statement or a SQL ALTER TABLE statement.

USING A CREATE TABLE STATEMENT

The syntax for creating a check constraint using a CREATE TABLE statement in Oracle 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.

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 in Oracle 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.

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;

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 in Oracle is:

ALTER TABLE table_name
ENABLE CONSTRAINT constraint_name;

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 in Oracle is:

ALTER TABLE table_name
DISABLE CONSTRAINT constraint_name;

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.

UNIQUE CONSTRAINTS

This Oracle tutorial explains how to create, drop, disable, and enable unique constraints in Oracle with syntax and examples.

WHAT IS A UNIQUE CONSTRAINT IN ORACLE?

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.

CREATE UNIQUE CONTRAINT - USING A CREATE TABLE STATEMENT

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

CREATE TABLE table_name
(
  column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...

  CONSTRAINT constraint_name UNIQUE (uc_col1, uc_col2, ... uc_col_n)
);

table_name: The name of the table that you wish to create.
column1, column2: The columns that you wish to create in the table.
constraint_name: The name of the unique constraint.
uc_col1, uc_col2, ... uc_col_n: The columns that make up the unique constraint.

Example

Let's look at an example of how to create a unique constraint in Oracle using the CREATE TABLE statement.

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

CREATE UNIQUE CONTRAINT - USING AN ALTER TABLE STATEMENT

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

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

table_name: The name of the table to modify. This is the table that you wish to add a unique constraint to.
constraint_name: The name of the unique constraint.
column1, column2, ... column_n: The columns that make up the unique constraint.

Example

Let's look at an example of how to add a unique constraint to an existing table in Oracle using the ALTER TABLE statement.

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_name_unique UNIQUE (supplier_id, supplier_name);

DROP UNIQUE CONSTRAINT

The syntax for dropping a unique constraint in Oracle is:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

table_name: The name of the table to modify. This is the table that you wish to remove the unique constraint from.
constraint_name: The name of the unique constraint to remove.

Example

Let's look at an example of how to remove a unique constraint from a table in Oracle.

ALTER TABLE supplier
DROP CONSTRAINT supplier_unique;

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

DISABLE UNIQUE CONSTRAINT

The syntax for disabling a unique constraint in Oracle is:

ALTER TABLE table_name
DISABLE CONSTRAINT constraint_name;

table_name: The name of the table to modify. This is the table whose unique constraint you wish to disable.
constraint_name: The name of the unique constraint to disable.

Example

Let's look at an example of how to disable a unique constraint in Oracle.

ALTER TABLE supplier
DISABLE CONSTRAINT supplier_unique;

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

ENABLE UNIQUE CONSTRAINT

The syntax for enabling a unique constraint in Oracle is:

ALTER TABLE table_name
ENABLE CONSTRAINT constraint_name;

table_name: The name of the table to modify. This is the table whose unique constraint you wish to enable.
constraint_name: The name of the unique constraint to enable.

Example

Let's look at an example of how to enable a unique constraint in Oracle.

ALTER TABLE supplier
ENABLE CONSTRAINT supplier_unique;

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

FOREIGN KEYS

This Oracle tutorial explains how to use Foreign Keys in Oracle with syntax and examples.

WHAT IS A FOREIGN KEY IN ORACLE?

A foreign key is a way to enforce referential integrity within your Oracle database. A foreign key means that values in one table must also appear in another table.

The referenced table is called the parent table while the table with the foreign key is called the child table. The foreign key in the child table will generally reference a primary key in the parent table.

A foreign key can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.

USING A CREATE TABLE STATEMENT

Syntax

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

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

  CONSTRAINT fk_column
    FOREIGN KEY (column1, column2, ... column_n)
    REFERENCES parent_table (column1, column2, ... column_n)
);

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

CREATE TABLE products
( product_id numeric(10) not null,
  supplier_id numeric(10) not null,
  CONSTRAINT fk_supplier
    FOREIGN KEY (supplier_id)
    REFERENCES supplier(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. Then we've created a foreign key called fk_supplier on the products table that references the supplier table based on the supplier_id field.

We could also create a foreign 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 TABLE products
( product_id numeric(10) not null,
  supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  CONSTRAINT fk_supplier_comp
    FOREIGN KEY (supplier_id, supplier_name)
    REFERENCES supplier(supplier_id, supplier_name)
);

In this example, our foreign key called fk_foreign_comp references the supplier table based on two fields - the supplier_id and supplier_name fields.

USING AN ALTER TABLE STATEMENT

Syntax

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

ALTER TABLE table_name
ADD CONSTRAINT constraint_name
   FOREIGN KEY (column1, column2, ... column_n)
   REFERENCES parent_table (column1, column2, ... column_n);

Example

ALTER TABLE products
ADD CONSTRAINT fk_supplier
  FOREIGN KEY (supplier_id)
  REFERENCES supplier(supplier_id);

In this example, we've created a foreign key called fk_supplier that references the supplier table based on the supplier_id field.

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

ALTER TABLE products
ADD CONSTRAINT fk_supplier
  FOREIGN KEY (supplier_id, supplier_name)
  REFERENCES supplier(supplier_id, supplier_name);

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.