4

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.

AdaTheDev
  • 142,592
  • 28
  • 206
  • 200
ImGreg
  • 2,946
  • 16
  • 43
  • 65
  • Well, no: the nature of a `uniqueidentifier` is to not allow duplicate entries. If its not unique then don't mark it as unique. – Femi Jun 29 '11 at 16:05
  • Just because the datatype is `UNIQUEIDENTIFIER` doesn't imply you cannot have the same value in there multiple times! You **CAN** - unless of course, you've explicitly added a `UNIQUE CONSTRAINT` or `UNIQUE INDEX` on that column – marc_s Jun 29 '11 at 16:06
  • 1
    Perfect. That is what I wanted to hear. Found a "Is Unique" flag in the "Indexes/Keys" section. Now I can enter duplicates into the uniqueidentifier field like I need to. Thanks @marc_s! – ImGreg Jun 29 '11 at 16:14

1 Answers1

9

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • 1
    You should also read @marc_s post on a similar question for even more advice regarding using guid's as keys http://stackoverflow.com/questions/2996031/guid-primary-foreign-key-dilemma-sql-server – RThomas Jun 29 '11 at 16:18
  • 1
    That was useful as well. Thanks everyone! – ImGreg Jun 29 '11 at 16:38