Oracle Primary Key usage explanation, oracleprimary

Oracle Primary Key usage explanation, oracleprimary

Oracle/PLSQL: Primary Key Usage

1 goal

The example shows how to create, delete, disable, and enable a primary key.

2 preface-what is a primary key?

In Oracle, a primary key indicates a single data table column that uniquely identifies a record or a joined data table column (Federated primary key | composite primary key ). The data in the data table column used by the primary key cannot contain null values. A table can only contain one primary key.

Note: in Oracle databases, the number of columns associated with primary keys cannot exceed 32. A primary key can be defined when a TABLE is created or by using the alter table syntax.

3. Create a primary key-define a primary key when creating a table

Example of a Single-Column primary key:

Create table TB_PK_EXAMPLE (ID number, NAME varchar2 (50), DESCRIPTION varchar2 (300), CONSTRAINT TB_PK_EXAMPLE_PK primary key (ID) -- Define the primary key );

Example of joint primary key:

Create table partition (supplier_id number, supplier_name varchar2 (50), incluvarchar2 (300), contact_name varchar2 (50), constraint incluprimary key (supplier_id, supplier_name) -- Federated primary key );

4. Create a primary key-use alter table syntax

Syntax

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

Sample Preparation

Create two tables (tb_employees and tb_scripts). The script is as follows:

create table tb_employees(employee_id number,employee_name varchar2(50),employee_age number,employee_birth date,department_id number);create table tb_departments(department_id number,department_name varchar2(100),location varchar2(300));

Same as the alter table syntax to create a primary key:

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

5. Disable primary keys

Syntax:

ALTER TABLE table_name DISABLE CONSTRAINT constraint_name;

Example:

alter table tb_employees disable constraint tb_employees_pk;

6. enable primary keys

Syntax:

ALTER TABLE table_name ENABLE CONSTRAINT constraint_name;

Example:

alter table tb_employees enable constraint tb_employees_pk;

7. delete a primary key

Syntax:

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;

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

Why Does oracle Add a not null primary key after the primary key when defining the primary key?

It is true that primary key is equal to unique key plus not null, but in the end, primary key is only a special key in index, however, there is no rule that the existence of primary cannot be anything else. Is an idea of preemptible, and adding not null is not an error.

How Does oracle cancel the primary key attribute of a field?

Alter table drop key