Primary and Foreign Keys in SQL
Learn how primary and foreign keys in SQL link vehicle and service tables, how referential integrity works, and how to write CREATE TABLE statements that keep records clean.
Contents
- What a primary key does
- What a foreign key does
- Referential integrity in plain terms
- Writing keys in CREATE TABLE
- Keys in a service and cost schema
- Differences between the two keys
- Adding keys to existing tables
- Mistakes that break the link
- Questions about SQL primary and foreign keys
- Can a table have more than one foreign key?
- Does a foreign key have to be unique?
- What happens when I delete a parent row that has children?
- Can a primary key contain more than one column?
- Why does my foreign key constraint fail to create?
Primary and foreign keys in SQL are the two constraints that tie a relational database together. A primary key gives each row a unique identity, and a foreign key points from one table to the key of another. Together they keep service notes, dates and cost records matched to the right vehicle.
- A primary key makes every row in a table uniquely identifiable and cannot hold a null value.
- A foreign key holds a value that must exist in the referenced table, or be null when the column allows it.
- Referential integrity is the rule that keeps those links valid when rows are added, changed or removed.
- Define keys in the CREATE TABLE statement so the database engine enforces them for every write.
- A foreign key column must match the data type of the primary key it references.
What a primary key does
A primary key is a column or a set of columns that gives every row its own identity. The database rejects any second row with the same key value. A primary key also rejects nulls, so a row can never sit in the table without identification.
In a service history table, the primary key is often a generated number that has no meaning outside the database. A vehicle identifier such as a plate or fleet number can work as a natural primary key when it is unique and never changes. Choose one approach and keep it consistent across the schema.
A table holds only one PRIMARY KEY constraint, but that constraint may span several columns. A composite key made of the vehicle identifier and the service date stops the same vehicle from logging two entries on one day. The key is the rule, and the index behind it makes lookups fast.
What a foreign key does
A foreign key is a column that stores a value copied from a primary key in another table. The FOREIGN KEY constraint tells the database to check that the copied value exists before accepting the row. This is how a service note carries the identity of the vehicle it belongs to.
A foreign key column does not have to be unique, because many rows may point at the same parent. Nulls are allowed only when the column is defined to accept them. A nullable foreign key suits an optional link, such as a cost line not yet assigned to a repair job.
The parent column named by the foreign key must itself be a primary key or carry a unique constraint. Data types on both sides must match, or the database may refuse the constraint at creation. Test the link with a small insert before loading a large set of records.
Referential integrity in plain terms
Referential integrity means every foreign key value points at a row that actually exists. The database enforces the rule on insert, update and delete, so orphan rows cannot build up. Orphans are child rows whose parent has vanished, and they make reports and costs unreliable.
Deleting a parent row is the moment integrity is tested. The default action blocks the delete while child rows still reference the key. Other actions either cascade the change to children or set the child values to null, and each is chosen in the constraint definition.
Updates to a parent key follow the same logic. If the key value changes, the database either rejects the change or propagates it to children under the chosen rule. Keeping primary key values stable avoids most of these problems.
Writing keys in CREATE TABLE
The CREATE TABLE syntax places the PRIMARY KEY constraint after the column list or inline with the column. A foreign key normally appears after the columns, naming the child column and the parent table with its column. Give every constraint a name so errors are easy to read later.
Referenced tables must exist before the statement that points at them runs. Create the vehicle table first, then the service history table that carries the foreign key. Scripts that drop tables must reverse that order to avoid constraint errors.
A constraint name such as fk_service_vehicle documents the relationship inside the schema itself. Some database engines also let you add or drop a constraint after the table exists. That option helps when a legacy table gains keys later.
- Define the parent table with its PRIMARY KEY before any child table.
- Name each constraint so error messages identify the rule.
- Match data types between the foreign key and the referenced primary key.
- Decide the delete action when you create the foreign key.
- Add keys to existing tables with ALTER TABLE when data is already loaded.
- Test the constraint with one insert that should fail.
Keys in a service and cost schema
A vehicle table with a primary key gives each unit one row and one identity. The service history table then holds a foreign key pointing back to that vehicle. Each service event becomes a child row with its own primary key and its date and notes.
Cost records can sit in their own table with a foreign key to the service event. A fuel or insurance cost may instead link straight to the vehicle when no repair is involved. The choice depends on what the cost describes.
This arrangement answers questions that span tables, such as the total cost attached to one vehicle. The keys do the matching, so names and plates are stored once. Changing a plate means one update in the vehicle table only.
Differences between the two keys
A primary key identifies a row inside its own table, while a foreign key creates a reference across tables. Only one primary key is allowed per table, but several foreign keys may sit in the same table. Both are constraints, and both are declared in the table definition.
Uniqueness and null handling separate the two rules sharply. A primary key is unique and never null, and a foreign key may repeat values and may accept nulls. The parent side of a foreign key needs a unique or primary constraint to be a valid target.
The two keys also differ in what they protect. A primary key stops duplicate rows, and a foreign key stops broken links between tables. Remove the foreign key and the rows still load, but the relationships become guesses.
| Rule | Primary key | Foreign key |
|---|---|---|
| Purpose | Identify each row | Reference another table |
| Uniqueness | Required | Not required |
| Nulls | Not allowed | Allowed if column permits |
| Count per table | One | Several |
| Target | The row itself | Primary or unique key elsewhere |
Adding keys to existing tables
A service history table built without constraints often holds duplicates and broken references already. Clean the data before adding a PRIMARY KEY constraint, because duplicate values block it. Remove or merge the duplicate rows and keep the entry that carries the full record.
Foreign keys face a similar test on existing rows. Every value in the column must match a parent key or be null before the constraint is accepted. Find the unmatched values first and decide whether to correct or delete them.
Adding keys to a table with live traffic needs planning. The operation scans the table and can lock it while it runs. Schedule the change for a quiet period, and confirm the data is backed up before you start.
Mistakes that break the link
A foreign key built on the wrong data type fails or compares badly, especially between integer and text identifiers. Mismatched types on the two sides of the relationship cause errors that are hard to trace. Confirm the type of the parent column before you declare the child column.
Loading child rows before parent rows triggers constraint errors in a fresh database. Order the load parent first, then children, and reverse the order for removal. Bulk load tools may need the constraint checked after the load, and that setting carries risk of orphan rows.
Deleting a parent row out of habit is a quiet source of lost history. The constraint stops it while children remain, which is the behavior you want for service records. Keep the parent rows and archive notes instead of removing vehicles that hold cost history.
Questions about SQL primary and foreign keys
Can a table have more than one foreign key?
Yes, a table can hold several foreign keys, each pointing at a different parent table or column. A service history table may reference both a vehicle and a technician table. Only the primary key is limited to one per table.
Does a foreign key have to be unique?
No, a foreign key column may repeat the same value many times across rows. Only the parent column it references must be unique or a primary key. Duplicate child values simply show that several rows point at the same parent.
What happens when I delete a parent row that has children?
The database blocks the delete when the foreign key uses the default restrict action. Cascading or null-setting actions change that outcome if chosen in the constraint. In a service record system, blocking the delete protects the history.
Can a primary key contain more than one column?
Yes, a composite primary key spans several columns and makes their combined values unique. A vehicle identifier plus a service date is one example. The database rejects any duplicate combination and any null in those columns.
Why does my foreign key constraint fail to create?
A foreign key fails when the referenced column is not unique or the data types differ. Existing child values that match no parent also block the constraint. Correct the data and confirm the parent column before you retry.