Inserting Data with INSERT INTO
A primary key is a unique integer identifier (conventionally named 'id') that uniquely identifies each row within a table.
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.
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.
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.
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.
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?
Read the foreign key: Start with the integer stored in the Track row's artist_id column: 12.
Search the referenced key: Look in the Artist table's primary-key column, conventionally named id, for the matching integer 12.
Follow the match: The Artist row with id = 12 is the row connected to the Track row.
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.
| 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.
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.
Identify the Track row: The Track row's own id value distinguishes it from other rows in the Track table.
Read artist_id: The Track row stores 7 in its artist_id foreign-key column.
Match the integer: Look for id = 7 in the Artist table.
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.
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.
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
Treating a foreign key as the identity of the current row
A foreign key stores a primary-key value from another table. The current Track row has its own primary key.
Fix:
Use the Track row's id to identify the Track row, then use artist_id to find the related Artist row.Looking for a foreign-key value in the wrong column
The foreign key works by matching an integer to the primary key in the referenced table.
Fix:
Follow the integer to the referenced table's primary-key column, conventionally id.Ignoring the naming convention
The table_name_id convention is intended to make relationships explicit and self-documenting.
Fix:
Use the column name as a clue, then verify the value against the referenced table's primary key.Assuming a descriptive value is enough to link rows
The described relational design uses integer primary keys and foreign keys to create references between rows.
Fix:
Trace the integer stored in the foreign-key column to the matching primary-key value.
Practice the Trace
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.
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
- A primary key is a unique integer identifier for a row within a table.
- The conventional primary-key column name is id.
- A foreign key stores a primary-key value from another table.
- To trace a relationship, match the foreign-key integer to the referenced table's id value.
- Names such as id and table_name_id make database relationships explicit and self-documenting.
Key Takeaways
- 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.