Handling Foreign Key Relationships

A FOREIGN KEY requires a value to reference an existing row in another table.

Programming

A Safe Way to Reuse Records

Many applications need a record to exist before they can use its primary key. The record might be a username, a tag, or reference data. The difficulty is that the record may already exist. A normal insertion that violates a constraint raises an error and rejects the operation. INSERT OR IGNORE changes that outcome: if the attempted row would violate a constraint, the database skips the insertion and lets the program continue.

Key point

The pattern has two logical steps: attempt to insert the record without interrupting the program, then select the primary key for the record that now exists.

What the Insertion Decides

What happens when an insertion either satisfies or violates a constraint?

There are two possible outcomes. If the new row does not violate a constraint, it is inserted normally. If it would violate a constraint, the row is not added, but the program continues without the insertion error that a normal INSERT would produce. The ignored insertion does not itself tell you the primary key, so a follow-up SELECT is needed when the application must use that record elsewhere.

Why Uniqueness Matters

A UNIQUE constraint restricts duplicate values in one or more columns. If a username column is UNIQUE, two rows cannot have the same username. Attempting to insert a second row with that username creates a constraint violation, so INSERT OR IGNORE skips the second insertion.

Table definition	Repeated value	INSERT OR IGNORE result	SELECT reliability
Column has a UNIQUE constraint	Violates uniqueness	Insertion is skipped	Reliable because the value identifies exactly one row
Column has no UNIQUE constraint	Duplicate is allowed	A new row is added, assuming no other constraint is violated	Not reliable for identifying one intended row

The presence of a UNIQUE constraint determines whether a repeated value is a constraint violation.

This distinction is essential for the INSERT OR IGNORE followed by SELECT pattern. The SELECT can reliably retrieve the intended record only when the value used to find it is UNIQUE. Without that constraint, several rows may contain the same value, so the value does not identify one definite row.

Ensuring a Username Exists

Worked example

Reuse or create a username

An application needs the primary key for the username riverstone. The username may already exist.

1
Attempt the insertion
Use INSERT OR IGNORE for riverstone. If the username is new, a row is inserted. If that UNIQUE username already exists, the attempted duplicate is skipped instead of raising an error.
2
Retrieve the primary key
Run a SELECT for riverstone immediately afterward. Because username is UNIQUE, the value maps to exactly one row, whether that row was newly inserted or was already present.
3
Use the result
The application can use the retrieved primary key when it needs to reference the user elsewhere.

Result

The application obtains the primary key for one existing username without risking a duplicate-insertion error.

Code walkthrough

INSERT OR IGNORE INTO users (username)
VALUES ('riverstone');

SELECT user_id
FROM users
WHERE username = 'riverstone';

1
The first statement attempts the insertion. A new row is added if riverstone does not violate a constraint; otherwise the insertion is skipped.
2
The second statement retrieves user_id for riverstone. The UNIQUE constraint makes this lookup identify exactly one row.

The INSERT OR IGNORE attempts to create the username.
The SELECT finds the row by its UNIQUE username.

Output

The SELECT returns the user_id for riverstone.

The Two-Step Lookup Sequence

How do INSERT OR IGNORE and SELECT work together when the record may already exist?

Think of the sequence as ensure, then identify. INSERT OR IGNORE ensures that the desired record is present or leaves the existing record in place. SELECT then identifies that record and returns its primary key. The two statements are separate database operations, but together they form one logical application task.

Foreign Keys and Missing Parents

How does a foreign key connect a child record to a parent record, and what happens when the parent does not exist?

A FOREIGN KEY requires a value to reference an existing row in another table. This creates a relationship between a child record and a parent record. If an attempted child insertion refers to a parent that does not exist, the insertion violates the foreign-key constraint. INSERT OR IGNORE can handle that violation by skipping the insertion rather than raising an error.

Common Mistakes

Common mistake
Assuming every repeated value will be ignored
What it looks like
Inserting another row with eye_color set to Blue when eye_color has no UNIQUE constraint.
Why it fails
Without a uniqueness constraint, the repeated value is allowed. INSERT OR IGNORE has no uniqueness violation to ignore, so the new row is added unless another constraint is violated.
Do this instead
Check which column or columns have a UNIQUE constraint before relying on duplicate skipping.
Common mistake
Expecting INSERT OR IGNORE to return the primary key
What it looks like
Attempting the insertion and then using a primary key that was never retrieved.
Why it fails
The pattern handles the insertion outcome, but the primary key still must be obtained with a SELECT.
Do this instead
Follow the insertion attempt with a SELECT using the unique value.
Common mistake
Using SELECT on a non-unique value as if it identified one row
What it looks like
Searching for a value in a column that permits duplicates and assuming the result represents one intended record.
Why it fails
Several rows may contain the same value when no UNIQUE constraint exists.
Do this instead
Use a value protected by a UNIQUE constraint for the ensure-and-retrieve pattern.
Common mistake
Using the pattern when the application must know whether insertion occurred
What it looks like
Writing application logic that behaves differently for a newly created record and an already existing record.
Why it fails
INSERT OR IGNORE is designed to let both outcomes continue without an insertion error; it is not the appropriate pattern when distinguishing those outcomes is required.
Do this instead
Use an existence check or a more complex conditional approach when the application must distinguish a new insertion from an ignored duplicate.

Choosing the Pattern

Application need	Suitable approach	Reason
Ensure a unique username exists and obtain its ID	INSERT OR IGNORE followed by SELECT	The UNIQUE username identifies one row whether it was inserted or already existed.
Reuse or create a tag	INSERT OR IGNORE followed by SELECT	The pattern supports existing reference records and new records.
Insert a repeated value into a column without UNIQUE	Do not rely on INSERT OR IGNORE for duplicate prevention	The database permits the duplicate, so the insertion succeeds unless another constraint is violated.
Know whether the insertion was new or ignored	Use an existence check or a more complex conditional statement	The basic pattern is not intended to distinguish those outcomes.

Choose the pattern according to the constraint and the information the application needs.

The pattern is especially useful for user registration, tag or category management, and reference-data linking. In each case, the application wants one logical record to be available for reuse. A UNIQUE constraint prevents competing duplicate records, INSERT OR IGNORE avoids an avoidable error, and SELECT supplies the primary key needed by later operations.

Practice the Decision

MEDIUM

A tags table stores tag_name. In scenario A, tag_name has a UNIQUE constraint and the value database already exists. In scenario B, tag_name has no UNIQUE constraint and the value database already exists. For each scenario, predict whether INSERT OR IGNORE adds a row or skips the insertion, and decide whether a follow-up SELECT by tag_name can reliably identify one row.

Hints

Ask first whether the repeated value violates a UNIQUE constraint.
Then ask whether the search value maps to exactly one row.

Your attempt (not saved)

Worked example

Practice result

Compare the two tag scenarios.

1
Scenario A
The existing value violates the UNIQUE constraint, so INSERT OR IGNORE skips the insertion. A SELECT by tag_name reliably identifies the one row.
2
Scenario B
The existing value does not violate uniqueness because no UNIQUE constraint exists. INSERT OR IGNORE adds another row, assuming no other constraint is violated. A SELECT by tag_name cannot reliably identify one intended row.

Result

Duplicate handling and lookup reliability both depend on the uniqueness constraint.

Key Takeaways

1
INSERT OR IGNORE inserts a row when no constraint is violated and silently skips the insertion when a constraint would be violated.
2
Pairing INSERT OR IGNORE with SELECT lets an application ensure that a record exists and retrieve its primary key.
3
A UNIQUE constraint is what turns a repeated value into a violation and makes the follow-up lookup reliable.
4
Without UNIQUE, duplicate values are allowed, so INSERT OR IGNORE does not prevent repeated rows.
5
Use another approach when the application must distinguish a newly inserted record from an already existing record.

At a glance

INSERT OR IGNORE handles constraint violations by skipping the attempted insertion instead of raising an error.
A follow-up SELECT retrieves the primary key for the record whether it was newly inserted or already existed.
The pattern depends on a UNIQUE constraint when a value must identify exactly one row.
Foreign-key violations can also be handled by INSERT OR IGNORE, but the reliable ensure-and-retrieve lookup requires a unique identifying value.
The pattern is useful for user registration, tag management, and reference-data linking.

Related concepts

More concepts starting with H

A Safe Way to Reuse Records

What the Insertion Decides

Why Uniqueness Matters

Ensuring a Username Exists

The Two-Step Lookup Sequence

Foreign Keys and Missing Parents

Common Mistakes

Choosing the Pattern

Practice the Decision

Key Takeaways

At a glance

Related Topics

Related concepts