SQL Server 2005 Foreign Keys and Indices
I have a general question regarding table indices on foreign keys in database modeling. If I have a table, TABLE_A, created as:
CREATE TABLE [dbo].[TABLE_A](
[ID] [int] IDENTITY(1,1) NOT NULL,
[RelatedTableBID] [int] NOT NULL,
CONSTRAINT [PK__TABLE_A] PRIMARY KEY CLUSTERED
(
[ID] ASC
) WITH (PAD_INDEX 开发者_如何学C = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [dbo].[TABLE_A] WITH CHECK
ADD CONSTRAINT [TABLE_A__RelatedTableB]
FOREIGN KEY([RelatedTableBID])
REFERENCES [dbo].[TABLE_B] ([ID])
and Table_B as:
CREATE TABLE [dbo].[TABLE_B](
[ID] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK__TABLE_B] PRIMARY KEY CLUSTERED
(
[ID] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
) ON [PRIMARY]
Will it in practice be more efficient if I create an index on the referencing table (Table A) referencing column (RelatedTableBID)? As in:
CREATE INDEX TABLE_A_FK_INDEX1 on TABLE_A(RelatedTableBID)
Or am I thinking about this backwards? It seems that since the column being referenced is itself a clustered index, there shouldn't be any issues during joins - if anything, only deletes from TABLE_B seem susceptible to poor performance.
Thanks for any help in setting me straight.
-Mustafa
EDIT
So, in general, if I'm ever joining on or using a column in a where clause consistently when querying, should I consider adding an index on it? What are some best practices and "rules of thumb" for creating database indexes? Sounds like that's a generally sound decision.You've got it right. That index on the foreign key should help whenever you need to join TABLE_A and TABLE_B.
No. What you're looking for is a third table. If it's a many-to-many relationship you're looking to build, it's not possible to do without a disgustingly ineffecient DB design without a third table.
Thus: Table 'users': uid, first, middle, last Table 'addresses': aid, street, city, state, country, etc. Table 'users-addresses': id, uid, aid
Then you can make many to many associations in the third table. This is typically called database normalization.
So then your query associative query would look something like:
SELECT * FROM users,addresses JOIN users-addresses ON users.uid=users-addresses.uid AND addresses.aid = users-addresses.aid WHERE users.uid='1' or something like that
..would give you all user information and addresses for user '1'
精彩评论