question in regard to updating using mssql
-
i have a database model that has an isbn as the primary key The isbn is input by the user it is the primary key for book and book is linked to book_by_authors on the isbn number and it is also linked to bookcategory by the isbn and so forth. How in the the world could I update the isbn easily? I keep getting constraint issues with the foreign keys. Should I take and create a method that stores the existing values and then deletes the record then turns around and adds the record back with the corrected data? just wondering what should be done in this case. thanks
-
i have a database model that has an isbn as the primary key The isbn is input by the user it is the primary key for book and book is linked to book_by_authors on the isbn number and it is also linked to bookcategory by the isbn and so forth. How in the the world could I update the isbn easily? I keep getting constraint issues with the foreign keys. Should I take and create a method that stores the existing values and then deletes the record then turns around and adds the record back with the corrected data? just wondering what should be done in this case. thanks
Perhaps you'd be better using a surrogate key rather than the ISBN if you want to allow users to edit them (you'd still have to check for collisions if they can manually alter an ISBN).
Tychotics: take us back to the moon "Life, for ever dying to be born afresh, for ever young and eager, will presently stand upon this earth as upon a footstool, and stretch out its realm amidst the stars." H. G. Wells
-
i have a database model that has an isbn as the primary key The isbn is input by the user it is the primary key for book and book is linked to book_by_authors on the isbn number and it is also linked to bookcategory by the isbn and so forth. How in the the world could I update the isbn easily? I keep getting constraint issues with the foreign keys. Should I take and create a method that stores the existing values and then deletes the record then turns around and adds the record back with the corrected data? just wondering what should be done in this case. thanks
Well you're screwed, a primary key that is dependent on your user input BBrrrttt WRONG. This is one of the most basic errors in database design. Like using a phone number or email address or ANY other user data as a PK/FK. Keys should be stupid, they should have absolutely NO other function than to maintain your data structure. Solution - create another field,BookID int with identity on your book table, everywhere you use the ISBN go there and add the new field and populate it based on existing structures (isbn). Now change you FKs to the BookID fields, now remove the FKs for the isbn. NOW you can edit your ISBN. It is a lot of work but you have to do it to correct the design error. Never put off fixing these errors, they get into the corners of your database and breed and shortly you are totally f***ed.
Never underestimate the power of human stupidity RAH