How to Create a Table with a Foreign Key in Oracle SQL Developer?

Creating a table with a foreign key in Oracle SQL Developer is a straightforward process that involves defining a relationship between two tables. Here's a step-by-step guide:

1. Create the Primary Table

First, you need to create the primary table that will contain the primary key. The primary key is a unique identifier for each row in the table.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName VARCHAR2(255),
    DepartmentID INT
);

This code creates a table named Employees with columns for EmployeeID, EmployeeName, and DepartmentID. The EmployeeID column is declared as the primary key using the PRIMARY KEY constraint.

2. Create the Foreign Key Table

Next, create the table that will reference the primary table. This table will contain the foreign key, which links its rows to the primary table.

CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR2(255)
);

This code creates a table named Departments with columns for DepartmentID and DepartmentName. The DepartmentID column is declared as the primary key.

3. Add the Foreign Key Constraint

Finally, you need to add the foreign key constraint to the table that references the primary table. This constraint ensures that the foreign key values in the referencing table match existing values in the primary key column of the primary table.

ALTER TABLE Employees
ADD CONSTRAINT FK_Employees_Departments FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID);

This code adds a foreign key constraint named FK_Employees_Departments to the Employees table. The DepartmentID column in Employees is linked to the DepartmentID column in the Departments table.

Example

Let's illustrate this with a practical example. Imagine you have two tables: Customers and Orders. The Customers table contains information about customers, including their CustomerID, CustomerName, and City. The Orders table stores information about orders, including OrderID, CustomerID, and OrderDate.

Here's how you would create these tables with a foreign key constraint:

Customers Table:

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR2(255),
    City VARCHAR2(255)
);

Orders Table:

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE
);

Adding the Foreign Key Constraint:

ALTER TABLE Orders
ADD CONSTRAINT FK_Orders_Customers FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID);

This constraint ensures that the CustomerID values in the Orders table match existing CustomerID values in the Customers table, maintaining data integrity and consistency between the two tables.

Conclusion

By following these steps, you can effectively create tables with foreign keys in Oracle SQL Developer, ensuring data relationships and enforcing referential integrity.

A2oz