unique identifiers

[crystalized]

If you are using a unique identifier in a table as the primary key, and you want to use that primary key value as a foreign key in another table what datatype do you use in the foreign key table for the foreign key?

Crystal
crystals@genesis.sk.ca

--------------------------------------------------

Experience is one thing you can't get for nothing.

-Oscar Wilde

 

[foxdev]

I was going to say the answer is Char(36), then I was going to say the answer is Binary(16), but I think I've settled on GUID.

I've never done this (and generally don't think its advisable), so I'm a bit ignorant on it, so naturally I'm qualified to render an opinion. Keep in mind two important factors:

A single table can have more than one GUID. Only one can be the ROWGUIDCOL.

A column identified as a GUID does not automatically get a value when the record is created.


Robert Bradley

http://www.foxdev.com/

 

[crystalized]

Is there any other way that I can have 2 tables that both have an identifier (produced automatically and of the same data type) that will never contain the same value.

I had thought of using an identity seed and have the original value on one table as 1 with an increment of 1, then on the other table have the initial value of -1 and an increment of -1. Given that neither table is predicted to have more than (this is a large estimate) 20,000 entries I did not think it likely the two would conflict with each other this way, but........

I suppose I could manufacture a key and then put it into the table myself, any opinions or suggestions?

Crystal
crystals@genesis.sk.ca

--------------------------------------------------

Experience is one thing you can't get for nothing.

-Oscar Wilde

 

[culshaja]

That is the purpose of a GUID. Theoretically a value will never be genrated twice - this is for ALL servers/databases not just your one, so if I have a database with a GUID then it will not contain the same value as any GUID defined within your database.

In response to your first question and the response, yes you define the foreign key as being of type uiqueidentifier if your primary key on the referenced table is a GUID. To get a new value you use newid() in the inset statement for the GUID column.

James James Culshaw
jculshaw@active-data-solutions.co.uk

http://www.active-data-solutions.co.uk

http://www.brainbench.com/transcript.jsp?pid=193305

 

[foxdev]

The only big disadvantage of the GUID is its size - 16 bytes. Not a whole lot, in the grand scheme of things, but if you had large tables and did frequent joings on GUID, it would be slower than with an integer.

Even so, with the requirements that you describe, GUID seems to be the only "clean and simple" solution, as James suggests.

An alternative, such as having a key lookup table where you store the last used ID for both tables and retrieve it at insert time, or making the column CHAR and appending a letter onto the value, would work, but introduce their own problems, complexities, and speed degradations.

Robert Bradley

http://www.foxdev.com/