Iv'e been trying to google this but can't get a clear understanding.
When creating a table, it can contain a primary key, or Composite index, not both. I created Table1 with a Composite index referencing 2 columns.
I created another table Table2 with a foreign key that should link back to Table1, but getting a error as it needs to link back to a primary key.
Is it possible to setup a relationship between 2 tables when the one has a Composite index?
asked May 17, 2013 at 07:43 AM in Default
You can have a primary key and a composite index - you can only have one of them as clustered.
Also a foreign key does not need to reference a primary key, it can reference a unique constraint (unique index).
So you can have a composite foreign key ...eg:
In this example table kev has both a primary key and a unique composite constraint. kev2 has a foreign key reference to the PK, and kev3 has a composite foreign key reference to the unique constraint
answered May 17, 2013 at 02:44 PM
Kev Riley ♦♦
You can have a composite primary key. I'm not sure where you got the idea that you can't have a composite index with a primary key. Here's how it works. By default, when you create primary keys in SQL Server, if you do nothing, they're created as a clustered index. You only get one clustered index. But, you can create multiple non-clustered indexes. Each of these can consist of one or more columns to make up a composite index. Also, you can create the primary key as a non-clustered index and then put the clustered index on some other column, or columns. But, in general, it's a good idea for a clustered index to be unique, so the primary key is usually a good candidate. Also, in general, the primary key is the main query path to the data, again making it a good candidate for the clustered index.
To link a foreign key you need either a primary key or a unique constraint. You can put both onto a table and then link to different child tables in different ways. No problem.
answered May 17, 2013 at 04:00 PM
Grant Fritchey ♦♦