Field which can lookup against multiple tables

Gary Burch 1 Reputation point
2021-02-08T17:37:32.677+00:00

I have a SQL Database with multiple tables of similar but distinct data (e.g.: Workstations, Virtual Servers, Physical Servers, Switches, ...)

All these tables have Primary Keys defined the same in the CREATE TABLE statement:

ID UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY

I need a field in another table which can refer to the Primary Key of any of these tables, and be able to use JOINs to return the values of other fields in the original table (and possibly other related tables)

I've looked at using a UNION VIEW to conflate all the ID fields, but I don't (as far as I can tell) then have a way to lookup the fields from the original table. I've also looked at creating a 'top-level' table containing the fields all these tables have in common, but couldn't find a way to populate the 'top-level' and child table at the same time.

Is there another approach I haven't considered? Or, am I best to just have the field which contains the IDs from different tables unbound and not worry about referential integrity?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,851 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,559 questions
{count} votes