How to add primary key in oracle

Method: 1. Use the "alter table table name add constraint primary key name primary key (primary key)" statement to add a named primary key; 2. Use the "alter table table name add primary key (primary key field)" statement to add No named primary key.

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

How to add a primary key in oracle

Primary key explanation:

The unique keyword of a table, such as a student table, the student number cannot be repeated and is unique, the student number is the keyword, that is Primary key.

Difference from foreign keys:

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

Primary key addition, deletion and other operations

1. Named primary key

1) Adding named primary key

①Add when creating a table Primary key (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 yy primary key (primary key 1, primary key 2) ;

2) Deletion with named primary key

ALTER TABLE table_test DROP CONSTRAINT yy;

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

3) Modification with named primary key

Need to delete the primary key first and then add it

2. Unnamed primary key

1) Creation of unnamed primary key

①Add the primary key (primary key "ID" when creating the table The primary key name 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 give an explanation 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

Need to delete the primary key first, and then add it

Recommended tutorial: " Oracle Video Tutorial》

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