x

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

more ▼

asked May 17, 2013 at 07:43 AM in Default

avatar image

ruancra
1.2k 31 37 45

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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

more ▼

answered May 17, 2013 at 02:44 PM

avatar image

Kev Riley ♦♦
66.1k 48 63 81

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered May 17, 2013 at 04:00 PM

avatar image

Grant Fritchey ♦♦
137k 20 44 81

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x150
x42
x6

asked: May 17, 2013 at 07:43 AM

Seen: 1171 times

Last Updated: May 19, 2013 at 04:00 AM

Copyright 2017 Redgate Software. Privacy Policy