Create a foreign key on the child table, pointing to a primary or unique key in the parent table. A portable pattern is:
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER,
CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id)
REFERENCES customers (customer_id)
);Use the exact syntax and migration method for your database engine. The examples below cover PostgreSQL 17, MySQL 8.4, SQL Server, and SQLite, including referential actions, indexes, existing data, and common failures.
What a foreign key does
A foreign key (FK) is a constraint on a referencing, or child, table. Every non-NULL child value must match a value in the referenced parent key. In the example, each orders.customer_id must identify an existing customers.customer_id. If every order must have a customer, declare the child column NOT NULL; otherwise, NULL can represent an intentionally absent relationship.
The referenced columns should be a primary key or a unique key. Composite keys must list columns in the same order and use compatible data types. Create the parent key before creating the child constraint on engines that require it.
Free tools Windows power users keep installed
One-click scans. No signup required.
#1 Best Overall
Create the constraint in a new table
Single-column relationship
CREATE TABLE customers (
customer_id INTEGER PRIMARY KEY,
name VARCHAR(200) NOT NULL
);
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
order_date DATE NOT NULL,
CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id)
REFERENCES customers (customer_id)
);Naming the constraint makes future migrations and error messages easier to understand. Without an explicit name, the server generates one.
Composite relationship
CREATE TABLE products (
tenant_id INTEGER NOT NULL,
product_id INTEGER NOT NULL,
name VARCHAR(200) NOT NULL,
CONSTRAINT pk_products PRIMARY KEY (tenant_id, product_id)
);
CREATE TABLE order_lines (
order_id INTEGER NOT NULL,
tenant_id INTEGER NOT NULL,
product_id INTEGER NOT NULL,
quantity INTEGER NOT NULL,
CONSTRAINT pk_order_lines PRIMARY KEY (order_id, product_id),
CONSTRAINT fk_lines_product
FOREIGN KEY (tenant_id, product_id)
REFERENCES products (tenant_id, product_id)
);Both sides must have matching column count, order, and compatible types. A composite parent key usually needs a primary-key or unique constraint covering the exact sequence.
Add a foreign key to an existing table
For engines that support this form, the common migration is:
ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id)
REFERENCES customers (customer_id);Before running it, find orphaned child values and repair them:
Quick wins for a faster PC:
Repair Windows errors before they cause bigger problemsFix Now →Scan for outdated or missing drivers - takes under a minuteDriver Scan →Clear out junk files and repair common Windows errorsFree Scan →SELECT o.customer_id, COUNT(*) AS orphan_count
FROM orders AS o
LEFT JOIN customers AS c ON c.customer_id = o.customer_id
WHERE o.customer_id IS NOT NULL
AND c.customer_id IS NULL
GROUP BY o.customer_id;Insert the missing parent rows, update or delete invalid child rows, or allow NULL where the relationship is optional. A validated constraint cannot be added while existing rows violate it. Run the migration in your normal transaction, locking, and deployment process; large tables may require an online or staged approach specific to your engine.
SQLite is different
SQLite does not provide a general ALTER TABLE ... ADD CONSTRAINT operation. To add an FK to an existing table, create a replacement table with the desired schema, copy validated data, drop the old table, rename the replacement, and recreate indexes, triggers, and other dependent objects. Follow SQLite’s documented table-rebuild procedure. Adding a column with a REFERENCES clause while enforcement is enabled also requires a NULL default.
Choose update and delete behavior
Add actions to the FK declaration according to the lifecycle you want:
| Action | Effect on child rows | Important condition |
|---|---|---|
NO ACTION / RESTRICT |
Rejects a parent update or delete that would leave an invalid reference. | Timing differs by engine; PostgreSQL can defer NO ACTION. |
CASCADE |
Updates or deletes matching child rows automatically. | Use only when deleting dependents is intentional. |
SET NULL |
Sets child FK columns to NULL. |
Every affected child column must be nullable. |
SET DEFAULT |
Sets child columns to their defaults. | Requires suitable defaults and is not supported in every engine. |
Example with actions
CREATE TABLE order_items (
order_item_id INTEGER PRIMARY KEY,
order_id INTEGER NOT NULL,
product_id INTEGER,
CONSTRAINT fk_item_order
FOREIGN KEY (order_id)
REFERENCES orders (order_id)
ON DELETE CASCADE
ON UPDATE NO ACTION,
CONSTRAINT fk_item_product
FOREIGN KEY (product_id)
REFERENCES products (product_id)
ON DELETE SET NULL
);Do not assume action names have identical timing or support across products. PostgreSQL supports deferrable constraints; actions other than NO ACTION cannot themselves be deferred. MySQL does not support deferred checking, and InnoDB treats NO ACTION as RESTRICT. MySQL 8.4 parses SET DEFAULT but rejects it for InnoDB.
Engine-specific requirements
PostgreSQL 17
PostgreSQL supports table-level FK declarations, all common actions, and DEFERRABLE or NOT DEFERRABLE (the default). Use DEFERRABLE INITIALLY DEFERRED when a transaction must temporarily contain an out-of-order relationship and be valid at commit. PostgreSQL does not automatically create an index on child columns.
CREATE TABLE payments (
payment_id BIGINT PRIMARY KEY,
order_id BIGINT NOT NULL,
CONSTRAINT fk_payment_order
FOREIGN KEY (order_id)
REFERENCES orders(order_id)
DEFERRABLE INITIALLY DEFERRED
);MySQL 8.4
Use InnoDB (or another engine with FK support), and provide indexes on both foreign and referenced keys. MySQL checks constraints immediately; there is no deferred mode. Verify storage-engine and version assumptions before deployment.
SQL Server
SQL Server supports inline single-column and table-level single- or multi-column FKs referencing primary or unique keys. Supported actions include NO ACTION, CASCADE, SET NULL, and SET DEFAULT. SET NULL requires nullable child columns, while SET DEFAULT requires defaults. SQL Server does not automatically create the child-side index.
SQLite
SQLite’s documentation states: “Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each database connection.” Enable and verify them outside a transaction:
PC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11Outdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchPRAGMA foreign_keys = ON;
PRAGMA foreign_keys;The second statement should return 1. Set this on every connection, including pooled connections and administrative tools. Changing the setting during an active transaction has no effect. SQLite recommends an index on child-key columns for efficient parent updates and deletes; it need not be unique.
Indexes, types, and performance
Index the child key
An FK enforces correctness, not query speed. An index beginning with the child FK columns helps joins and lets the database find dependent rows quickly during parent updates or deletes. MySQL requires the relevant indexes; PostgreSQL, SQL Server, and SQLite differ in whether they create or merely recommend one. Check the execution plan for your workload.
Match definitions exactly
- Use compatible numeric, character, and binary types; avoid silent conversion.
- For composite keys, match order and every column.
- Match collation and length rules where your engine requires them.
- Ensure the parent key is actually primary or unique according to that engine’s rules.
Plan cascading operations
Cascades can touch many rows, acquire locks, fire triggers, and generate substantial logs. Index child columns, test worst-case fan-out, and set transaction and statement timeouts appropriate to production. Prefer explicit archival workflows when a business record must never disappear automatically.
Safe migration checklist
- Identify parent and child tables and decide whether the relationship is mandatory.
- Confirm the parent columns have a valid primary or unique key.
- Compare data types, order, collation, and nullability.
- Inventory existing orphan values with a
LEFT JOIN. - Choose
NO ACTION,CASCADE,SET NULL, or a supported default deliberately. - Create or verify a child-side index.
- Apply the engine-specific DDL in a migration, with a rollback plan.
- Insert an invalid child value in a disposable test transaction and confirm rejection.
- Test parent update and delete behavior, including high-volume cases.
- For SQLite, enable and verify
PRAGMA foreign_keyson every connection.
Troubleshooting common errors
“Cannot add or update a child row”
The child value has no matching parent, or the parent row was not committed yet. Insert the parent first, correct orphan data, and check transaction ordering.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
“Referenced table/key not found”
The parent table may not exist, the key may not be primary or unique, or composite columns may differ. Create the parent key first and compare definitions column by column.
SET NULL fails
The child column is NOT NULL. Make it nullable only if an absent relationship is valid, then rerun the migration.
Deletes are unexpectedly slow
Missing child indexes force scans. Add an index beginning with the FK columns and inspect the plan; also check cascades, triggers, and lock contention.
SQLite accepts invalid references
Foreign-key enforcement is probably off for that connection. Execute PRAGMA foreign_keys = ON before the transaction and verify it returns 1.
Best Value
SQLite rejects ALTER TABLE ... ADD CONSTRAINT
That syntax is not a general SQLite capability. Use the documented table-rebuild migration and recreate dependent objects.
Or skip the browser setup
If you need screenshots of SQL documentation, migration dashboards, or generated schema pages rather than a browser automation stack, ScreenshotNeo returns a clean image or PDF from one request. It accepts consent banners before capture and removes more than 60 known consent platforms, newsletter popups, and chat widgets; bot checks, blank pages, timeouts, failed loads, and cache hits are not billed, with the result identified by response headers. Its MCP server provides take_screenshot, get_page_info, and capture_pdf tools for Claude, Cursor, and other MCP clients.
cURL: See the full option list in the ScreenshotNeo documentation.
curl -G "https://api.screenshotneo.com/v1/shot" -d access_key=YOUR_API_KEY --data-urlencode url=https://stripe.com -o shot.webpPython:
import requests
r = requests.get("https://api.screenshotneo.com/v1/shot", params={"access_key": "YOUR_API_KEY", "url": "https://stripe.com"}, timeout=90)
open("shot.webp", "wb").write(r.content)Node.js:
const q = new URLSearchParams({ access_key: 'YOUR_API_KEY', url: 'https://stripe.com' });
const res = await fetch(`https://api.screenshotneo.com/v1/shot?${q}`);The free plan includes 1,000 screenshots per month with no card; paid plans start at $5 for 3,000 shots. Create a free ScreenshotNeo account.
Recommended Free Tools
Frequently Asked Questions
Can a foreign key reference a non-unique column?
Generally no. Reference a primary key or a key covered by a qualifying UNIQUE constraint; exact rules vary by engine, especially for composite keys.
Does creating a foreign key automatically create an index?
Do not assume it. MySQL requires foreign-key indexes, while PostgreSQL and SQL Server do not automatically create the child-side index; SQLite recommends one for performance.
Can I disable a foreign key temporarily?
The mechanism is engine-specific and can create integrity gaps. Prefer loading parent data first or using a controlled migration; SQLite’s enforcement switch is per connection.
The Bottom Line
Create the FK on the child table, reference a primary or unique parent key, validate existing rows, index the child columns, and choose delete/update actions that match your data lifecycle. Then verify behavior using your database engine’s rules.
Do these 3 things before closing this tab:
1Fix the driver behind crashes, sound loss and screen glitches2Clear out junk files and repair common Windows errors3Scan for outdated or missing drivers - takes under a minuteQuick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
