asked
Composite index
Hi all 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? Thx
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: if object_id('kev3','U') is not null drop table kev3; if object_id('kev2','U') is not null drop table kev2; if object_id('kev','U') is not null drop table kev; create table kev ( id int not null, partid1 int not null, partid2 int not null, ) alter table kev add constraint PK_kev primary key (id) create unique index IX_kev_composite on kev(partid1, partid2) create table kev2 ( id int not null foreign key references kev(id), blah varchar(100) ) create table kev3 ( id int not null, part1 int not null, part2 int not null, blah varchar(100) ) alter table kev3 add constraint FK_kev3 foreign key (part1, part2) references kev (partid1, partid2) 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
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.
14 People are following this question.
