Problematic delete with SQL index
I have a table:
- ASK table with
idask
- Preference
idpref
,fk_idask
,fk_idstructure
- STRUCTURE table with
idstructure
With all restrictions between id
and fk_id
and a unique index on the PREFERENCES table ( fk_idask
, fk_idstructure
).
The problem is, I have two lines in PREFERENCES.
`IDPREF` `FK_IDASK` `FK_IDSTRUCTURE`
1 1 1
2 1 2
If I want to invert (swap?) Two structures between settings
`IDPREF` `FK_IDASK` `FK_IDSTRUCTURE`
1 1 **2**
2 1 **1**
The unique index between fk_idask
and fk_idstructure
decays because with the first update result in two preferences for the same query with the same structure.
To prevent this, I am doing the deleteAndResave function, and that solves the problem for now.
But now you get the ASSIGNATION table with idassignation
and fk_idpref
.
Now if I remove the prefix associated with ASSIGNATION the constraint break.
I've already found a workaround, but it's ugly. Is there any reasonable solution to this problem?
Thanks for the answer!
ps. Sorry for my bad english :(
a source to share
You can try this:
UPDATE
PREFERENCES
SET
FK_IDSTRUCTURE = 3 - FK_IDSTRUCTURE
Now, because it works in one go, the "C" (consistency) in "ACID" means that foreign keys and uniqueness are processed "in time" but will be available before and after.
You can sutter the expression with CASE statements for more complex things
UPDATE
PREFERENCES
SET
FK_IDSTRUCTURE = CASE FK_IDSTRUCTURE
WHEN 2 THEN 1 WHEN 1 THEN 2 ELSE FK_IDSTRUCTURE END
a source to share
Yah, flipped two structures between the table index and crashed!
Anyway, I am assuming that your foreign key points to the wrong path. It looks like the foreign key is in the destination and indicates preference.
Foreign key must be included in settings and assign REFERENCES.
Another option is to consider the ON DELETE CASCADE option for a foreign key. This means that when you delete rows in the specified table, the database engine automatically deletes the corresponding rows in the foreign key table.
Another commonly used option is to keep strings, but mark them as inactive. This can be done by adding an "active bit" column. When prompted, you filter out rows that are marked as inactive.
a source to share