How to modify foreign keys in oracle

In Oracle, you can use the ALTER statement to modify foreign keys. The syntax is "ALTER TABLE table name ADD CONSTRAINT constraint foreign KEY (column_name) references table name (id)".

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

How to modify foreign keys in oracle

Log in to the oracle database, and the logged-in user is required to have permission to create objects. Here we take the SCOTT user as an example, using SQL tools to connect to the database.

Create two tables to achieve foreign key association.

create table main_tab
(
id number,
name varchar2(30)
);
create table sub_tab
(
id number,
main_id number,
name varchar2(30)
);

Here you need to set the main_id in sub_tab as a foreign key. The prerequisite for setting foreign keys is that main_id must be the primary key of main_tab. So you need to set the primary key of main_tab first.

The code is as follows:

ALTER TABLE main_tab ADD CONSTRAINT pk_main_tab PRIMARY KEY(id);

At this time, you can set the foreign key in sub_tab.

The code is as follows:

ALTER TABLE sub_tab ADD CONSTRAINT fk_sub_tab foreign KEY (main_id) references main_tab(id);

The alter table command is explained here.

ALTER TABLE sub_tab ADD CONSTRAINT fk_sub_tab foreign KEY (main_id) references main_tab(id);

alter table table_name-----------------------means to change a certain table

add constraint constraint_name- ------------It means to add constraints/restrictions to a certain table

foreign key(column_name)----------------- -The description is a foreign key constraint, and it belongs to a certain column.

references table_name(id);------------------Specify which table this foreign key belongs to

The foreign key must be the primary key of the main table.

Recommended tutorial: "Oracle Video Tutorial"

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