Oracle's primary KEY (Primary key) usage detailed

Oracle/plsql: Primary KEY (Primary key) usage

1 Goals

Examples explain how to create, delete, disable, and turn on primary keys.

2 Preface-What is the primary key

In Oracle, a primary key refers to a single data table column or union data table column (Union PRIMARY key | Composite primary key) that uniquely identifies a record. The Data table column data used by the primary key cannot contain null values. Also, a table can contain only a single primary key.

Description: In an Oracle database, there cannot be more than 32 columns for a federated primary key. The primary key can be defined when the table is created or by the ALTER TABLE syntax.

3 Creating a primary key-defining a primary key when creating a table

Example single-column primary key:

CREATE TABLE tb_pk_example (  ID number,  NAME varchar2 (),  DESCRIPTION varchar2,  CONSTRAINT tb_ PK_EXAMPLE_PK PRIMARY Key (ID)--Define primary key);

Example of Federated primary key:

CREATE TABLE tb_supplier_ex (  supplier_id number,  supplier_name varchar2 (),  supplier_description VARCHAR2 (+),  contact_name varchar2 (+),  constraint TB_SUPPLIER_EX_PK primary key (supplier_id, Supplier_ Name)--Federated primary key);

4 Creating a primary key-using the ALTER TABLE syntax

Grammar

ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY (col1, col2,... coln);

Sample preparation

Create two tables (Tb_employees and tb_departments) with the following script:

CREATE TABLE Tb_employees (employee_id number,employee_name varchar2 (), Employee_age Number,employee_birth date, department_id number); Create Table tb_departments (department_id number,department_name varchar2 (+), location VARCHAR2 (300));

Create a primary key with the ALTER TABLE syntax:

--single-column primary key ALTER TABLE tb_employees ADD constraint TB_EMPLOYEES_PK primary key (EMPLOYEE_ID);--Union PRIMARY key ALTER TABLE tb_departments Add constraint TB_DEPARTMENTS_PK primary key (Department_id,department_name);

5 Disabling primary keys

Grammar:

ALTER TABLE table_name DISABLE CONSTRAINT constraint_name;

Example:

ALTER TABLE tb_employees disable constraint tb_employees_pk;

6 Enable primary key

Grammar:

ALTER TABLE table_name ENABLE CONSTRAINT constraint_name;

Example:

ALTER TABLE tb_employees enable constraint tb_employees_pk;

7 Deleting a primary key

Grammar:

ALTER TABLE table_name DROP CONSTRAINT constraint_name;

Example:

ALTER TABLE tb_employees drop constraint tb_employees_pk;alter table tb_departments drop constraint tb_departments_pk; ALTER TABLE tb_pk_example drop constraint tb_pk_example_pk;alter table TB_SUPPLIER_EX drop constraint tb_supplier_ex_pk;

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

If you have any problems in the process of trying, or if my code is wrong, please correct me, thank you very much!

Contact information: [Email protected]

Copyright @: Reprint please indicate the source!
--------------------------------------------------------------------------------------------------------------- -----

Oracle's primary KEY (Primary key) usage detailed