SQL Server
Primary Keys and Foreign Keys in SQL Server
Understand how primary keys identify rows and foreign keys connect related tables while protecting relationships in SQL Server.
On this page
Relational databases divide information into tables and connect related rows through key columns. A primary key identifies rows in one table. A foreign key in another table refers to that identifier and lets SQL Server protect the relationship. You will see these ideas in almost every real database, including the small customer and order example used in this lesson.
The synthetic JaviBuildsLab database contains dbo.Customers and dbo.Orders. Each customer has one row in Customers; each order has one row in Orders. The connection is Customers.CustomerId → Orders.CustomerId. This lesson explains what the keys mean, how they preserve valid relationships, and how they affect the JOINs you have already learned.
What a primary key does
A primary key is a constraint that identifies each row in a table. Its value must be unique across the table, and a primary-key column cannot be NULL. If a table has a CustomerId primary key, two customers cannot share the same CustomerId, and every customer row must have one.
In the demo schema, CustomerId identifies a customer and OrderId identifies an order. The values let a query, application, or another table refer to a particular record without relying on a person’s name or an address that might change. A name such as “Ana Torres” is not necessarily unique; a stable identifier can distinguish two people with the same name.
The primary key is a rule, not merely a column whose values happen to look unique today. SQL Server checks the rule when rows are inserted or updated. Trying to insert a duplicate key or a NULL key value is rejected. This protects the table even when data arrives from different applications or scripts.
SELECT CustomerId, FirstName, LastName
FROM dbo.Customers
ORDER BY CustomerId;You can inspect primary-key constraints in SSMS by expanding a table’s Keys folder in Object Explorer or opening the table designer. Names such as PK_Customers often indicate a primary-key constraint, though teams may use other naming conventions. Inspect the table definition or properties when you need to confirm the exact columns.
What a foreign key does
A foreign key is a constraint on one table that references a key in another table. In this example, Orders.CustomerId refers to Customers.CustomerId. This says that each non-NULL customer identifier recorded for an order must match a customer row, unless the relationship was explicitly designed to allow NULL.
The parent table is the referenced table: Customers. The child table contains the foreign key: Orders. The direction can feel backward at first because the order row stores the link, but the relationship is “this order belongs to that customer.” The CustomerId value is repeated in the child table so that many orders can refer to the same parent customer.
- CustomerId PK
- One customer
- Many orders
- CustomerId FK
This is a one-to-many relationship: one customer may have zero, one, or many orders; each order refers to one customer in this design. It does not mean every customer must already have an order. The foreign key guards each child reference that exists.
Referential integrity in practice
Referential integrity is the consistency rule that keeps references valid. If Orders.CustomerId has a foreign key to Customers.CustomerId, SQL Server will reject an order that refers to a customer identifier that is absent from Customers. This prevents an “orphan” order that points nowhere.
For example, CustomerId = 11 is valid if that customer exists. A value such as 99999 is rejected when no matching parent exists. The database can enforce the rule even if a change comes from a different script or application. This is more reliable than asking every program to remember to check the relationship correctly.
Foreign-key values are allowed to repeat. In fact, repetition is how multiple child rows refer to one parent. If customer 11 has four orders, all four order rows can contain CustomerId = 11. The primary key is unique in the parent table; the foreign key in the child table is generally not unique.
The constraint also affects deletes and updates. If a customer still has orders, deleting the customer may be rejected so that the child rows are not left without a valid parent. The exact behavior can be configured, including cascading actions, but those choices have consequences and are not a shortcut to use casually. For a beginner, understand the default protection first. The DELETE lesson and safe change workflow explain why you should inspect dependencies before changing rows.
A foreign key can sometimes allow NULL when the relationship is optional and the column definition permits it. NULL then means the child row currently has no parent reference; it is not a made-up parent identifier. If every order must belong to a customer, the column should also be required. The rule is expressed through both the column’s nullability and its foreign-key constraint. A primary key has the opposite fixed requirement: each row must have a present, unique identifier.
Constraints are checked when a row is changed, not only when someone writes a JOIN. That distinction matters: a query can display related values, but it does not establish or protect the relationship. The foreign key provides that protection regardless of whether an application joins the tables today. It also gives database tools a declared relationship they can show in diagrams and designers.
When you read a schema, look for the constraint definition instead of inferring relationships only from column names. Two columns can both be named CustomerId without having an enforced relationship, and a real relationship could use different names. The declared keys tell you which rule SQL Server actually checks.
See the relationship in SQL
The foreign key provides a clear join condition:
SELECT c.CustomerId, c.FirstName, c.LastName,
o.OrderId, o.OrderDate, o.Amount
FROM dbo.Customers AS c
INNER JOIN dbo.Orders AS o
ON o.CustomerId = c.CustomerId
ORDER BY c.CustomerId, o.OrderId;This query returns a row for each matching customer-order pair. A customer with several orders appears on several rows. The repeated CustomerId in Orders is expected and does not violate the uniqueness of the customer primary key. See why JOINs create repeated rows if the result shape is surprising.
The key constraint makes the relationship reliable, but the query still needs the correct ON condition. A foreign key does not automatically join tables for you or guarantee that every query uses it. Always qualify columns and inspect the schema when you are unsure which columns are related.
A brief note on composite and natural keys
Some tables need a composite key made from more than one column. For example, a hypothetical order-line table could identify a row using both OrderId and LineNumber. Each part alone may repeat, while the pair is unique. Composite keys are useful when the combination itself is the stable identity, but they require care because related foreign keys must carry each component.
A natural key comes from a real-world attribute, such as a tax identifier or an assigned code. A surrogate key is an identifier created for database use, such as an integer CustomerId. Natural values can be meaningful but may change, be unavailable, or need privacy protection. Surrogate identifiers are common because they separate row identity from descriptive business facts. The right choice depends on the domain and its rules; neither label means “always use this.”
Do not confuse an identifier with a guarantee that the row’s other facts are correct. A primary key can ensure that each customer has a distinct ID, but it cannot prove that the spelling of a name or the address is accurate. Constraints protect specific rules; they are not a substitute for understanding the data.
Primary keys, foreign keys, and indexes
A key is a data-integrity concept. An index is a structure SQL Server can use to find rows more efficiently. SQL Server commonly creates an index to support a primary key, depending on how the key is defined and the table’s existing indexes. A foreign-key constraint does not automatically mean the referencing column has an index. These concepts are related but not identical: a unique index alone and a primary-key constraint communicate different design rules.
The next lesson introduces indexes in SQL Server and explains their basic tradeoffs. For now, remember which object protects uniqueness or a relationship, and which object supports data access.
Practice in SSMS
In Object Explorer, expand JaviBuildsLab, Tables, and inspect the Keys folders for dbo.Customers and dbo.Orders. Find the primary key on CustomerId and OrderId, then locate the foreign key from Orders.CustomerId to Customers.CustomerId. In the table designer or properties, verify which columns are involved.
Run the join example and compare the number of rows with the number of orders. Then identify a customer with several orders; the same CustomerId should appear repeatedly on the order side. Do not try to delete a parent row as a test on a shared database. If you are learning with disposable data, understand the constraint behavior before attempting any modification.
- PK identifies parent
- FK references key
- SQL Server enforces link
Keys make relationships explicit, enforce useful rules, and give joins a clear foundation. The next lesson looks at indexes, which can help SQL Server find rows while adding storage and write costs: Indexes in SQL Server. You can follow the whole sequence from the SQL Server Foundations roadmap.
Check what you learned
2 quick questions
SQL Server Foundations
Lesson 23 of 25
Was this helpful?
What could be better?
Thanks — that helps me improve Javi Builds.
Continue learning
- Cheat sheetCheck SQL Server Table SizesMeasure SQL Server table row counts and storage usage, separate data from index space, and identify which tables are driving database growth.Open cheat sheet
- RoadmapSQL Server FoundationsUnderstand how primary keys identify rows and foreign keys connect related tables while protecting relationships in SQL Server.View roadmap
What should I build next?
I'm building Javi Builds around practical problems data engineers actually face. Tell me what you'd like to see next.
Stay in the loop
Get the Javi Builds updates you actually care about.
Check your inbox
I sent you a confirmation link. Your subscription will be active after you confirm it.