What are the Differences Between a Primary Key vs Foreign Key?

I explain what a primary key and foreign key is, as well as the differences between primary key vs foreign key in this article.

What Is a Primary Key?

What is the primary key definition?

A primary key is one or more columns in a table that are used to uniquely identify the row.

When you’re working with a relational database, you have multiple tables and you need to link them to each other. There needs to be a way to identify records, even if data changes. The way to do this is using a primary key.

It’s kind of like how when you speak to your bank, insurance company, or government department, they ask for an ID:

  • Banks ask for a customer or account number.
  • Insurance companies ask for your member number.
  • Government departments ask for your Tax File Number (here in Australia) or perhaps your Social Security Number in the US.

That way, it doesn’t matter if you change your name or address, there is always an identifier that can be used to find your information.

This is what a primary key is. The primary key definition is a column or set of columns used to uniquely identify a row.

Want an Oracle SQL function cheat sheet for easy reference? Click here to get a PDF file that contains all of Oracle functions for you to reference or print out.

 

What are the Primary Key Requirements?

A primary key has several requirements.

First, the data in the columns must be unique. If there is a second record that is added with the same values for the primary key as an existing record, the new record won’t be inserted. You’ll get an error saying the primary key says a record already exists.

Also, the columns in the primary key cannot contain NULL values. You must specify a value.

Finally, a table can have only one primary key. You can’t create a second primary key on a different set of columns after you’ve created your primary key.

This is what a primary key might look like when represented in a table.

Employee Primary Key

This employee table has several fields. The employee_id field is the primary key in this example, and has been indicated using “PK” on the left.

It means that each value of the employee_id column is unique and can be used to identify a single row. Each record in a table is uniquely identified by a primary key.

 

What is a Foreign Key?

A foreign key is a column or set of columns in a table that refers to a primary key in another table.

They are used to specify that this record in this table is related to that record in that table.

This is an example of what a foreign key would look like on a table.

Employee Primary Key and Foreign Key

This image shows that the department table has a primary key of department_id. There is also a field in the employee table called department_id.

The department_id field in the employee table is the foreign key. It refers to the department_id column in the department table, which is the primary key of that table.

Why would you need a foreign key?

Using the example above, an employee works in a department. To be able to see which department the employee works in, there needs to be information in the employee record to hold this.

We could store the department name, but what if the department name changes? We would have to update every record in the employee table. What if we miss one?

Also, how do we know it’s unique? Are we sure that “Sales” and “Salespeople” are the same department, or are they different?

If we use a foreign key, then it refers to a specific record in another table and is accurate.

So, the foreign key definition is a column or set of columns that is used to refer to another table’s primary key.

 

What is the Difference Between a Primary Key and a Foreign Key?

Now that we’ve explained the primary key definition and the foreign key definition, let’s see what the differences are.

This table here highlights some of the differences in Oracle SQL between primary and foreign keys.

Primary Key vs Foreign Key

Characteristic Primary Key Foreign Key
How many can a table have? One One or many
How are indexes created? Created automatically Not created automatically but can (and should) be
Can have more than one column Yes Yes
Can have duplicate values No Yes
Can have NULL values No Yes
Uniquely identify a row in a table Yes Not always

In summary:

  • A table can only have one primary key, but can have many foreign keys. A table doesn’t need a primary key or foreign key in Oracle.
  • Indexes are automatically created on primary keys in Oracle, but not for foreign keys.
  • Both primary keys and foreign keys can be created on more than one column. Oracle allows up to 32 columns.
  • Primary keys cannot contain duplicate values, but foreign keys can. This is how a “one to many” relationship is represented.
  • A primary key cannot contain NULL values in any columns, but foreign keys can.
  • A primary key is used to uniquely identify a row. A foreign key doesn’t enforce this, but it could be (if there is only one matching value)

 

How Can I Create a Primary Key in Oracle?

There are two ways to create a primary key in Oracle:

  • With the table as part of the CREATE TABLE statement
  • Using the ALTER TABLE statement if the table already exists

There is no CREATE PRIMARY KEY command that you can use.

Let’s see how to create one with the CREATE TABLE command. I’ve detailed more on this command in my Guide on Creating Tables in Oracle.

These commands use the keyword CONSTRAINT, because a primary key (and a foreign key) is a type of constraint.

CREATE TABLE employee (
  employee_id NUMBER(10),
  first_name VARCHAR2(100),
  last_name VARCHAR2(100),
  salary NUMBER(8),
  department_id NUMBER(10),
  CONSTRAINT employee_pk PRIMARY KEY (employee_id)
);

This will create a primary key with the name of “employee_pk” on the column “employee_id”.

If the employee table already exists, but has no primary key, you can add one using the ALTER TABLE statement. You can read more about this statement in my Guide to the ALTER TABLE statement here.

To add a primary key using ALTER TABLE:

ALTER TABLE employee
ADD CONSTRAINT employee_pk PRIMARY KEY (employee_id);

This will create a primary key with the name of “employee_pk” on the column “employee_id”.

In Oracle, you can also drop primary keys:

ALTER TABLE employee DROP CONSTRAINT employee_pk;

You can disable primary keys:

ALTER TABLE employee DISABLE CONSTRAINT employee_pk;

You can also enable primary keys:

ALTER TABLE employee ENABLE CONSTRAINT employee_pk;

 

How Can I Create a Foreign Key in Oracle?

Similar to creating a primary key, you can create a foreign key on a table in two ways:

  • Using the CREATE TABLE statement
  • Using the ALTER TABLE statement

Let’s see how to create a foreign key using CREATE TABLE. We’ll use our earlier example and create one for the department_id column in the employee table.

CREATE TABLE employee (
  employee_id NUMBER(10),
  first_name VARCHAR2(100),
  last_name VARCHAR2(100),
  salary NUMBER(8),
  department_id NUMBER(10),
  CONSTRAINT fk_emp_deptid
    FOREIGN KEY (department_id)
    REFERENCES department(department_id)
);

As you can see, the command here is a bit longer than creating a primary key. The syntax can be hard to remember as well, but the order of the words is:

  • CONSTRAINT
  • Then the constraint name, which I’ve called “fk_emp_deptid”. This way I know it’s a foreign key, on the employee table, and refers to the department_id column.
  • FOREIGN KEY
  • In brackets, the name of the column in this table.
  • REFERENCES
  • The name of the table it refers to
  • In brackets, the name of the column in the other table.

You can also create a foreign key using ALTER TABLE. The command looks similar.

ALTER TABLE employee
ADD CONSTRAINT fk_emp_deptid
FOREIGN KEY (department_id)
REFERENCES department(department_id);

This adds a similar foreign key constraint that we added earlier.

In Oracle, you can also drop foreign keys:

ALTER TABLE employee DROP CONSTRAINT fk_emp_deptid;

You can disable foreign keys:

ALTER TABLE employee DISABLE CONSTRAINT fk_emp_deptid;

You can also enable foreign keys:

ALTER TABLE employee ENABLE CONSTRAINT fk_emp_deptid;

 

How Can I View Primary Keys and Foreign Keys in SQL Developer?

To view primary keys and foreign keys in SQL Developer, first expand the connection you’re using.

SQL Developer Expand Connection

Then, expand Tables (which may be called Tables (Filtered))

SQL Developer Expand Tables

Then, click once on the name of the table you want to view the constraints for, or right-click and select Open (if clicking once doesn’t open it due to your settings).

SQL Developer View Table Details

Next, click on the Constraints tab.

SQL Developer View Constraint Details

You’ll see a list of constraints for the table. The CONSTRAINT_TYPE column will help you idenfity you primary key vs foreign key

 

Can a Primary Key be a Foreign Key?

The short answer is yes.

However, primary keys only allow unique values, and foreign keys allow for duplicates, so a primary key on its own would most likely not be suitable.

You can have part of the foreign key in the primary key, if required, but it’s better to create separate columns for these.

 

What’s the Difference Between a Primary Key and a Unique Key?

A primary key and a unique key are similar, but there are some differences.

Characteristic Primary Key Unique Key
How many can a table have? One One or many
How are indexes created? Created automatically Created automatically
Can have more than one column Yes Yes
Can have duplicate values No No
Can have NULL values No Yes
Uniquely identify a row in a table Yes Yes

The decision on whether to create a primary key or a unique key depends on what your aim is.

If it’s the key used to uniquely identify the record and to be linked to other tables using foreign keys, create a primary key. If it’s a constraint to ensure a value is unique, but not linked to other tables, use a unique index.

 

Why Do Relational Databases Use Primary Keys and Foreign Keys?

The main reason is to create logical relationships between two tables.

Primary keys are used to identify a record in a table. Foreign keys are used to relate one record to another record in a different table.

There is extra logic built into the database management system that enforces these rules. For example, most of the time you can’t delete a primary key record that has foreign keys related to it. This is to prevent “orphaned records”, or records where the foreign key points to a primary key that no longer exists.

 

So, that’s the definition of a primary key and foreign key, as well as the differences between them. If you have any questions, leave a comment below.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

Want an Oracle SQL function cheat sheet for easy reference? Click here to get a PDF file that contains all of Oracle functions for you to reference or print out.

Improve Your Oracle SQL With My 10-Day Email Course

x