Oracle Primary Key usage explanation, oracleprimary

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.