Oracle's Uniqueness Constraint (unique Constraint) usage details ____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:

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:

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:

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)--defines a compound uniqueness constraint
;

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

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

2 Sample preparation, first create a table

drop table tb_supplier;
drop table tb_products;

CREATE TABLE Tb_supplier
(
  supplier_id number not          null
 , Supplier_name        varchar2)
 , Contact_Name         varchar2 (m)
);

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

ALTER TABLE tb_supplier
add constraint  tb_supplier_u1
unique (supplier_id);

4 The Uniqueness constraint based on multiple columns

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

6 Disabling Uniqueness Constraints 1) Syntax:

ALTER TABLE table_name
DISABLE CONSTRAINT constraint_name;

2) Example:

ALTER TABLE tb_supplier
DISABLE CONSTRAINT  tb_supplier_u1;

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

---------------------------------------------------------------------------------------------------------

如果您们在尝试的过程中遇到什么问题或者我的代码有错误的地方，请给予指正，非常感谢。

联系方式：david.louis.tian@outlook.com 版权＠：转载请标明出处。

----------------------------------------------------------------------------------------------------------