Relational Data Modeling - Foreign Key Constraint (one-to-many relationship)

Data System Architecture

About

A Foreign Key Constraint is a constraint that creates a one to many relationship between a primary key and a foreign key (ie between two tables)

The foreign key constraint on a table A will :

A foreign key constraint is also known as foreign key/primary key relationships

Referential Integrity is the data property whereby a foreign Key in one table has a corresponding Primary key in a different table.

Enforcement

A relational database management system (RDBMS) enforces a foreign key constraint :

  • normally either by deleting the foreign key rows as well to maintain integrity,
  • or by returning an error and not performing the delete.

Which method is used would be determined by the referential integrity constraint, as defined in the data dictionary.

Pro / cons

  • sharding: Extracting data with foreigns keys is hard and therefore sharding is hard
  • Performance: An index lookup is executed for each insert/delete

Example

An Foreign Key example of a database (with the scott schema from Oracle).

Consider a part of the EMP (Employe) table.

EMPNO ENAME DPTNO
7369 SMITH 10
7499 ALLEN 20
7566 JONES 30

The column DPTNO from the EMP table refer to the column DEPTNO from the table DEPT (Department) below :

DEPTNO DNAME
10 ACCOUNTING
20 RESEARCH
30 DISTRIBUTION

For each DPTNO in the table EMP, you must have a DPTNO in the table DEPT. We say then that :

  • the column DPTNO from the table EMP is a foreign key.
  • and then that the column DEPTNO is a primary key.

If it's not true, there is NO referential integrity.

Documentation





Discover More
Dataquality Metrics
Data Quality - Metrics

Very often these metrics are named dimension but it is an abuse language, it acts rather in a dimensional model as attribute of the data rules. Timeliness refers to the time expectation for...
Star Schema
Dimensional Data Modeling - Star Schema

A star schema is a relational schema that is a dimensional: with a single fact table and many foreign key relationships with several dimension tables. The characteristic star-like structure is often...
Data System Architecture
Logical Data Modeling - Dependency (Coupling)

A dependency is a Relationship that signifies that a entity requires other entity for their specification or implementation. This means that the complete semantics of the client (the dependent) are either...
Less Than Or Equal Relation
Logical Data Modeling - Relationship Representation / Implementation / Visualisation

A relationship can be described/implemented by: a function a a a a A relationship can be represented as a function Example with a three element (ternary) relationship: A binary...
Card Puncher Data Processing
Oracle Database - Rowid Scans operation

The rowidscan is a access path used by the query optimizer to produce the best explain plan. The rowid of a row specifies the datafile and data block containing the row and the location of the row in...
Data System Architecture
Relational Data Modeling - (Integrity) (Constraints|action assertions)

Constraints are a set of rule inside a relational database that declare consistency rules in order to: enforce data integrity and give information on the data (used by the query optimizer) Every...
Data System Architecture
Relational Data Modeling - Foreign Key

A foreign key is a metadata instruction that declare that a column in a table (called foreign table) holds data from another table primary key column. A foreign key is declared with the creation of...
Data System Architecture
Relational Data Modeling - One-to-many / May-to-one Relationship

This article is the one-to-many relationship in a relational model. For example, in a geography schema having tables Region, State, and City: many states are in a given region, but no states are...
Data System Architecture
Relational Data Modeling - Primary key

A primary key is a one or more columns that holds the primary key of a row in a table. A Primary Key identify: the lowest level of a table the unique identifier of a single row doesn't change...
Data System Architecture
Relational Data Modeling - Referential Integrity (cascade)

Referential integrity concerns the concept of foreign key to ensures that the one-to-many relationship between tables remain consistent. When one table has a foreign key to another primary key table,...



Share this page:
Follow us:
Task Runner