Forum > Databases

DB cleaning up tables by lost foreign key

(1/3) > >>

Nicole:
I rewrote my DB and populated it again.
There is a table tbkurszeilen having ad foreign key fk_kontrakte from tbkontrakte.
This foreign key has the setting cascade. NOW. But this seems not to be valid ex post.

How can I say
Delete from tbkurszeilen where fk_kontrakt " has no target in " at tbkontrakt.id_kontrakt" ?

In other words I want to delete all data, whose foreign key does not exist any more.

dseligo:

--- Code: MySQL  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---deletefrom tbkurszeilenwhere fk_kontrakte not in (select id_kontrakt from tbkontrakt)
Test first with:

--- Code: MySQL  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---select *from tbkurszeilenwhere fk_kontrakte not in (select id_kontrakt from tbkontrakt)

rvk:

--- Quote from: Nicole on February 27, 2024, 06:10:37 pm ---In other words I want to delete all data, whose foreign key does not exist any more.

--- End quote ---
You mean you have a foreign key pointing to non existing records in the other table.
That can't happen.

Or did you mean that you don't have the foreign key constraint added yet and want to delete some because otherwise you would get an error on adding the foreign key constraint?

Nicole:
thank you seleigo, I will try that.

rvk:
This is a most strange thing and I cannot imagine, how it happened.
I migrated my corrupted DB into a new DB.
After having copied the DDL of my tables one by one and written the data from the old DB to the new one, everything looked fine.

Yesterday I found out, that some constraints were just GONE. Even a primary key was gone in one table as pk.
No idea, how this could happen. The DLL was taken from the old tables including the fk / pk constraints.
As the primary-key value was correct as value, the queries worked never the less. So I was not aware of the data disaster.

Since two days I try to migrate the data a second time to a third newly setup database.
And one trigger was gone! 10 were ok, the 11. - gone.  :o :o :o

Is it possible, that Flamerobin and Lazarus interact and eat database-snipets?

rvk:

--- Quote from: Nicole on February 27, 2024, 07:14:18 pm ---Is it possible, that Flamerobin and Lazarus interact and eat database-snipets?

--- End quote ---
Not if you work with a server instance and connect both to that server.

You need to check if there is any corruption.
You can do that with gfix.
https://www.ibphoenix.com/resources/documents/how_to/doc_5

I've already told you that in the other topics to do this on your previous corrupted database but I'm not sure if you ever did.

As long as you don't do that, this will keep happening.

Navigation

[0] Message Index

[#] Next page

Go to full version