How to modify the primary key in oracle

Methods to modify the primary key: 1. Use the "ALTER TABLE table name DROP CONSTRAINT primary key name;" statement to delete the primary key; 2. Use the "alter table table name add primary key (field);" statement to add the primary key.

The operating environment of this tutorial: Windows 7 system, Oracle 11g version, Dell G3 computer.

Explanation of primary key:

The unique keyword of a table. For example, in a student table, the student number cannot be repeated and is unique. The student number is the keyword, that is, the primary key.

Difference from foreign keys:

Foreign keys are fields that are linked to other tables. For example, there is a student table and a course selection table. At this time, the students need to be modified. The student number in the table must also change the corresponding one in the course selection table. In this case, you need to add the student number to the course selection table as a foreign key constraint, so that when you modify the student number, all foreign key associations will be changed

Operations such as adding and deleting primary keys

1. Have named primary keys

1) Add a named primary key

①Add the primary key when creating the table (yy is the primary key name of the primary key "ID")

CREATE TABLE table_test(
 
id INT NOT NULL,       --注意：主键必须非空
 
name VARCHAR(20) NOT NULL,
 
address VARCHAR(20),
 
constraint yy PRIMARY KEY(id)
 
);

②Add the primary key after creating the table

alter table table_test add constraint yy primary key(id);

Formula: alter table table name add constraint primary key name primary key (field);

2) Delete named primary key

ALTER TABLE table_test DROP CONSTRAINT yy;

Formula: ALTER TABLE table name DROP CONSTRAINT primary key name;

##3) Modifications with named primary keys

需先删除主键，再进行添加

2. Unnamed primary key

1) Creation of unnamed primary key

①Add the primary key when creating the table (the primary key name of the primary key "ID" needs to be queried, there are methods below)

CREATE TABLE table_test(

id INT NOT NULL,       --注意：主键必须非空

name VARCHAR(20) NOT NULL,

address VARCHAR(20),

PRIMARY KEY(id)

);

②Add the primary key after creating the table

alter table table_test add primary key (id);

Formula ：

alter table table name add primary key(primary key field 1, primary key field 2...);

2) Deletion of unnamed primary key

① First find out the primary key name (constraint_name), the user_cons_columns table will be explained at the end of the article

SELECT t.* from user_cons_columns t where t.table_name  = 'TABLE_TEST' and t.position is not null;

Formula:

SELECT t.* from user_cons_columns t where t.table_name = 'Table name' and t.position is not null; --The table name must be in uppercase letters, such as: TABLE_TEST

②Execute the deleted SQL

ALTER TABLE table_test DROP CONSTRAINT SYS_C0056038;

formula:

ALTER TABLE table name DROP CONSTRAINT primary key name;

3) Modification of unnamed primary key

需先删除主键，再进行添加

Recommended tutorial: "

Oracle tutorial》

The above is the detailed content of How to modify the primary key in oracle. For more information, please follow other related articles on the PHP Chinese website!