Detailed introduction to the setting process of foreign keys in Oracle

Oracle database is a very popular relational database management system (RDBMS) with powerful features, including support for foreign keys.

Foreign key refers to a field or a group of fields used in one table to relate to a row in another table. It can be used to ensure data integrity and enable query-based data access. Before adding foreign key constraints, the necessary tables and associations between tables must be established in Oracle. In this article, we will introduce in detail the setting process of foreign keys in Oracle.

1. Create tables and relationships
First, we need to create the table that needs to set foreign key constraints and the table pointing to the table. Suppose we want to relate the customers table to the orders table. We can create these two tables in Oracle using the following code:

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  customer_name VARCHAR(50),
  customer_email VARCHAR(100)
);

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  order_date DATE,
  customer_id INT,
  FOREIGN KEY (customer_id)
  REFERENCES customers(customer_id)
);

In the above example, we have created two tables, customers and orders. The order table contains the customer_id field corresponding to the customer, which is the primary key field of the customers table.

2. Add foreign key constraints
After establishing the relationship, we can add foreign key constraints. Foreign key constraints in Oracle can be defined when the table is created or added after the table is created.

In the previous step, we have defined foreign key constraints and added them to the table through FOREIGN KEY and REFERENCES statements. Here, we will explain the meaning of each keyword in detail:

1. FOREIGN KEY: Defines a foreign key field.
2. REFERENCES: Defines a target table for foreign keys and specifies the primary key of the target table.
3. ON DELETE CASCADE: Define the cascade operation when deleting parent table records. When a record in the parent table is deleted, the related records in the child table are also deleted. You can use ON DELETE SET NULL or ON DELETE NO ACTION to specify other operations, such as setting the foreign key to NULL or prohibiting deletion.

In Oracle, the syntax for adding foreign key constraints is as follows:

ALTER TABLE child_table 
ADD CONSTRAINT fk_name 
FOREIGN KEY (child_column) 
REFERENCES parent_table (parent_column);

Among them, child_table is the child table containing the foreign key field, child_column is the name of the foreign key column, and parent_table is The name of the target table, parent_column is the name of the primary key column in the target table. fk_name is the name of the foreign key constraint and must be unique.

For example, in our orders table, we can add the following foreign key constraint (note, this can be done at table creation time via the CREATE TABLE statement):

ALTER TABLE orders 
ADD CONSTRAINT fk_customer_id 
FOREIGN KEY (customer_id) 
REFERENCES customers(customer_id) 
ON DELETE CASCADE;

This will be in the orders table Add a foreign key constraint named fk_customer_id to orders, which refers to the primary key column customer_id in the customers table. When you delete a row in the customers table, the operation also deletes all orders associated with it.

3. Test foreign key constraints
Once the foreign key constraint is added to the table, we can test whether it is working properly. We can test this constraint by inserting a row in the orders table that does not match the customer_id value:

INSERT INTO orders (order_id, order_date, customer_id)
VALUES (1, '2021-01-01', 100);

-- ERROR: ORA-02291: integrity constraint (fk_customer_id) violated - parent key not found

This will cause an error because it references a customer_id value that does not exist in the customers table. We can use the integrity constraint-related error code ORA-02291 to verify that the foreign key is working.

4. Summary
In Oracle, foreign keys are an important tool to ensure data integrity and maintain relationships between tables. We can use the FOREIGN KEY and REFERENCES keywords to define foreign key constraints and define cascade operations using ON DELETE CASCADE, SET NULL or NO ACTION. After defining the foreign key constraint, we can test whether it is working properly and ensure that it functions in database operations.

The above is the detailed content of Detailed introduction to the setting process of foreign keys in Oracle. For more information, please follow other related articles on the PHP Chinese website!