How to set up foreign keys when creating a database in Oracle?

To create a foreign key in Oracle, you need to use the ALTER TABLE statement. The specific steps are as follows:

First, create a primary table and a secondary table, ensuring that the primary table has a column serving as a foreign key, and the secondary table has a corresponding column that matches the foreign key column in the primary table.

2. Add a foreign key using the ALTER TABLE statement, the syntax is as follows:

FOREIGN KEY (<从表外键列>)
REFERENCES <主表名称> (<主表主键列>);

For example, to set the dept_id column in the departments table as a foreign key referencing the employee_id column in the employees table, you can use the following statement:

ALTER TABLE departments
FOREIGN KEY (dept_id)
REFERENCES employees(employee_id);

Before creating the foreign key, make sure that the data types and lengths of the primary and foreign key columns in the main table match, and that the primary key column in the main table has an index. This will ensure the successful creation of the foreign key by executing the above SQL statement.

Attention: Ensure that the values in the foreign key column already exist in the primary table when creating foreign keys, otherwise it will result in a foreign key constraint violation error.

Leave a Reply 0

Your email address will not be published. Required fields are marked *