Problems with Identity Specification - deleting rows

first of all, i'm new forums, , i'm still getting started vbee , sql.

i'm building simple windows application has sql database 3 tables, related each other. in of them need set one column simple index rows (1, 2, 3... etc), work pk , relate other tables' fks.

i've set property identity specification of column yes, , added some rows of data for testing. if keep adding rows, pk columns work fine, if start deleting rows, numbering gets fragmented, , relations between tables lose integrity.

i've tried msdn online topics, but they just tell this:

if identity column exists table frequent deletions, gaps can occur between identity values. if want avoid such gaps, not use identity property.

so, questions are: there another way around problem? how number rows automatically , re-number them when row deleted, , keep table relations working? has constraints?

sorry if my question was already answered, couldn't find problem searching forums. thanks in advance.

(and also, sorry bad english... )

hi there , welcome.

it has foreign key constraints, indeed. either have buil own logic update / delete related data in other tables or use cascading option foreign key constraint deletes rows associated through fk row deleted. gaps design, can reseed them, not thing should done after deleting single row. either use identity property or implement logic on own new pk value table.

hth, jens suessmeyer.

---
http://www.sqlserver2005.de
---

SQL Server  >  Getting started with SQL Server