HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

No Primary Key in some sql server tables

Submitted by: @import:stackexchange-dba··
0
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!

Solution

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.

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_Archive

Context

StackExchange Database Administrators Q#226396, answer score: 5

Revisions (0)

No revisions yet.