Primary keys and foreign keys

This lesson uses DDI_SQL_Training dataset v1.0.0 on SQL Server 2019 or later. Use a primary key and a referencing foreign key in disposable tables. Run changing examples only on a disposable training instance. The example uses a session-local object or rolls back its changes.

Run the example

BEGIN TRANSACTION;
CREATE TABLE dbo.DDI_LessonParents (ParentID int NOT NULL PRIMARY KEY, Name nvarchar(30) NOT NULL);
CREATE TABLE dbo.DDI_LessonChildren (ChildID int NOT NULL PRIMARY KEY, ParentID int NOT NULL
    REFERENCES dbo.DDI_LessonParents(ParentID));
INSERT INTO dbo.DDI_LessonParents VALUES (1, N'Parent');
INSERT INTO dbo.DDI_LessonChildren VALUES (10, 1);
SELECT c.ChildID, p.ParentID, p.Name
FROM dbo.DDI_LessonChildren AS c JOIN dbo.DDI_LessonParents AS p ON p.ParentID = c.ParentID;
ROLLBACK TRANSACTION;

Read it step by step

1. ParentID uniquely identifies a parent row.
2. Children.ParentID references a valid parent and can repeat across different children.
3. The join shows the linked rows; rollback removes both practice tables and rows.

Check the result

These rolled-back tables demonstrate the same relationship pattern as Orders.CustomerID to Customers.CustomerID.

Practice

Create rolled-back parent and child tables, link ChildID 20 to ParentID 2, and display the pair.

BEGIN TRANSACTION;
CREATE TABLE dbo.DDI_LessonPracticeParents (ParentID int NOT NULL PRIMARY KEY);
CREATE TABLE dbo.DDI_LessonPracticeChildren (ChildID int NOT NULL PRIMARY KEY, ParentID int NOT NULL REFERENCES dbo.DDI_LessonPracticeParents(ParentID));
INSERT INTO dbo.DDI_LessonPracticeParents VALUES (2); INSERT INTO dbo.DDI_LessonPracticeChildren VALUES (20, 2);
SELECT ChildID, ParentID FROM dbo.DDI_LessonPracticeChildren;
ROLLBACK TRANSACTION;