How to Create a Foreign Key on a Table in Oracle SQL

In this article, I’ll explain how to create a foreign key on a table in Oracle SQL.

What Is a Foreign Key?

First, let’s explain what a foreign key is.

A foreign key is a constraint that’s added to a table. It allows you to specify that a column in a table refers to the primary key of another table.

It’s used to relate data in two tables and improve the integrity of your data.

There are two ways to create a foreign key on a table in Oracle: the inline method and the out-of-line method. I’ve documented them both in my guide to the CREATE TABLE statement here and I’ll explain them in this article for you.

Foreign Key Using an Inline Constraint

To create a foreign key using the inline constraint syntax, use a command like this:

CREATE table_name (
column_name data_type REFERENCES other_table_name (other_column_name)
…
);

Using this syntax, you specify your CREATE keyword, then the table name, then open brackets. For the column, you want to specify as the foreign key, and add the word REFERENCES to the end (after the data type).

Then, specify the name of the other table. This is the table that contains the primary key that your new foreign key will link to. For example, if you have a department table, and you’re creating a new employee table that has a department ID field in it, your “other table” would be the department table.

Next, specify the name of the column that the foreign key refers to in the other table, inside brackets. If we use the employee and department table, this would be the department_id column (or whatever you have called the primary key in the department table).

Finally, continue to define your columns and table as normal. Your foreign key will be created when you run the statement.

Foreign Key Using an Out-of-Line Constraint

The other way that you can declare a foreign key is using the out-of-line constraint method.

This is done by declaring a constraint after all of your columns have been declared.

The advantage of this method is that you can give your foreign key constraint a name, which is helpful for altering, enabling, and disabling it at a later date. Using the inline method, a name is generated automatically by the Oracle database.

The syntax for creating a foreign key using the out-of-line method is:

CREATE table_name (
column_name data_type,
...
CONSTRAINT fk_tbl1_tbl2
  FOREIGN KEY (this_tables_column)
  REFERENCES other_table_name (other_column_name)
);

You start by declaring your table name and all of your columns as required.

After all of the columns are declared, but are still inside the brackets, you add the word CONSTRAINT. This denotes that you will be defining a constraint.

Next, provide a name for your new constraint. This can be up to 30 characters. I prefer to stick to a standard when naming my constraints, so I start the names with fk.

After naming your constraint, add the words FOREIGN KEY to specify that it is a foreign key constraint.

Then, open brackets and add in the name of the column in this table that will be the foreign key. Then, close the brackets.

Next, add the word REFERENCES, then the name of the other table you’re referring to. Using the example above, if you’re creating an employee table and want a department_id to refer to the department table, this would be department table. 

Then, open the brackets, add in the name of the column in the other table (in this case, the department table), and then close the brackets.

Finally, close the brackets to the create table statement and add a semicolon. The statement is now ready to run. The foreign key will be created with the name you have specified.

More information on foreign key constraint parameters is available in the Oracle documentation.

So, that’s how you can create a foreign key on a new table. Leave any questions in the comments!