How to Create Table with Foreign Key in Oracle

Introduction

If you are working with large amounts of data in an Oracle database, you may have come across the need to establish relationships between tables. This is where the concept of foreign keys comes in. Creating tables with foreign keys in Oracle can help maintain data integrity and improve overall database performance. In this article, we will explore the process of creating tables with foreign keys in Oracle, including the syntax and best practices to follow.

How to Create Tables with Foreign Keys in Oracle

Creating tables with foreign keys in Oracle is essential in establishing relationships between tables in a relational database. The process involves specifying a column or set of columns in one table as a foreign key referencing the primary key column(s) in another table. Let’s take a closer look at the steps involved.

The syntax for Creating Tables with Foreign Keys in Oracle

To create a table with a foreign key in Oracle, you must include the CONSTRAINT clause and specify the foreign key name and the name of the referenced primary key column(s). Here’s the basic syntax:

CREATE TABLE table_name (
  column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...
  CONSTRAINT constraint_name
    FOREIGN KEY (foreign_key_column)
    REFERENCES referenced_table (primary_key_column)
    [ON DELETE {CASCADE | SET NULL}]
    [ON UPDATE {CASCADE | SET NULL}];
);

Let’s break down the different parts of this syntax:

• CREATE TABLE table_name: This is the standard syntax for creating a new table in Oracle.
• (column1, column2, …): This specifies the columns in the new table that will be part of the foreign key constraint.
• CONSTRAINT constraint_name: This optional clause allows you to name the foreign key constraint for easier reference later on.
• FOREIGN KEY (column1, column2, …): This clause specifies that the listed columns in the new table will form a foreign key constraint.
• REFERENCES parent_table (column1, column2, …): This clause specifies the parent table and columns that the foreign key constraint will reference.
• [ON DELETE { CASCADE | SET NULL }]: This clause specifies the action that should be taken if a row in the parent table is deleted. CASCADE will delete all corresponding rows in the child table, while SET NULL will set the foreign key value in the child table to NULL.
• [ON UPDATE { CASCADE | SET NULL }]: This clause specifies the action that should be taken if a row in the parent table is updated. CASCADE will update all corresponding rows in the child table, while SET NULL will set the foreign key value in the child table to NULL.

Specifying the Foreign Key Constraint and Referential Actions

When creating a foreign key in Oracle, you can specify the constraint name, the parent table, and the parent column using the following syntax:

CONSTRAINT constraint_name FOREIGN KEY (child_column) REFERENCES parent_table (parent_column) [ON DELETE {CASCADE|SET NULL|SET DEFAULT}] [ON UPDATE {CASCADE|SET NULL|SET DEFAULT}];

The CONSTRAINT keyword specifies the name of the foreign key constraint. The FOREIGN KEY keyword indicates that a foreign key constraint is being defined. The child_column is the column in the child table that references the parent table. The parent_table and parent_column refer to the table and column being referenced by the foreign key.

The ON DELETE and ON UPDATE clauses define the actions to be taken when a referenced row is deleted or updated. The three possible actions are CASCADE, SET NULL, and SET DEFAULT.

• CASCADE deletes or updates the child table’s corresponding row(s) when the parent row is deleted or updated.
• SET NULL sets the value of the foreign key column to NULL in the child table when the parent row is deleted or updated.
• SET DEFAULT sets the value of the foreign key column to its default value in the child table when the parent row is deleted or updated.

Example of Creating Tables with Foreign Keys in Oracle

Let’s create two tables, customers and orders, and define a foreign key relationship between them:

CREATE TABLE customers (
   customer_id NUMBER(10) PRIMARY KEY,
   customer_name VARCHAR2(50)
);

CREATE TABLE orders (
   order_id NUMBER(10) PRIMARY KEY,
   order_date DATE,
   customer_id NUMBER(10),
   CONSTRAINT fk_orders_customers
      FOREIGN KEY (customer_id)
      REFERENCES customers(customer_id)
      ON DELETE CASCADE
);

In this example, the “customers” table has a primary key constraint on the “customer_id” column, and the “orders” table has a foreign key constraint on the “customer_id” column that references the “customer_id” column in the “customers” table.

The foreign key constraint is named “fk_orders_customers“, and the “ON DELETE CASCADE” clause specifies that if a row is deleted from the “customers” table, all corresponding rows in the “orders” table will also be deleted.

With this example, we have successfully created a foreign key relationship between the “customers” and “orders” tables in Oracle.

How to Create Tables with Foreign Keys using Oracle SQL Developer

Creating tables with foreign keys in Oracle SQL Developer is a user-friendly and efficient way to ensure data integrity in your database. By using the UI instead of SQL code, you can visually see and manage your tables and their relationships. This section will guide you through the process of creating tables with foreign keys using Oracle SQL Developer step-by-step.

1. Open Oracle SQL Developer and connect to your database.
2. Head over to the “Connections” tab and expand it, right-click on your schema, and select “New Table“.

3. In the “Create Table” window, enter the name of your table and its columns.
4. To add a foreign key constraint, check the “Advanced” checkbox, then click on the “Constraints” tab.

5. Click the “Add Foreign Key Constraint” option to add a new “Foreign Key Constraint“.

6. In the “Create Foreign Key Constraint” window, specify the constraint’s name, the source and target columns, and the referential actions.

7. Click “Apply” to create the foreign key constraint.
8. Once you’ve added all the necessary columns and constraints, click “Save” to create the table.

How to Create Tables with Foreign Keys using TOAD

In addition to Oracle SQL Developer, TOAD is another popular tool for creating and managing Oracle databases. In this section, we will discuss the steps for creating tables with foreign keys using TOAD’s graphical user interface (UI). This method allows for a more visual approach to creating tables with foreign keys and can benefit those who prefer a more intuitive approach to database management.

Here are the steps to create tables with foreign keys using TOAD UI:

1. Open TOAD and connect to your Oracle database.
2. In the top menu, click “Database” and select “Schema Browser“.

3. In the left panel of the Schema Browser, select the schema where you want to create your tables.
4. Click on the icon “Create Table“.

5. Specify the table and column names in the “Create Table” window.

6. In the “Constraints” tab, click the “Add” button to add a new constraint.
7. In the “Add Constraint” window, select “Foreign Key” as the constraint type.

8. Specify the name of the foreign key constraint and the referenced table and column.

9. Optionally, specify any referential actions for the foreign key constraint.

10. Click “Apply” to create the table with the foreign key constraint.

By following these steps, you can easily create tables with foreign keys using TOAD UI without the need for SQL code.

Best Practices for Using Foreign Keys in Oracle

Foreign keys play an important role in maintaining the integrity of the database by ensuring that the data in one table is consistent with the data in another table. However, following some best practices when using foreign keys in Oracle is essential to ensure optimal performance and maintainability.

Naming Conventions and Indexing

Using meaningful and consistent names for foreign keys can make understanding the relationships between tables in the database easier. A common convention is to prefix the name of the foreign key with “fk_” followed by the name of the referencing table and the referenced column. For example, if a foreign key references the “customer_id” column in the “customers” table, the foreign key could be named “fk_customers_customer_id“.

Creating indexes on foreign key columns to improve query performance is also important. When a foreign key is used in a query, Oracle can quickly use the index to find the corresponding rows in the referenced table. To create an index on a foreign key column, you can use the following syntax:

CREATE INDEX index_name ON table_name (column_name); 

Handling NULL Values in Foreign Key Columns

In some cases, allowing NULL values in a foreign key column may be necessary. For example, if a customer has not yet placed an order, the foreign key column in the “orders” table that references the “customer_id” column in the “customers” table may be NULL.

You can add the “NULL” keyword to the foreign key definition to allow NULL values in a foreign key column. For example:

Consider a database that stores sales proposals. Each proposal is assigned to one salesperson and one client. The proposal table would have two foreign keys, one with the client ID and one with the sales rep ID. However, when a proposal is first created, a sales rep may not yet be assigned to it. In this case, the sales rep ID would be left null. This allows you to create proposals without assigning a sales rep to them immediately and then update the sales rep ID field with the appropriate value when a sales rep is eventually assigned to a proposal.

CREATE TABLE proposals (
proposal_id INT PRIMARY KEY,
client_id INT NOT NULL,
sales_rep_id INT,
proposal_data VARCHAR(255),
FOREIGN KEY (client_id) REFERENCES clients(client_id),
FOREIGN KEY (sales_rep_id) REFERENCES sales_reps(sales_rep_id)
);

In this example, the “proposals” table has two foreign keys: “client_id” and “sales_rep_id“. The “client_id” field is defined as “NOT NULL” meaning it cannot have a null value. However, the “sales_rep_id” field is not defined as “NOT NULL“, meaning it can have a null value.

This allows you to create proposals without assigning a sales rep to them immediately. When a sales rep is eventually assigned to a proposal, you can update the “sales_rep_id” field with the appropriate value.

Conclusion

In this article, we have discussed the importance of using foreign keys in Oracle databases and the best practices for implementing them. We have covered the syntax for creating tables with foreign keys and how to specify the foreign key constraint and referential actions.

We have also emphasized the importance of following naming conventions, indexing best practices for foreign keys, and handling NULL values in foreign key columns. By implementing these best practices, we can maintain data integrity and ensure accurate data relationships, leading to better performance and fewer errors.

In conclusion, using foreign keys in Oracle databases is crucial for maintaining data integrity and ensuring accurate data relationships. Following best practices for naming conventions and indexing and handling NULL values in foreign key columns is essential. We encourage readers to implement foreign keys in their Oracle databases to improve performance and reduce errors.