No Primary Key in some sql server tables

Question

I have downloaded WorldWideImporters Database as a standard db (Microsoft Sample Database for SQL Server). In this db, some of the tables don't have primary keys but clustered indexes can be seen. Can anybody tell me why some tables are working without PK? What is the reason?

Any comments would be appreciated!

Answer 1

You can run the following query to see the tables with no primary key.

SELECT [schema] = SCHEMA_NAME(schema_id),
       name,
       temporal_type_desc
FROM   sys.tables
WHERE  object_id NOT IN (SELECT parent_object_id
                         FROM   sys.key_constraints
                         WHERE  type = 'PK');

This returns the following

+-------------+------------------------------+--------------------+
|   schema    |             name             | temporal_type_desc |
+-------------+------------------------------+--------------------+
| Warehouse   | ColdRoomTemperatures_Archive | HISTORY_TABLE      |
| Warehouse   | Colors_Archive               | HISTORY_TABLE      |
| Warehouse   | PackageTypes_Archive         | HISTORY_TABLE      |
| Warehouse   | StockGroups_Archive          | HISTORY_TABLE      |
| Warehouse   | StockItems_Archive           | HISTORY_TABLE      |
| Application | Cities_Archive               | HISTORY_TABLE      |
| Application | Countries_Archive            | HISTORY_TABLE      |
| Application | DeliveryMethods_Archive      | HISTORY_TABLE      |
| Application | PaymentMethods_Archive       | HISTORY_TABLE      |
| Application | People_Archive               | HISTORY_TABLE      |
| Application | StateProvinces_Archive       | HISTORY_TABLE      |
| Application | TransactionTypes_Archive     | HISTORY_TABLE      |
| Purchasing  | SupplierCategories_Archive   | HISTORY_TABLE      |
| Purchasing  | Suppliers_Archive            | HISTORY_TABLE      |
| Sales       | BuyingGroups_Archive         | HISTORY_TABLE      |
| Sales       | CustomerCategories_Archive   | HISTORY_TABLE      |
| Sales       | Customers_Archive            | HISTORY_TABLE      |
+-------------+------------------------------+--------------------+

You will see that 100% of these are the history table component of a temporal table pair.

So the explanation is very simple as per the docs.

History table cannot have constraints (primary key, foreign key, table or column constraints).

Answer 2

To see which tables have no primary key, run the following script:

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 
EXCEPT 
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
WHERE  CONSTRAINT_TYPE = 'PRIMARY KEY'

You will get the following list:

TABLE_NAME
----------
BuyingGroups_Archive
Cities_Archive
ColdRoomTemperatures_Archive
Colors_Archive
Countries_Archive
CustomerCategories_Archive
Customers_Archive
DeliveryMethods_Archive
PackageTypes_Archive
PaymentMethods_Archive
People_Archive
StateProvinces_Archive
StockGroups_Archive
StockItems_Archive
SupplierCategories_Archive
Suppliers_Archive
TransactionTypes_Archive

Now it's obvious that the Archive tables have no primary keys. I guess it was a design choice, you can argue that a table without a primary key is not a relation and of course has no guarantee of being able to identify every row. But, I can see how someone would think that since the only source of data comes from the non archive table that have PKs, so there is less risk. IMHO that is wrong, but not uncommon.

Since the project is on GitHub, you can contribute PKs to the schema.

HTH

UPDATE: Martin correctly commented that these are in fact the temporal history tables, and therefore can't have PKs or other constraints as they are system managed.

Answer 3

The Microsoft databases are used widely within the documentation. Consequently some of the design is there to provide good examples and showcase the product rather than teach best practice in database design. I'd guess (though I can't prove) that this choice was made for some pedagogical or marketing reason.