Oracle | PL/SQL Unique index (Unique Constraint) Usage

Oracle | PL/SQL Unique index (Unique Constraint) Usage

1 goal
The following example shows how to create, delete, disable, and use uniqueness constraints.

2. What is a uniqueness constraint?
A uniqueness constraint refers to the constraint that a field or multiple fields in a table can uniquely identify a record. Union fields can contain null values.

Note: in Oracle, the uniqueness constraint can contain a maximum of 32 columns.

The Uniqueness constraint can be created when a TABLE is created or by using the alter table statement.

3. Differences between uniqueness constraints and primary keys
Primary Key: All columns that constitute the Primary Key cannot contain null values.
Unique Constraint: If a uniqueness Constraint consists of multiple columns, some of the columns can contain null values.
Oracle does not allow both primary key creation and uniqueness constraints on the same column.
4. Define uniqueness constraints when creating a table
1) Syntax:
Create table table_name
(
Column1 datatype null/not null,
Column2 datatype null/not null,
...
CONSTRAINT constraint_name UNIQUE (column1, column2,..., column_n)
);

2) Example of uniqueness constraints based on a single column:

Create table tb_supplier
(
Supplier_id number not null
, Supplier_name varchar2 (50)
, Contact_name varchar2 (50)
, CONSTRAINT tb_supplier_u1 UNIQUE (supplier_id) -- create a uniqueness CONSTRAINT when creating a table
);

3) Examples of uniqueness constraints based on multiple columns:

Create table tb_products
(
Product_id number not null,
Product_name number not null,
Product_type varchar2 (50 ),
Supplier_id number,
CONSTRAINT tb_products_u1 UNIQUE (product_id, product_name) -- Define compound uniqueness CONSTRAINT
);

5. Use alter table syntax to create a uniqueness Constraint
1) syntax
Alter table table_name
Add constraint constraint_name
UNIQUE (column1, column2,..., column_n );

2) Prepare an example. Create a table first.

Drop table tb_supplier;
Drop table tb_products;

Create table tb_supplier
(
Supplier_id number not null
, Supplier_name varchar2 (50)
, Contact_name varchar2 (50)
);

Create table tb_products
(
Product_id number not null,
Product_name number not null,
Product_type varchar2 (50 ),
Supplier_id number
);

3) unique constraint based on a single column

Alter table tb_supplier
Add constraint tb_supplier_u1
Unique (supplier_id );

4) uniqueness constraints based on multiple columns

Alter table tb_products
Add constraint tb_products_u1
Unique (product_id, product_name );

6. Disable the uniqueness constraint.
1) Syntax:
Alter table table_name
Disable constraint constraint_name;

2) Example:

Alter table tb_supplier
Disable constraint tb_supplier_u1;

7. Use uniqueness constraints
1) Syntax:
Alter table table_name
Enable constraint constraint_name;

2) Example:

Alter table tb_supplier
Enable constraint tb_supplier_u1;

8. Delete the uniqueness constraint.

1) Syntax:
Alter table table_name
Drop constraint constraint_name;

2) Example:

Alter table tb_supplier drop constraint tb_supplier_u1;
Alter table tb_products drop constraint tb_products_u1;

-------------------------------------- Split line --------------------------------------

Rlwrap

SQLPLUS spool to dynamic Log File Name

Oracle SQLPLUS prompt settings

Accelerate SQL return by setting SQLPLUS ARRAYSIZE (row prefetch)

PL/SQL Developer Practical Skills

-------------------------------------- Split line --------------------------------------