Problematic delete with SQL index

Question

I have some table:

• table ASK with idask
• table PREFERENCES with idpref, fk_idask, fk_idstructure
• table STRUCTURE with idstructure

With all the constraint between the id and the fk_id and a unique index on the table PREFERENCES (fk_idask, fk_idstructure).

The problem is when I have two row in PREFERENCES.

`IDPREF`   `FK_IDASK`   `FK_IDSTRUCTURE`  
 1          1            1  
 2          1            2

If I want to invert(switch?) the two structure between the preferences

`IDPREF`   `FK_IDASK`   `FK_IDSTRUCTURE`  
 1          1            **2**
 2          1            **1**

the unique index between FK_IDASK and FK_IDSTRUCTURE break up, because with the first update result in two preferences for the same ask with the same structure.

For prevent this, I make a function deleteAndResave and this solve the problem for the moment.

But now the arrive the ASSIGNATION table with idassignation and fk_idpref.

Now if I delete a Preference linked by an ASSIGNATION the constraint break.

I already find a workaround, but is ugly. There is some sane solution for this problem?

Thanks for the answer!

ps. Sorry for my bad english :(

Answer 1

Yah, inverted two structures between table index and break up!

Anyway, I'm guessing your foreign key is pointing the wrong way. It looks like the foreign key is on assignation, and pointing to preferences.

The foreign key should be on preferences, and REFERENCE assignation.

Another option is to consider an ON DELETE CASCADE option on the foreign key. This means that when you delete rows in the referenced table, the database engine automatically deletes the relevant rows in the foreign key table.

Another often used option is to keep the rows, but mark them as inactive. This can be done by adding an "active bit" column. When querying you filter out the rows marked as inactive.

Answer 2

You could try this:

UPDATE
    PREFERENCES
SET
    FK_IDSTRUCTURE = 3 - FK_IDSTRUCTURE

Now, because it runs in one go, the "C" (Consistency) in "ACID" means that foreign keys and uniqueness are handled "during" but will be OK before and after.

You can pimp the statement with CASE statements for more complex stuff

UPDATE
    PREFERENCES
SET
    FK_IDSTRUCTURE = CASE FK_IDSTRUCTURE 
        WHEN 2 THEN 1 WHEN 1 THEN 2 ELSE FK_IDSTRUCTURE END