I'm focusing on SQL Server here, but I think this is a database agnostic question, really.
I have a table whereby a Guid is logically the primary key. However, uniqueidentifiers apparently make terrible clustered indexes, so I'm planning on creating a dummy key as an identity column and making that the clustered index for the table.
When it comes to foreign keys referencing this table however, should the foreign keys reference the clustered index or the primary key? I'm thinking it should be the primary key still, right? And that's because the clustered index doesn't technically HAVE to be unique, whereas the primary key does…?
Also, just to double check, if I make another column the clustered index, that doesn't mean I need to add a non-clustered index to my primary key column, does it? In other words, does my column automatically have an index on it just by being the primary key?
Best Answer
Taken from the MSDN article on FOREIGN KEY Constraints
I think it is perfectly fine to define a
UNIQUE CLUSTERED INDEX
on anIDENTITY
column and define thePRIMARY KEY
on different columns, often with aNONCLUSTERED INDEX
but you don't have to specify an index.