Home  >  Article  >  Database  >  How to modify foreign keys in oracle

How to modify foreign keys in oracle

WBOY
WBOYOriginal
2022-03-03 16:34:493184browse

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)".

How to modify foreign keys in oracle

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

How to modify foreign keys in oracle

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

How to modify foreign keys in oracle

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

How to modify foreign keys in oracle

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

How to modify foreign keys in oracle

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!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn