How to Create a Foreign Key in SQL Server

I have been there: I copied a Postgres style foreign key into SQL Server, hit Run, and SQL Server clapped back with:

Msg 8139: Number of referencing columns in foreign key differs from number of referenced columns

At first, I thought, “Huh” Then I spotted it. I forgot to tell SQL Server which column in my child table is doing the referencing. In this post, I’ll walk through the exact mistake, how I fixed it, and share a clean, re runnable script with extra practice features (defaults, cascades, indexes, sample data, and useful queries). I’ll also wrap up with quick troubleshooting tips and final thoughts.

The Setup I Started With

I had three tables: exams, question_bank, and answer_bank (I originally spelled it anwser_bank oops). Inside question_bank, my foreign key line looked like this:

CONSTRAINT question_exam_id FOREIGN KEY REFERENCES exams(exam_id)

In SQL Server, that’s incomplete. I must name the referencing column(s) on the left something like:

FOREIGN KEY (question_exam_id) REFERENCES exams(exam_id)

Without the left side, SQL Server thinks there are zero referencing columns pointing to one referenced column, which triggers Msg 8139.

What I Fix

1. FK syntax: Added the referencing column list:
   FOREIGN KEY (**question_exam_id**) REFERENCES exams(exam_id)
2. Typos and commas: Renamed anwser_* to answer_* and removed trailing commas.
3. Defaults & checks: Added defaults for IDs and booleans; a check to prevent negative points.
4. More relationships: Wired answer_bank → question_bank via a second FK.
5. Quality of life: Added indexes on FK columns, sample data, and “practice” queries.

The Complete, Re-Runnable Script I Use Now

Drop in ready for SQL Server. It safely drops in dependency order, recreates everything, inserts sample data, and gives you queries to explore.

/* ---------- Safe drops (in dependency order) ---------- */
IF OBJECT_ID('dbo.answer_bank', 'U') IS NOT NULL DROP TABLE dbo.answer_bank;
IF OBJECT_ID('dbo.question_bank', 'U') IS NOT NULL DROP TABLE dbo.question_bank;
IF OBJECT_ID('dbo.exams', 'U') IS NOT NULL DROP TABLE dbo.exams;
GO

/* ---------- Base tables ---------- */
CREATE TABLE dbo.exams
(
    exam_id   UNIQUEIDENTIFIER NOT NULL 
        CONSTRAINT PK_exams PRIMARY KEY 
        CONSTRAINT DF_exams_id DEFAULT NEWID(),
    exam_name VARCHAR(50) NOT NULL
);
GO

CREATE TABLE dbo.question_bank
(
    question_id          UNIQUEIDENTIFIER NOT NULL 
        CONSTRAINT PK_question_bank PRIMARY KEY 
        CONSTRAINT DF_question_id DEFAULT NEWID(),
    question_exam_id     UNIQUEIDENTIFIER NOT NULL,
    question_text        VARCHAR(1024) NOT NULL,
    question_point_value DECIMAL(5,2) NOT NULL 
        CONSTRAINT DF_question_point DEFAULT(1.00)
        CONSTRAINT CK_question_point_nonneg CHECK (question_point_value >= 0),

    /* ✅ Correct FK syntax: name the referencing column(s) */
    CONSTRAINT FK_question_exam
        FOREIGN KEY (question_exam_id) 
        REFERENCES dbo.exams(exam_id)
        ON DELETE CASCADE      -- optional: delete questions when exam is deleted
        ON UPDATE NO ACTION
);
GO

CREATE TABLE dbo.answer_bank
(
    answer_id           UNIQUEIDENTIFIER NOT NULL 
        CONSTRAINT PK_answer_bank PRIMARY KEY 
        CONSTRAINT DF_answer_id DEFAULT NEWID(),
    answer_question_id  UNIQUEIDENTIFIER NOT NULL,
    answer_text         VARCHAR(1024) NOT NULL,
    answer_is_correct   BIT NOT NULL 
        CONSTRAINT DF_answer_correct DEFAULT(0),

    /* Extra FK to tie answers to questions */
    CONSTRAINT FK_answer_question 
        FOREIGN KEY (answer_question_id)
        REFERENCES dbo.question_bank(question_id)
        ON DELETE CASCADE
        ON UPDATE NO ACTION
);
GO

/* ---------- Helpful indexes ---------- */
/* Speeds up joins/lookups by the FK columns */
CREATE INDEX IX_question_exam ON dbo.question_bank(question_exam_id);
CREATE INDEX IX_answer_question ON dbo.answer_bank(answer_question_id);
GO

/* ---------- Sample data (practice) ---------- */
INSERT INTO dbo.exams (exam_name) VALUES ('SQL Basics'), ('Data Modeling 101');

-- Capture IDs to use in inserts
DECLARE @ExamSql UNIQUEIDENTIFIER = (SELECT exam_id FROM dbo.exams WHERE exam_name = 'SQL Basics');
DECLARE @ExamDm  UNIQUEIDENTIFIER = (SELECT exam_id FROM dbo.exams WHERE exam_name = 'Data Modeling 101');

INSERT INTO dbo.question_bank (question_exam_id, question_text, question_point_value)
VALUES 
  (@ExamSql, 'What does a primary key guarantee?', 1.00),
  (@ExamSql, 'Which SQL clause filters rows?', 1.00),
  (@ExamDm,  'What is a foreign key used for?', 2.00);

DECLARE @Q1 UNIQUEIDENTIFIER = (SELECT question_id FROM dbo.question_bank WHERE question_text LIKE 'What does a primary key%');
DECLARE @Q2 UNIQUEIDENTIFIER = (SELECT question_id FROM dbo.question_bank WHERE question_text LIKE 'Which SQL clause%');
DECLARE @Q3 UNIQUEIDENTIFIER = (SELECT question_id FROM dbo.question_bank WHERE question_text LIKE 'What is a foreign key%');

INSERT INTO dbo.answer_bank (answer_question_id, answer_text, answer_is_correct)
VALUES
  (@Q1, 'Uniqueness of each row', 1),
  (@Q1, 'Sorting results', 0),
  (@Q2, 'WHERE', 1),
  (@Q2, 'ORDER BY', 0),
  (@Q3, 'Linking to a parent table', 1),
  (@Q3, 'Speeding up queries', 0);
GO

/* ---------- Practice queries ---------- */
-- 1) List exams with number of questions and total points
SELECT e.exam_name,
       COUNT(q.question_id) AS question_count,
       SUM(q.question_point_value) AS total_points
FROM dbo.exams e
LEFT JOIN dbo.question_bank q ON q.question_exam_id = e.exam_id
GROUP BY e.exam_name
ORDER BY e.exam_name;

-- 2) Show questions with their correct answers
SELECT e.exam_name, q.question_text, a.answer_text AS correct_answer
FROM dbo.exams e
JOIN dbo.question_bank q ON q.question_exam_id = e.exam_id
JOIN dbo.answer_bank a ON a.answer_question_id = q.question_id AND a.answer_is_correct = 1
ORDER BY e.exam_name, q.question_text;

-- 3) Demonstrate ON DELETE CASCADE (uncomment to test, then query again)
-- DELETE FROM dbo.exams WHERE exam_name = 'SQL Basics';
-- SELECT * FROM dbo.question_bank;  -- questions for deleted exam are gone
-- SELECT * FROM dbo.answer_bank;    -- related answers are also gone
GO

/* ---------- Example: Add a foreign key later (ALTER) ---------- */
/* (For practice: same as earlier but shows ALTER TABLE syntax)
ALTER TABLE dbo.question_bank
ADD CONSTRAINT FK_question_exam
    FOREIGN KEY (question_exam_id)
    REFERENCES dbo.exams(exam_id);
GO
*/

Keys in SQL Server

• A foreign key links child → parent using matching column(s).
• In T-SQL, you must specify both sides:
  FOREIGN KEY (child_col_1, child_col_2, …) REFERENCES parent(parent_col_1, parent_col_2, …)
• If you omit the child column list, SQL Server can’t pair things up and throws Msg 8139.

Walking Through “Practice” Bits

I like to verify behavior quickly:

1. Counts & totals: The first query shows questions per exam and total points—great sanity check.
2. Correct answers: The second query proves my answer_is_correct logic and join path.
3. Cascade deletes: Uncomment the DELETE FROM exams line and watch related questions/answers vanish (because of ON DELETE CASCADE). Comment it back if you don’t want cascades in production.

Common Pitfalls I Have Personally Hit

• Trailing commas: A sneaky comma before ) breaks a CREATE TABLE. Keep an eye out.
• Mismatched types: FK column types must match (e.g., UNIQUEIDENTIFIER → UNIQUEIDENTIFIER).
• Naming confusion: Give constraints sensible names (FK_answer_question); it makes debugging easier.
• Forgetting indexes: FKs don’t automatically index the child column. I add IX_* on FK columns for joins.

Extra Practice Ideas I Found Useful

• Uniqueness per question (prevent duplicate answer text): CREATE UNIQUE INDEX UX_answer_per_question ON dbo.answer_bank(answer_question_id, answer_text);
• Computed columns (quick analytics): ALTER TABLE dbo.question_bank ADD question_len AS LEN(question_text);
• Change cascade rules: Remove ON DELETE CASCADE and try deleting a parent. Feel the constraint violation—then decide which behavior you truly want.
• Try an upsert with MERGE: Insert if missing, update if found—great for idempotent scripts.

Final Thought

I used to think “foreign keys are fussy.” Now I see they’re just precise. The moment I tell SQL Server which child column references which parent column explicitly and clearly the errors disappear and my data model behaves exactly how I intend. If you’re porting patterns from Postgres, double-check those T-SQL FK parentheses, keep your types aligned, and add the small quality of life touches (defaults, checks, indexes). Your future self will thank you.