Add/Delete Row

Results 1 to 2 of 2

Thread: Add/Delete Row

  1. #1
    Join Date
    Dec 1969

    Default Add/Delete Row

    This is a repost from yesterday, but I&#039;m kind of at a stand-still here.<BR>Situation: <BR>Access Database, 2 tables (actually more, but for simplicity&#039;s sake, sticking with 2). table1 has a Contact_ID field, which is an integer. It is linked to table2 which binds the ID (autonumber in this table) to a Name. I need to be able to manage this contact list, by adding and removing contacts from an ASP page. I can see that adding a contact to table2 (by name) would give me a new Autonumber, which I can then insert into table1 as Contact_ID. However, I&#039;ve noticed that when deleting a contact from table2, it&#039;ll give me an error if the ID that&#039;s tied to that name is used anywhere in table1. How do I work around this? Perhaps first check for all instances of that contact_ID in table1 and setting the value to 0 (or NULL)? <BR>Is there a faster way to go about doing this?<BR>Also, when deleting a contact by name, do I have to first look up the matching ID, delete both from table 2, then the equivalent Contact_ID from table1?

  2. #2
    Join Date
    Dec 1969

    Default Change constraints in DB...

    That primary key to foreign key link is enforced by a constraint in the DB. Dunno whether the particular DB put it there automatically (unlikely) or you specified it (perhaps inadvertently?) when you designed the DB, but just remove the constraint and you should be able to get away with the delete.<BR><BR>HAVING SAID THIS...<BR><BR>It&#039;s a bad idea. Now you will have "orphaned" records in the table with the foreign key! That is, records that *think* they are pointing to valid records in the primary table but aren&#039;t. You *really* should first remove all the dependent records from the second table and only *then* remove the primary record.<BR><BR>If you don&#039;t want to actually remove the records from the secondary table, at least change their foreign key to point to nothing (or, if the referential integrity rules don&#039;t allow that, point to a "dummy" record that you put into the primary table just for this purpose). You can do this all in one shot:<BR><BR>UPDATE secondaryTable SET foreignKey = 9999 WHERE foreignKey = keyOfPrimaryRecordToBeDeleted<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts