Query on creating referential constraints..

Question

Query on creating referential constraints..

RockingAdmin

SSCrazy

Points: 2828
More actions
December 30, 2009 at 11:28 pm

#217928

hi,
i have a query on designing tables with referential constraints. i have created two tables as below..
ex:
table1:
userid int pk
username varchar
.
.
.
table2:
userperid int pk
userid int
accessname varchar
accessloc varchar
.
.
.
Now creating a foreign key, first i created a index::::
create index Ix_table2 on table2(userid);
alter table table2 add constraint Fk_userid foreign key(userid) references table1(userid);
is it good to create an index before creating a foreign key for reducing the performance issues.
Thanks
Rock....
December 31, 2009 at 2:11 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

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

vstitte SSCarpal Tunnel Points: 4475 More actions · Answer 1

Adding indexes on columns in general depends on a lot of factors.

Adding an index on a foreign key column would make sense if both the parent and child tables have a lot of records; example would be like Order (parent)and Order_Line_Items(child). Whereas if the parent table has only a few rows and child table has many rows, then you can get away with not having one. This is only a suggestion and generally works well.

It is more important to apply indexes where appropriate. It is not necessary to have one on foreign keys unless it makes sense.

ChiragNS One Orange Chip Points: 26137 More actions · Answer 2

is it good to create an index before creating a foreign key for reducing the performance issues.

Indexes are created for performance improvement. You can create an index before or after creating foreign keys.

"Keep Trying"

RockingAdmin SSCrazy Points: 2828 More actions · Answer 3

here is the scenario, i have many child tables with one common field called userid

And one parent table i.e user table.

here i will create a foreign keys in child tables that refers to parent table.

is it good to create a index on all the tables i.e index on userid column in child tables.

after creating an index's in all child tables. then creating a foreign keys.

Is it a good practice of creating many USERID indexes like that?

please guide me if i am wrong

Thanks

Rock..:-)

ChiragNS One Orange Chip Points: 26137 More actions · Answer 4

You can create index on UserID column for all child tables. Just make sure that the child tables do not contain very few data. In such a case the index may not be used.

"Keep Trying"