FKs and indexes

Question

Post reply

FKs and indexes

PiMané

SSChampion

Points: 13965
More actions
November 29, 2012 at 3:29 pm

#271446

Hi,
I read an article Grantt wrote about Fks and how they should always have indexes.
My question is:
If I have a table with clustered index on c1 and has a FK on c1 and c2 is it enough to create an index nonclustered on c2? I ask this cause since c1 is the clustered index it is on every leaf level of all the nc indexes and so and index with "just" c2 also has c1. or do I have to create and index on c1 and c2 even if internaly they are the same?!
thanks,
Pedro

If you need to work better, try working less...

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101200 More actions · Answer 1

When you create the nonclustered index on c2, explicitly specify c1 as a key after that.

Then define the FK on (c2, c1) and you should be covered.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

PiMané SSChampion Points: 13965 More actions · Answer 2

ScottPletcher (11/29/2012)
When you create the nonclustered index on c2, explicitly specify c1 as a key after that.
Then define the FK on (c2, c1) and you should be covered.

if the fk is c2, c1 does the index have to be c2, c1 or can it be c1, c2?

both column are always used for validation so column order shouldn't be an issue here, right?

thanks,

pedro

If you need to work better, try working less...

ScottPletcher SSC Guru Points: 101200 More actions · Answer 3

PiMané (11/29/2012)
ScottPletcher (11/29/2012)
When you create the nonclustered index on c2, explicitly specify c1 as a key after that.
Then define the FK on (c2, c1) and you should be covered.
if the fk is c2, c1 does the index have to be c2, c1 or can it be c1, c2?
both column are always used for validation so column order shouldn't be an issue here, right?
thanks,
pedro

The fk has to match the index. That's why I specified things the way I did.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

PiMané SSChampion Points: 13965 More actions · Answer 4

ScottPletcher (11/29/2012)
The fk has to match the index. That's why I specified things the way I did.

I asked it cause if you have an index c1,c2 and another index c2,c1 when filtering by both c1 and c2 any of the indexes are good for seek, so why is it different on FKs?

Thanks,

Pedro

If you need to work better, try working less...

PiMané SSChampion Points: 13965 More actions · Answer 5

ScottPletcher (11/29/2012)
PiMané (11/29/2012)
ScottPletcher (11/29/2012)
When you create the nonclustered index on c2, explicitly specify c1 as a key after that.
Then define the FK on (c2, c1) and you should be covered.
if the fk is c2, c1 does the index have to be c2, c1 or can it be c1, c2?
both column are always used for validation so column order shouldn't be an issue here, right?
thanks,
pedro
The fk has to match the index. That's why I specified things the way I did.

Just made a test and on the "parent" table the index has to be exactly the same as the FK so the FK can be created.

But on the "child" table it can be any order of the FK columns as long as they are the first columns on the index.

If the FK is c2, c1 the index can be c1, c2, cN that SQL will make an index seek on the index to verify the FK.

Thanks,

Pedro

If you need to work better, try working less...

ScottPletcher SSC Guru Points: 101200 More actions · Answer 6

PiMané (11/30/2012)
ScottPletcher (11/29/2012)
PiMané (11/29/2012)
ScottPletcher (11/29/2012)
When you create the nonclustered index on c2, explicitly specify c1 as a key after that.
Then define the FK on (c2, c1) and you should be covered.
if the fk is c2, c1 does the index have to be c2, c1 or can it be c1, c2?
both column are always used for validation so column order shouldn't be an issue here, right?
thanks,
pedro
The fk has to match the index. That's why I specified things the way I did.
Just made a test and on the "parent" table the index has to be exactly the same as the FK so the FK can be created.
But on the "child" table it can be any order of the FK columns as long as they are the first columns on the index.
If the FK is c2, c1 the index can be c1, c2, cN that SQL will make an index seek on the index to verify the FK.
Thanks,
Pedro

Yes, as I stated: the FK has to have an exactly matching index on the parent table.

A FK from a child to a parent doesn't affect any indexes on the child table. You don't have to index c1 and c2 in any way at all on the child to use them as a FK. Only the parent has to be indexed.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

PiMané SSChampion Points: 13965 More actions · Answer 7

ScottPletcher (11/30/2012)
A FK from a child to a parent doesn't affect any indexes on the child table. You don't have to index c1 and c2 in any way at all on the child to use them as a FK. Only the parent has to be indexed.

Don't have to create an index on the child but if you delete from the parent the child will be searched to validate the record usage.. so the operation will perform better if the child has an index on the FK columns also.

The parent has the index since it's "mandatory" an unique index on the columns in order to create the reference from the child to the parent table.

Pedro

If you need to work better, try working less...