English
Menu

What is Foreign key?

A column that refers to the primary key (or a unique column) of another table. Through the referential integrity constraint, which allows only values that exist in the referenced table or NULL, it prevents data that points to nonexistent rows. The behavior when a referenced row is deleted or updated (rejection, cascading deletion and so on) can also be specified.

Japanese name: 外部キー

Foreign key appears in 3 practice questions across 1 topic, including Database Basics.

Field: Database Basics

Try it: questions about Foreign key

The primary key of a customer table (cust_id, name) is cust_id, and cust_id in an orders table (order_id, cust_id) is defined as a foreign key referring to cust_id in the customer table. The customer table contains only the rows with cust_id 'C1' and 'C2'. What happens when INSERT INTO orders VALUES ('O9', 'C5') is executed in this state?

  1. AThe row is not added and an error occurs, because it violates the referential integrity constraint
  2. BThe row is added, and a row with cust_id 'C5' is automatically added to the customer table as well
  3. CThe row is added, but its cust_id value is replaced with NULL
Show answer and explanation

Answer: A. The row is not added and an error occurs, because it violates the referential integrity constraint

A foreign key is a column that refers to the primary key (or similar) of another table, here the customer table, and it enforces the referential integrity constraint: only values that exist in the referenced table, or NULL, may be stored. The customer table has no customer 'C5', so the DBMS rejects the attempt to add an order for a nonexistent customer, and an error occurs. This prevents orders that belong to no known customer. Conversely, an attempt to delete a customer row that still has orders is also rejected under the default behavior (NO ACTION). Adding ON DELETE CASCADE to the foreign key definition makes deleting a customer also delete that customer's orders in a cascade.

Foreign key in the explanations

Questions that mention Foreign key (3)

  1. The primary key of a customer table (cust_id, name) is cust_id, and cust_id in an orders table (order_id, cust_id) is defined as a foreign key referring to cust_id in the customer table. The customer table contains only the rows with cust_id 'C1' and 'C2'. What happens when INSERT INTO orders VALUES ('O9', 'C5') is executed in this state?
  2. The primary key of an employee table (emp_no, name, dept_code, dept_name) is emp_no, and every column holds a single value. The functional dependencies are emp_no → name, emp_no → dept_code and dept_code → dept_name. Which correctly states the normal form of this table?
  3. In an E-R diagram, there is a many-to-many relationship between students and courses: one student takes several courses, and one course is taken by several students. Which is an appropriate way to implement this relationship with relational database tables?

Books on Database Basics Ad

As an Amazon Associate I earn from qualifying purchases.

Semiconductor, Electronics and Computing Calculators

SQL PracticeRun SQL in your browser and check the results, with 10 practice exercises that grade your answers

Related terms

Primary key Third normal form (3NF) E-R diagram Relational database ACID properties Database index Database view Isolation level Normalization NoSQL

Back to the glossary