Allowing duplicate uniqueidentifiers in SQL Server 2008?

Question

Lets say I have only 2 tables in my DB. The two tables are: InputType and HardwareType. Each table uses GUIDs as their primary key. There is a foreign key from InputType to HardwareType.

The problem I am having is that there will be multiple rows of InputType that refer to the same row in HardwareType (ie. there are several inputs per type of hardware). The issue with this is that the column I have in InputType that refers to the GUID in HardwareType will not allow duplicates -- therefore, not allowing multiple inputs per hardware type.

I could probably get around this by setting the datatype of the column in InputType to a "varchar" or something instead of "uniqueidentifier", but do I have to do it this way? Is there someway to allow duplicate entries of GUIDs when it isn't the primary key, but instead a foreign key to another table?

Help is appreciated! Thanks.

Answer 1

Just because the datatype is UNIQUEIDENTIFIER doesn't imply you cannot have the same value in that column multiple times!

You CAN in fact - unless of course, you've explicitly added a UNIQUE CONSTRAINT or UNIQUE INDEX on that column - this is your choice, but there's nothing applied by default, unless you do something about it yourself.

So you should be able to reference HardwareType from InputType using the UNIQUEIDENTIFIER - even if multiple rows in InputType will reference the same row in HardwareType - no problems at all.