Usage of the UNIQUE constraints (UNIQUE Constraint) in Oracle, uniqueconstraint

Usage of the UNIQUE constraints (UNIQUE Constraint) in Oracle, uniqueconstraint

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

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

5. Use alter table syntax to create a uniqueness constraint 1) syntax

ALTER TABLE table_nameADD CONSTRAINT constraint_nameUNIQUE (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_supplieradd constraint  tb_supplier_u1unique (supplier_id);

4) uniqueness constraints based on multiple columns

alter table tb_productsadd constraint  tb_products_u1unique (product_id,product_name);

6. Disable uniqueness constraint 1) Syntax:

ALTER TABLE table_nameDISABLE CONSTRAINT constraint_name;

2) Example:

ALTER TABLE tb_supplierDISABLE CONSTRAINT  tb_supplier_u1;

7. Use uniqueness constraint 1) Syntax:

ALTER TABLE table_nameENABLE CONSTRAINT constraint_name;

2) Example:

ALTER TABLE tb_supplierENABLE CONSTRAINT tb_supplier_u1;

8. Delete the uniqueness constraint 1) Syntax:

ALTER TABLE table_nameDROP CONSTRAINT constraint_name;

2) Example:

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

Bytes ---------------------------------------------------------------------------------------------------------

If you encounter any problems during your attempt or my code is incorrect, please correct me. Thank you very much!

Contact: david.louis.tian@outlook.com

Copyright @: reprinted, please indicate the source!
Bytes ----------------------------------------------------------------------------------------------------------