First of all, I'm new to the forums, and I'm still getting started with VBEE and SQL.
I'm building a simple Windows Application that has a SQL Database with three tables, related to each other. In all of them I need to set one column as a simple index for the rows (1, 2, 3... etc), to work as a PK and relate to the other tables' FKs.
I've set the property Identity Specification of the column to Yes, and added some rows of data for testing. If you just keep adding rows, the PK columns work fine, but if you start deleting rows, the numbering gets fragmented, and the relations between the tables lose integrity.
I've tried the MSDN Online Help topics, but they just tell you this:
If an identity column exists for a table with frequent deletions, gaps can occur between identity values. If you want to avoid such gaps, do not use the identity property.
So, my questions are: is there another way around this problem? How to number rows automatically and re-number them when a row is deleted, and keep the table relations working? It has anything to do with constraints?
Sorry if my question was already answered, I really couldn't find anything like my problem searching the forums. Thanks in advance.
(and also, sorry for the bad english... )
Hi there and welcome.
It has to do with foreign key constraints, indeed. You either have to buil your own logic to update / delete the related data in the other tables OR you use the cascading option with the Foreign key constraint which deletes the rows associated through the FK to the row which is deleted. The gaps are by design, you can reseed them, but that is not a thing which should always be done after deleting a single row. So you either use the identity property or implement a logic on your own to get a new PK value for the table.
HTH, jens Suessmeyer.
http://www.sqlserver2005.de
|||Thanks for the tips, Jens. I'll try that and let you know if it worked.
No comments:
Post a Comment