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.

What Links Here


  1. 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.

  2. 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.

  3. 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.

  4. 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.

  5. 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.

Comments are closed. If you have something you really want to say, tweet @gadgetopia.