Foreign Keys to tables where primary key is not clustered index

clustered-indexforeign keyprimary-key

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

A FOREIGN KEY constraint does not have to be linked only to a PRIMARY KEY constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table.

I think it is perfectly fine to define a UNIQUE CLUSTERED INDEX on an IDENTITY column and define the PRIMARY KEY on different columns, often with a NONCLUSTERED INDEX but you don't have to specify an index.