Detailed example of Oracle adding unique constraints

This article brings you relevant knowledge about Oracle, which mainly introduces the related issues of adding unique constraints. A unique constraint means that one field or multiple fields in the table can be unique when combined. Let’s take a look at the constraints that mark a record. I hope it will be helpful to everyone.

Recommended tutorial: "Oracle Video Tutorial"

1 Target

Use Demonstration example demonstrates how to create, delete, disable and use unique constraints

2 What is a unique constraint?

Uniqueness constraints refer to constraints where one field or multiple fields combined in the table can uniquely identify a record.

Union fields can include null values.

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

Unique constraints can be created when creating a table or using the ALTER TABLE statement.

3 The difference between unique constraints and primary keys

• Primary Key: All columns that make up the primary key cannot include null values.
• Unique Constraint: Assume that the unique constraint consists of multiple columns, some of which may include null values.
• Oracle does not allow the creation of both a primary key and a unique constraint on the same column.

4 Define unique 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) Based on a single column Demonstration sample of unique constraints:

create table tb_supplier
(
  supplier_id          number not null
 ,supplier_name        varchar2(50)
 ,contact_name         varchar2(50)
 ,CONSTRAINT tb_supplier_u1 UNIQUE (supplier_id)--创建表时创建唯一性约束
);

3) Demonstration sample of unique 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) --定义复合唯一性约束
);

5 Use ALTER TABLE syntax to create unique constraints

1) Syntax

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

2) Demonstration sample preparation, first create the table

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 single column

alter table tb_supplier
add constraint  tb_supplier_u1
unique (supplier_id);

4) Unique constraints based on multiple columns

alter table tb_products
add constraint  tb_products_u1
unique (product_id,product_name);

6 Disable unique constraints

1) Syntax:

ALTER TABLE table_name
DISABLE CONSTRAINT constraint_name;

2) Demonstration example:

ALTER TABLE tb_supplier
DISABLE CONSTRAINT  tb_supplier_u1;

7 Using unique constraints

1) Syntax:

ALTER TABLE tb_supplier
ENABLE CONSTRAINT tb_supplier_u1;

2) Demonstration example:

ALTER TABLE tb_supplier
ENABLE CONSTRAINT tb_supplier_u1;

7 Delete unique constraints

1) Syntax:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

2) Demonstration example:

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

Recommended tutorial: "Oracle Tutorial"

The above is the detailed content of Detailed example of Oracle adding unique constraints. For more information, please follow other related articles on the PHP Chinese website!