Oracle's Uniqueness Constraint (UniqueConstraint) usage detailed ____oracle

Oracle | Pl/sql Unique index (unique Constraint) usage 1 target

Demonstrates how to create, delete, disable, and use a uniqueness constraint with an example. 2 What is a uniqueness constraint.

A uniqueness constraint refers to a constraint in a table in which a field or multiple fields combine to uniquely identify a record. Union field, you can include null values.

Note: In Oracle, uniqueness constraints can have up to 32 columns.

Uniqueness constraints can be created when a table is created or by using the ALTER TABLE statement. 3 Uniqueness Constraint and primary key difference

Primary KEY (Primary key): All columns that make up a primary key cannot contain null values. Uniqueness constraint (unique Constraint): If a uniqueness constraint consists of multiple columns, some of the columns can contain null values. It is not allowed in Oracle to create both a primary key and a uniqueness constraint on the same column.

4 Defining uniqueness constraints when creating a table 1) Syntax:

?

1 2 3 4 5 6 7

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

2 A single column based uniqueness constraint example:?

1 2 3 4 5 6 7

CREATE TABLE Tb_supplier (supplier_id number not NULL, Supplier_name varchar2), Contact_Name VARCHAR2, CONSTRAINT tb_supplier_u1 Unique (supplier_id)-Creates a uniqueness constraint when creating a table;

3 Examples of uniqueness constraints based on multiple columns:?

1 2 3 4 5 6 7 8

CREATE TABLE Tb_products (product_id number not NULL, product_name number is not NULL, Product_type VARCHAR2 (m), supplier_id number, CONSTRAINT tb_products_u1 unique (product_id, product_name)--Define a compound uniqueness constraint );

5 Creating a Uniqueness constraint using ALTER TABLE syntax 1) Grammar?

1 2 3

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

2 Sample preparation, first create a table?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16-17

drop table tb_supplier;   drop table tb_products;         CREATE TABLE Tb_supplier (supplier_id number not NULL, Supplier_name varchar2), Contact_Name   VARCHAR2 (50));      CREATE TABLE Tb_products (product_id number not NULL, product_name number is not NULL, Product_type VARCHAR2 (m), supplier_id number);

3) Uniqueness constraint based on single column?

1 2 3

ALTER TABLE Tb_supplier add constraint tb_supplier_u1 unique (supplier_id);

4 The Uniqueness constraint based on multiple columns?

1 2 3

ALTER TABLE tb_products add constraint tb_products_u1 unique (product_id,product_name);

6 Disabling Uniqueness Constraints 1) Syntax:?

1 2	ALTER TABLE table_name DISABLE CONSTRAINT constraint_name;

2) Example:?

1 2	ALTER TABLE tb_supplier DISABLE CONSTRAINT tb_supplier_u1;

7 using Uniqueness Constraints 1) Syntax:?

1 2	ALTER TABLE table_name ENABLE CONSTRAINT constraint_name;

2) Example:?

1 2	ALTER TABLE tb_supplier ENABLE CONSTRAINT tb_supplier_u1;

8 Delete Uniqueness constraint 1) Syntax:?

1 2	ALTER TABLE table_name DROP CONSTRAINT constraint_name;

2) Example:?

1 2	ALTER TABLE tb_supplier DROP CONSTRAINT tb_supplier_u1; ALTER TABLE tb_products DROP CONSTRAINT tb_products_u1;