5

Here's my current situation:

We have a table storing data submitted from the website form, say "MyTable". The table uses bigint (auto-increment) as the primary key. Everything is good so far.

Now, that table is required to be able to store the records uploaded from the mobile devices. But the records uploaded from the mobiles are to be verified first before being stored into the "MyTable". Hence, a staging table is created, say "Temp_MyTable".

In other words, this is the flow:

Mobile devices ------> Staging Table ------> Actual Table

                    [GUID as PK]         [bigint as PK], [GUID as FK]

The thing is, the Staging Table is using GUID as the primary key. Reason, we have to reference back the records uploaded from multiple devices. Users may re-upload the record after modification. Note: The GUID here is generated by the mobile (simple javascript).

So, now, i want to reference back the records of the Actual Table to the Staging Table. In this case, a Foreign Key 'probably' required. Unfortunately, it is the GUID data type.

And after reading a number of articles, it seems that using GUID as the Foreign Key may be disadvantageous - more storage or slower.

I would like to know, is it Ok to use GUID as the Foreign Key? Can someone justify that it is actually 'perfectly fine'? Or is there a better design?

  • It is that or not have a FK. – paparazzo Jun 24 '16 at 8:20
  • Is there a FOREIGN KEY from the Actual table that references the Temporary table? How does that work when rows get deleted from the temporary? – ypercubeᵀᴹ Jun 24 '16 at 8:24
  • @Paparazzi I am not sure what you mean – zeroflaw Jun 24 '16 at 9:01
  • @ypercubeᵀᴹ Sorry i just edited my wording. And that is my question, whether i should add a Foreign Key from the Actual table that references the Temporary table. Because if it is, that FK is currently a GUID data type. – zeroflaw Jun 24 '16 at 9:02
  • Sorry but it's still unclear. 1. Do you have an FK now? 2. Do you need an FK? – ypercubeᵀᴹ Jun 24 '16 at 9:06
6

Using a UUID column as a foreign key is fine, and if that is the only candidate key on the parent record recommended/required. The FK must refer to a value that is unique in the referred table, this usually means the primary key though any column (or combination of columns) defined as unique by way of a constraint or index will do. Some suggest that it should be something that is always populated too (i.e. defined as not nullable), otherwise not all the rows in the referred table can be referred to by the FK, though there are many examples where this is not correct.

If the primary key on the referred table is a uniqueidentifier column then unless you have another unique value in that table you should use that as your foreign key target.

The only real reason that people might recommend against UUIDs as foreign keys is space: such values take 16 bytes to store instead of 4 (assuming the alternative is a 32-bit integer) or 8 (for a bigint).

There are arguments against using UUIDs as primary keys (though there are equally valid arguments for too!) especially if your PK is also your clustering key (i.e. the only member in or a significant member of the table's clustered index if it has one), but that is a different discussion.

  • Thank you for pointing out that UUID is fine to serve as an FK (and the elaboration). I was thinking whether i should create another Reference Table mapping the UUID/GUID with an auto-increment PK. But it seems quite clumsy. Directly using the UUID as the FK would be straightforward. – zeroflaw Jun 27 '16 at 7:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.