[solved] "unique" violation in a table

[Nicole]

hmm...
This SQL statement does not react.
I hit "run" and nothing happens. No error-message, no result.

This is not very encouraging....

about this unique:
These are date + item.
The data go back to about 1981 or so.
The difference if there is an additional primary key - matters.

[rvk]

hmm...
This SQL statement does not react.
I hit "run" and nothing happens. No error-message, no result.

It should at least give you an empty result in FlameRobin.
(So headers FK_COMM, DATUM, COUNT without any info below it.)

It it doesn't and it hangs... then it is still searching your DB.

[rvk]

I just checked. Setting the RDB$INDEX_INACTIVE to 3 just deactivates the constraint.

So you have a database full of deactivated constraints.
You might want to check how long this is going on (when did this happen).

With the previous mentioned SQL you can check, per constraint, if you have duplicates.

You can also just try to activate the constraint again and see if it throws an error.

ALTER INDEX UNQ1_NAME_U_JAHR ACTIVE

If it does, you need to investigate that constraint (with the SQL mentioned above) to see the problem records.

You can also do this with a EXECUTE BLOCK for all but I would recommend doing it per constraint.
https://www.firebirdfaq.org/faq274/

After you did it for all, you can try a backup/recover cycle to see if it works.

[Nicole]

worked finally, sobb.
See screenshot.

I do not like the results...
Perhaps I take a break and think over, what to do and how.

[rvk]

I do not like the results...
Perhaps I take a break and think over, what to do and how.

Well yes, there are 9 records with 1 duplicate (so total of 18 records).

You need to do a select on those records/
Let me fiddle a bit... 

This:

Code: SQL  [Select][+][-]

1. SELECT A.CNT, B.* FROM
2.   (SELECT COUNT(*) AS CNT, FK_COMM, DATUM
3.       FROM TBCOT
4.       GROUP BY FK_COMM, DATUM
5.       HAVING COUNT(*) > 1) A
6. INNER JOIN RELATIE B ON B.FK_COMM=A.FK_COMM AND B.DATUM=A.DATUM
7. ORDER BY FK_COMM, DATUM

(Hope I didn't make any typos) This should give you all records that are and have duplicates.
You need to determine what you want to do with those records.
The problem is that you don't have a PRIMARY KEY which is UNIQUE so when you DELETE on FK_COMM, DATUM it will delete BOTH records.

If you have some other fields which makes the records unique you can delete the ones you want.

Code: SQL  [Select][+][-]

1. DELETE FROM TBCOT WHERE FK_COMM='BP' AND DATUM='31.01.2023' AND SPEC_LONG='something unique'

If there isn't anything unique in the other fields then there is no other option than to delete all those records and add them again (once).

After you have done that, and there is no more result with the above SQL, you can activate the constraint/indices again.
(you can also test it by backup up the database and restoring it again)

~~
Did you find out what happened to the database more than a year ago and why all those constraints/indices are deactivated??
You are lucky it's just limited to those 9 records that are duplicated (all on 31.01.2023).

[Nicole]

Thank you for the attempt.
I am afraid, the evil twins did not know, they are two of them.
There was no way around deleting those lines and inserting them again.
Manually, value by value.

Whatever I did, it was done twice.
Deleting by selecting one of the bad twins, deleted both.
Changing the date or the COMM resulted into a copy of evil twins with the new value.
So I had some evil twins more added instead of changed one of the existing.
At least they were deleted easily.

Thank you so very much for this SQL code which was the only way to find them.
I was that surprised, there was a way to make them visible at all!

And the SQL-code is saved and other unique keys checked by it as well.
In the best case my DB is clean now.

[rvk]

And the SQL-code is saved and other unique keys checked by it as well.
In the best case my DB is clean now.

Yes, and a backup and restore is the best way to find that out.
After the restore you can do the line below again to see if all those 3's are gone (they need to be NULL or 0 for ACTIVE).

Code: SQL  [Select][+][-]

1. SELECT * FROM RDB$INDICES WHERE RDB$SYSTEM_FLAG = 0

If all the constraints are set again the DB should be fine (at least you can restore it again  ).

[Nicole]

I am afraid, there are a lot of 3s still.
Never heard of such things before.
What does "3" mean?

This does not say anything to me. What are "Active"?
What does TDB$SYSTEM_Flag mean and most of all:
What can I do?

[rvk]

I am afraid, there are a lot of 3s still.
Never heard of such things before.
What does "3" mean?

I mentioned the 3's here:

I did the new one, but am clueless about what it may mean.

The new one has 0 in RDB$INDEX_INACTIVE (which means it is active because inactive = 1)

You can see the values here: https://ib-aid.com/download/docs/firebird-language-reference-2.5/fblangref-appx04-indices.html

But I see 3 everywhere else in RDB$INDEX_INACTIVE. I myself have never seen the 3.
I see here that 3 also means inactive indices on the constraints. So all the constraints you see with 3 don't have indices !!!!!
(So you have a MAJOR problem in your original database)

I'm not sure if that also means that you can create duplicate records (according to that constraint).

RDB$INDICES has RDB$INDEX_INACTIVE flag that is null or 0 if index is active (after CREATE INDEX or ALTER INDEX ACTIVE). 1 means that index is inactive (after ALTER INDEX INACTIVE). But there is also value of 3 is used to indicate inactive indices on constraints. So, you can set RDB$INDEX_INACTIVE=3 for that index, COMMIT, and than return value to 0 and commit again - index will be rebuilt.

You need to find the records that are not according to the constraints and correct them.
After that... you can enable the constraints/indices again.

The 3's mean the same as 1 in RDB$INDEX_INACTIVE. It means the constraint and index is inactive. That means it's not there. It doesn't do its work.

For the constraints it means it doesn't do the check and for the index it means it doesn't keep the index current. It also means that if you had some index, your database doesn't use it and your whole database is slow as hell on some queries.

Are those 3's still there if you do the backup/recover cycle??
I thought the inactive ones would be activated again.
Did you get any errors during restore??

Maybe that's the reason they are there in the first place, that you did a restore and got errors on those indexes and they were never activated.

If the 3's remain in the restored database and you didn't get any errors you need to activate them all (manually with ALTER or with the EXECUTE BLOCK from the page I mentioned earlier). But first check if you don't get any errors during recover.

[Nicole]

Thank you for the verbose explanation. This time I understood half of it.
Exactly this was my original problem: My software was slooooowww. And as I logged its lines and units, I found the reason, - the insert statements into the database. They took one or two seconds each, an eternity for the job they do.

My next step was to reduce the data. I had them back 30 years. I asked myself: Do I need that?
And reduced it to 3 years and outsourced the original database.
However the speed of the inserts did not increase.

Today in the morning I thought: I have to re-write it.
But how?!
Unfortunately backup out of Firebird with the backup tool - does not work any more. There is an error, which is meaningless. I do not know the number, but I am quite sure, it is not helpful. I hope, all the data are there. I did not miss anything.

My plan - or dream? - would be this:
I print all my tables and check the SQL and ideas.
Then I setup a new database (with the default setting of UTF8!) and play the tables as SQL inside.

This was the easy part.
What I am not sure: How to get the data of the old database into the new one?
The fields will be the same, so there SHOULD work a select * / insert *
IF this is possible as such.
Is it?
And if yes, will I be able to manage this. 

[alpine]

However the speed of the inserts did not increase.

Make sure all of your tables have a PK! Even a surrogate one.
This has a direct impact over how the DB engine works and for the DB internal structure.

[rvk]

However the speed of the inserts did not increase.

Until you re-activate the constaints/indices the database will remain slow.
You NEED to activate them, otherwise this can happen again.

Look at all the indices with

Code: SQL  [Select][+][-]

1. SELECT * FROM RDB$INDICES WHERE RDB$SYSTEM_FLAG = 0

and examine the ones with 1 or 3 in RDB$INDEX_INACTIVE.

You can start by activating one or two constraints. Look at the name and type

Code: SQL  [Select][+][-]

1. ALTER INDEX UNQ1_NAME_U_JAHR ACTIVE

(where UNQ1_NAME_U_JAHR is the chosen index)

If it doesn't give an error then it is activated.
You can do that with all indexes (there is a script where you can do that for all indexes but first try a few manually).

Once they are all activated the database should be a lot snappier.
But it is still advised to do a backup/recover cycle after that (for garbage cleanup etc).

Unfortunately backup out of Firebird with the backup tool - does not work any more. There is an error, which is meaningless. I do not know the number, but I am quite sure, it is not helpful. I hope, all the data are there. I did not miss anything.

You mean the backup doesn't work anymore.
Important stuff (seems to me ). You need regular backups.

What I am not sure: How to get the data of the old database into the new one?
The fields will be the same, so there SHOULD work a select * / insert *
IF this is possible as such.
Is it?
And if yes, will I be able to manage this. 

FlameRobin can export all records to a text file as INSERT statements.
But that will only work for regular text fields, not for BLOB fields.
And when executing it for reading the records back in you need to make sure the character set is correct.
Not easy if it contains lots of different things.

[Nicole]

Pls be so kind, to check my new question
https://forum.lazarus.freepascal.org/index.php?board=16.0