Is it acceptable to have a surrogate/primary key of one table be the primary/foreign key of another table?

Question

For my purposes I'm trying to apply this to Microsoft SQL, but would like to know if there are restrictions for other DBMS regarding this.

I don't know how to represent UML as ascii characters so if someone has a tool suggestion to do so please add in comments.

Example of the scenario below. Note that I'm using composite keys.

building
------------------------------------
+ buildingFullName:String(30) <<PK>>
+ buildingId:Int <<SK>> <<PK>>

buildingRoom
------------------------------------
+ buildingId:Int <<PK>>  <<FK>>
+ roomNumber:String(10) <<PK>>
+ roomId:Int <<PK>> <<SK>>

buildingRoomAsset
-------------------------------------
+ roomId:Int <<PK>> <<FK>>
+ roomAsset:String(9) <<PK>> <<SK>>

Answer 1

If you are using composite primary keys, your foreign key references would also need to be composite keys. If the primary key of building is (buildingId, buildingFullName) then buildingRoom would need to have both a buildingId and a buildingFullName that would be the foreign key to building. Similarly, if (buildingId, buildingRoom, roomId) is the primary key of buildingRoom, you would need all three of those columns in buildingRoomAsset in order to create the foreign key. Defining the foreign keys this way doesn't make sense.

If you are adding a surrogate key to the table, you would almost always want to define that column and that column alone as the primary key of the table. You can add additional unique constraints to ensure that things like the buildingFullName are unique within the table. Adding the buildingFullName to the primary key not only forces the buildingFullName to be pulled through to all the child tables, making it much more difficult to update the data, it means that you can have many buildings with the same name which seems unlikely to be what you're after.

Additionally, I would strongly suspect that you want an asset table that stores information about assets and then a roomAsset table that maps a roomId to one or more assetId values. That will make life much easier when an asset gets moved from one room to another among other use cases.

If it were me

building
------------------
+ buildingId <<SK>> <<PK>>
+ buildingFullName 

room
------------------
+ roomId <<SK>> <<PK>>
+ buildingId <<FK>>
+ roomNumber

asset
------------------
+ assetId <<SK>> <<PK>>
+ assetName

roomAsset
------------------
+ roomAssetId <<SK>> <<PK>>
+ roomId <<FK>>
+ assetId <<FK>>