CODEWITHSUNDEEP
sqldatabaseprimary-keyguid
rrazd
rrazd

Reputation: 1739

Can you use GUID for a column if column is not a primary key?

I have two columns in a database. Both are ids. I am making one numeric auto incremented. This is what I chose as the primary key.But the other column has to contain unique auto generated ids as well. There have to be different from the ids of the primary key (ie. the numeric auto increment column). Thus I was thinking of using GUID for this column. BUT:

Can you use GUID for a column if column is not a primary key? Is it good practice?

Upvotes: 1

Views: 273

Answers (3)

Jonathan Leffler
Jonathan Leffler

Reputation: 753525

Clearly, the GUID column will be unique, but it is rather big for joining purposes. So, it can be desirable to use the numeric column within the database for foreign key joins (joining on 4 byte or 8 byte integer quantities) rather than the 16 byte GUID column (unless you store it as a string, in which case it has 32 hex digits plus 4 dashes). The indexes will be smaller for the numeric column; the referencing tables will be smaller; the join comparisons will be quicker.

So, there might well be good reasons to have both, and to have the GUID as a secondary (alternative) unique key on the table rather than as the primary key.

Upvotes: 3

Daniel Pratt
Daniel Pratt

Reputation: 12077

Yes, you can. Whether it's a good idea depends on circumstance, but in general there's no issue here. Furthermore, you should probably create a 'unique key' on that column to enforce referential integrity (doing this will also allow you to create foreign keys that relate back to this column).

Just make sure you don't create a clustered index on that column!

Upvotes: 1

STO
STO

Reputation: 10638

This is possible and seems no problem except data size and some performance overhead.

As for me it is normal practice if you need to sync data in the table with some external system. Then you use guid to maintain global record identifier for synchronization and numeric primary key for relations inside database because they are faster than guid's.

Upvotes: 2

Related Questions