Global Database IDs

By Deane Barker on October 1, 2004

Here’s a handy feature for a database...

Tables can have primary keys, but what about a database-wide primary key? When a row is inserted, the “id” field would be populated with a numeric key that’s unique to the entire database, not just that table.

Additionally, a two-column system table would store (1) all the keys in the database, and (2) the table each key is in. So, with just a number, you could pull a record out of anywhere in the database, without even knowing exactly what it is.

Better yet, give me SQL syntax like this:

SELECT * WHERE super_bad*ss_global_id IS [number]

Or even just:

SELECT [number]

Since I don’t know what type of object it is, I don’t know the fields, so I can’t request them explicitly anyway.

The table name could be given back to me in a system field. Then my code could read that field, figure out what type of object it is, and load it up from the data in the record.

I know, I know – you could do this manually using triggers and a stored procedure or two. But what a pain. This should be supported natively.

Someone call Larry Ellison. I want royalties.

Comments (6)

dontthinkso says:

Too late you could use a GUID for a primary key in ms sql server

Deane says:

Well, yeah, I knew about that. But I want the system to maintain an index of where in the database (which table) each GUID appears.

Sebastiano Pilla says:

While syntactically convenient, I’m afraid that such a feature would not scale for high-concurrency databases, due to the bottleneck for the GUID generation. But that’s only speculation on my part.

Deane says:

Well, as a previous commentor mentioned, a lot of databases have “GUID” datatypes already. The only bottleneck would be waiting for insertion into the tracking table.

Kyle Heon says:

I think Oracle offers what I believe is called a sequence number. Once requested the number is taken and forever gone. It can be used for an insert or simply lost.

Benjamin says:

Deane,

You might check out Maya’s Information Commons. They’re using the GUID concept to the extreme and it has promise. They see their system as a possible replacement for URL’s by creating a global, permanent ID for ever piece of content in their “commons” system. It really is a cool concept. Not sure yet how it’ll play out.

Whether or not their idea pans out, I’m sure you’d enjoy reading their white papers.