Do pre-defined joins in SQL Server slown down inserts/deletions?

I wanted to know if SQL Server 2000 does something behind the scenes in a transparent manner whenever records are inserted/deleted from two tablesbetween which a join is defined based on a primary key to foreign key relationship. So I have already defined a parent-child relationship through the 'Database Diagram' between these 2 tables. I know when a table is indexed then SQL Server will perform some actions behind the scenes in a transparent manner.
The reason I am asking this question is to know if its bad to define parent-child relatioship between 2 tables that will each contain thousands or millions of records.
Hi,
I do not know that is the exact control structure for an insert but ifyou have some constraints on your columns as well as foreign keys, therecord you are trying to insert is controlled by means of thoseconstraints.
For example, you have a foreign key on CityId column referencing Citiestable. Then the column value you are inserting for CityId must bepresent at Cities table. So this is a job to check if the cityid reallyexists in Cities table. Perhaps as simple as select * from cities wherecityid = @.cityid . But this brings an extra work during the insert.
This causes a delay. But maintains data integrity within your database.And data integrity is more important for me. Because you can improveperformance by many methods (by improving hardware, sql code, sqlserver parameters,etc) but if you lose your data integrity, it will notbe easy perhaps impossible to maintain it back.

Indexes are very useful. But if you use so many indexes on a tableespecially which is used only for insert and updates (not for so muchread), every insert will mean a new update or insert on each index usedon that table.
In short, we have the plus and minus side by side. The outcome depends how and where you use them.
Eralper
http://www.kodyaz.com

|||EDIT
I don't think DRI(declarative referential integrity) will slow down inserts and deletes because one thing it is not insert related it just guarantees updates and deletes will cascade up or down based on what you set up.I am assuming you marked the insert relationship also but that is just for data integrity by enforcing the relationship between tables. It is ANSI SQL if you say cascade on delete no action it will not affect the other table but if you say on delete cascade then it will affect the other table. If your tables are too big you should look into creating views that will be smaller. Run a search for cascade delete and cascade update in SQL Server BOL (books online). Hope this helps.sql