patternsqlMinor
No Primary Key in some sql server tables
Viewed 0 times
tablesprimarysqlsomeserverkey
Problem
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!
Any comments would be appreciated!
Solution
To see which tables have no primary key, run the following script:
You will get the following list:
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.
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_ArchiveNow 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.
Code Snippets
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
EXCEPT
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'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_ArchiveContext
StackExchange Database Administrators Q#226396, answer score: 5
Revisions (0)
No revisions yet.