Inserting Data with INSERT INTO

The name id is a convention, not a description of the row's other data. It tells you that the column is being used as the table's primary identifier. The integer value in that column identifies a particular row within that table.

Programming

Why Inserted Rows Need Identity

When information is distributed across multiple database tables, inserting a row is not only about storing its descriptive data. The row also needs an identity, and it may need a connection to a row stored in another table. Primary keys provide identity within a table. Foreign keys provide connections between tables.

In the real world

The source describes an Artist table containing artist names and eye color, alongside a Track table containing song titles and play counts. A track needs a way to indicate which artist it belongs to. Keys supply that connection: one table identifies an artist, and the other stores the identifying value that refers to that artist.

A Primary Key Gives Each Row an Identity

A primary key is a unique integer identifier for each row within a table. The conventional column name for this identifier is id. Its job is to distinguish one row from every other row in the same table. Two rows in the same table therefore cannot be identified by the same primary-key value if that value is to remain unique.

How does a primary key distinguish one row from every other row within the same table?

Key point

A Foreign Key Carries a Reference

A foreign key is a column in one table that stores a primary-key value from another table. That stored integer creates a link between the tables. To follow the link, take the foreign-key value in the first table and look for the same value in the primary-key column of the referenced table.

How does a foreign-key value identify the exact row represented by a primary key in another table?

Worked example

Following an Artist Reference

A Track row stores artist_id = 12. An Artist table contains a row whose primary-key column id has the value 12. Which artist row does the track reference?

1
Read the foreign key
Start with the integer stored in the Track row's artist_id column: 12.
2
Search the referenced key
Look in the Artist table's primary-key column, conventionally named id, for the matching integer 12.
3
Follow the match
The Artist row with id = 12 is the row connected to the Track row.

Result

The foreign key does not contain the artist's descriptive data. It stores the integer that lets the database look up the related Artist row.

Naming Makes Links Readable

Naming conventions make table relationships explicit and self-documenting. A primary-key column is conventionally named id. A foreign-key column conventionally uses the referenced table's name followed by _id, such as table_name_id. In the source's Artist and Track example, a foreign-key name such as artist_id communicates that the stored value refers to an Artist row.

How are rows in separate tables connected when one table stores the other table's primary key as a foreign key?

Column role	Conventional name	What it does
Primary key	id	Uniquely identifies a row within its table
Foreign key	table_name_id	Stores a primary-key value from another table

The source's naming conventions for identifying rows and expressing table relationships.

Reading an Inserted Relationship

When a new row is inserted into a table that participates in a relationship, inspect two things separately. First, identify the row using its primary-key value. Second, if the row contains a foreign-key column, follow that value to the matching primary key in the other table. This separation prevents a common confusion: the foreign key identifies a related row, while the primary key identifies the row currently being examined.

Worked example

From a Track Row to an Artist Row

Consider a Track row with a unique id value and an artist_id value of 7. In the Artist table, one row has id = 7. Trace the relationship.

1
Identify the Track row
The Track row's own id value distinguishes it from other rows in the Track table.
2
Read artist_id
The Track row stores 7 in its artist_id foreign-key column.
3
Match the integer
Look for id = 7 in the Artist table.
4
Use the related row
The Artist row with id = 7 is the artist associated with that Track row. Its stored artist information can then be accessed through the relationship.

Result

One row's primary key identifies the Track row, while the Track row's foreign key points to the Artist row whose primary key has the matching value.

Best practice

When reviewing inserted data, name the two roles explicitly: the row's own id and the referenced table's id stored in the foreign-key column. This makes it easier to tell which row is being identified and which related row is being referenced.

Common Relationship Mistakes

Common mistake
Treating a foreign key as the identity of the current row
What it looks like
Assuming artist_id identifies the Track row itself
Why it fails
A foreign key stores a primary-key value from another table. The current Track row has its own primary key.
Do this instead
Use the Track row's id to identify the Track row, then use artist_id to find the related Artist row.
Common mistake
Looking for a foreign-key value in the wrong column
What it looks like
Trying to match a Track row's artist_id value against an Artist descriptive column instead of Artist.id
Why it fails
The foreign key works by matching an integer to the primary key in the referenced table.
Do this instead
Follow the integer to the referenced table's primary-key column, conventionally id.
Common mistake
Ignoring the naming convention
What it looks like
Reading artist_id as an unrelated integer rather than as a value associated with the Artist table
Why it fails
The table_name_id convention is intended to make relationships explicit and self-documenting.
Do this instead
Use the column name as a clue, then verify the value against the referenced table's primary key.
Common mistake
Assuming a descriptive value is enough to link rows
What it looks like
Using an artist name as the relationship mechanism instead of storing the artist's primary-key value
Why it fails
The described relational design uses integer primary keys and foreign keys to create references between rows.
Do this instead
Trace the integer stored in the foreign-key column to the matching primary-key value.

Practice the Trace

EASY

A Track row has its own id value of 31 and stores artist_id = 19. The Artist table contains rows with id values 7, 12, and 19. Which value identifies the Track row, and which Artist row does the foreign key reference?

Hints

Separate the row's own primary key from its foreign-key column.
Match artist_id to the Artist table's id column.

Your attempt (not saved)

What do you think happens?

Before checking the explanation, predict the two answers: what identifies the Track row, and which Artist id does it reference?

Reveal answer

Answer:

The Track row is identified by id = 31, and artist_id = 19 points to the Artist row with id = 19.

The row's primary key identifies the row in its own table. The foreign-key value is matched against the referenced table's primary key.

Key Takeaways

1
A primary key is a unique integer identifier for a row within a table.
2
The conventional primary-key column name is id.
3
A foreign key stores a primary-key value from another table.
4
To trace a relationship, match the foreign-key integer to the referenced table's id value.
5
Names such as id and table_name_id make database relationships explicit and self-documenting.

At a glance

Primary keys uniquely identify rows within their own table.
id is the conventional name for a primary-key column.
Foreign keys connect tables by storing primary-key values from another table.
A foreign-key relationship is traced by matching its integer to the referenced table's primary key.
Clear names such as artist_id make relationships easier to understand and maintain.

Related concepts

More concepts starting with I