Index Questions

[Jonathan Wood]

I have two tables with a one-to-many relationship to each other.

In the table on the many side, I have a foreign key that contains the value
of a primary key in the table on the one side.

Questions:

1. Would you normally index the foreign key in the table on the many side?

2. Would you normally add a primary key to the table on the many side?

Thanks for any input.

 

[JIGNESH]

Ans1. Yes. There could be several reasons, one i can immediately think is
Indexes on FKs can help join performance.

Ans2. Yes. Bcos each row should be uniquely idendifited for updates/deletes.

Regards
JIGNESH.

 

[Jonathan Wood]

Thanks. Re: #2, I would never need to uniquely identify a row. Rather, I
would request all the rows matching a particular foreign key, or maybe the
first or last rows matching a particular foreign key.

 

[Cowboy \(Gregory A. Beamer\)]

Yes on both counts.

I loathe tables without proper constraints, which includes primary keys.
There are a few instances where this is not true, but they are rare. Whether
the table is clustered on the primary key or not depends on the data stored,
but it is fairly common to leave the default clustered index when using auto
number fields (IDENTITY), which is fairly common on many tables.

--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA

*************************************************
| Think outside the box!
|
*************************************************

 

[Jonathan Wood]

Thanks for the additional confirmation.

One thing: I will have links to the ASP.NET membership tables but I've
decided not to modify the original tables just in case they need to be
recreated. The result is that I have links to those tables without defining
the relationships and having them enforced by SQL. Other than that though, I
agree to the point I understood your explanation.