[solved] "unique" violation in a table

[Nicole]

I have no idea, how this can happen: For the second time, my backup and restore tool reports an unique-requirement-violation in my database (Firebird).

Hard to say if the tool (Firebird editor) caused it or found it.
The last time it was easy to find the very line, but very hard to solve the violation. The moment I deleted the double data-line, BOTH of them were deleted. So I wrote the field-values on a sheet of paper and restored one of the line by FlameRobin.

This time it is harder. The trouble-data are hidden in ten thousands of lines.
Has anybody an idea, how to find the problem line?
SQL does not allow to find something, which is not allowed or does it?

What is the problem is, that a foreign key and a date are the same.
My rule says, they have to be unique.

Any tricks, how to deal with?
The database works, but may be the problem has anything to do with my performance problems which I posted some time before.

This is the DLL of the table

Code: MySQL  [Select][+][-]

1. CREATE TABLE TBCOT
2. (
3.   DATUM date NOT NULL,
4.   FK_COMM char(2) NOT NULL,
5.   SPEC_LONG bigint,
6.   SPEC_SHORT bigint,
7.   COMM_LONG bigint,
8.   COMM_SHORT bigint,
9.   SMALL_LONG bigint,
10.   SMALL_SHORT bigint,
11.   SPEC_NET integer,
12.   COMM_NET integer,
13.   SMALL_NET integer,
14.   CONSTRAINT UNQ_TBCOT_COMM_U_DAT UNIQUE (FK_COMM,DATUM)
15. );
16. ALTER TABLE TBCOT ADD CONSTRAINT FK_TBCOT_1
17.   FOREIGN KEY (FK_COMM) REFERENCES TBKENNZAHLEN (COMM);
18. ALTER TABLE TBCOT ADD CONSTRAINT CHK_DATUMAUFLEER
19.   check (datum > '1.1.1960');
20. GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
21.  ON TBCOT TO  SYSDBA WITH GRANT OPTION GRANTED BY SYSDBA;
22.  
23.  

[rvk]

Where is your primary key? Always have a primary key in a table so you can work with these records and correct them.

But what you encountered shouldn't happen.
It can only happen if the database is corrupt or you have disabled certain constraints.

Normally I put the corrupted database offline, try the validation and mend options, backup and restore without indexes and constraints ( -inactive and - no_validity). Then you can investigate what constraints cause problems and fix it, then enable the indexes/constraints and bring the database back online.

But I would be more worried as to how this could happen.
Were the database constraints ever disabled and the db never put online (i.e. single mode)?

[Zvoni]

Where is your primary key? Always have a primary key in a table so you can work with these records and correct them.

He has a Primary Key: It's the combination of his ForeignKey and Date, because he declares it as UNIQUE, which makes it automatically a PrimaryKey
(OK, i admit: Not in the sense of DDL, but my point stands)

What i can see: Is it even the Violation of that "Unique" Constraint (even if TS stated that he gets that violation, but we don't know the exact Error-message?
I see TS using german Date-Notation in Constraint-Check with a Column of Type Date against a String... *shudder*

Bottom line:
FK_COMM = 2 - DATUM=2024-01-23
FK_COMM = 2 - DATUM=2024-01-22

are valid entries

[rvk]

Where is your primary key? Always have a primary key in a table so you can work with these records and correct them.

He has a Primary Key: It's the combination of his ForeignKey and Date, because he declares it as UNIQUE, which makes it automatically a PrimaryKey
(OK, i admit: Not in the sense of DDL, but my point stands)

Sure, but you can have multiple UNIQUE constraints. That doesn't make it a primary key 

What i can see: Is it even the Violation of that "Unique" Constraint (even if TS stated that he gets that violation, but we don't know the exact Error-message?
I see TS using german Date-Notation in Constraint-Check with a Column of Type Date against a String... *shudder*

Yeah, reason enough to never use combined constraints to emulate your "primary key" 
TS also stated that removing one of the "double" records removed both of them. That leads me to believe that the combined constraints where actually the same (which shouldn't happen with a working constraint in place in a non-corrupt database).

[Nicole]

I AM concerned, how this can happen!!!

other case of trouble, which "cannot happen" in a different table:
Before every entry in the database I check for not being empty / zero / blanc.
For over two years I have in some cases null-values in very rare cases.
There are 3 checks for the parameter "not empty", never the less several months later I wonder why the select-statement is lacking some expected results, - and find a null again there.
I cannot say, how this may happen. Perhaps it is anything behind my code.
Unfortunately this is one string of 2 digits within some months where every day with several thousands of date.
As my error-checks were never ever triggered, I do not even guess at which date this happens.
And if this happened I bet, I cannot reproduce.

But back to our no-primary-key table.
I had no primary key because I want to safe space. The request always will be for the comm and date.
So the primary key would just be an additional thing.
I would not guess, that the primary key is the problem, because the above mentioned zero-value-riddle is in a table which has a primary key.

Not sure, if these error-messages are helpful, this is why I did not post it.
But if they are, - here they are

Code: Text  [Select][+][-]

1. ---------------------------
2. Error
3. ---------------------------
4. action cancelled by trigger (3) to preserve data integrity
5. Cannot deactivate index used by a PRIMARY/UNIQUE constraint
6. unknown ISC error 336330835
7. ---------------------------
8. OK  
9. ---------------------------
10.  

Code: Text  [Select][+][-]

1. gbak:creating indexes
2. gbak:committing metadata
3. gbak:    activating and creating deferred index UNQ_WARE_DATUM
4. gbak:    activating and creating deferred index MYUNIQUE
5. gbak:    activating and creating deferred index UNQ_TBCOT_COMM_U_DAT
6. gbak:cannot commit index UNQ_TBCOT_COMM_U_DAT

[rvk]

I had no primary key because I want to safe space. The request always will be for the comm and date.
So the primary key would just be an additional thing.

The missing primary key is indeed not the problem but it's something I noticed right away because I always have a primary key on ALL my tables. It also makes it easier to work with foreign keys. (I have LINKNUMMER in ALL my tables with which I connect my tables. In that case FK_COMM wouldn't exist in that table because it's already in TBKENNZAHLEN as COMM.)

Not sure, if these error-messages are helpful, this is why I did not post it.
But if they are, - here they are

Code: Text  [Select][+][-]

1. ---------------------------
2. Error
3. ---------------------------
4. action cancelled by trigger (3) to preserve data integrity
5. Cannot deactivate index used by a PRIMARY/UNIQUE constraint
6. unknown ISC error 336330835
7. ---------------------------
8. OK  
9. ---------------------------
10.  

I find the second line very disturbing. "Cannot deactivate index" would indicate it tried to deactivate an index. If it would have succeeded, it would have deactivated a major piece of your database design. Can you tell WHAT SQL statement exactly resulted in this error ???

(This seems to be a backup or restore command because otherwise I can't imagine why a index would be needed to be (de)activated.)

Code: Text  [Select][+][-]

1. gbak:creating indexes
2. gbak:committing metadata
3. gbak:    activating and creating deferred index UNQ_WARE_DATUM
4. gbak:    activating and creating deferred index MYUNIQUE
5. gbak:    activating and creating deferred index UNQ_TBCOT_COMM_U_DAT
6. gbak:cannot commit index UNQ_TBCOT_COMM_U_DAT

Yeah, that's normal if the constraint was not in place (or there was a corruption) and you want to restore that database.

We still need to know if the database is corrupted or the constraint is deactivated in the original database. Do you still have access to that original database?

You need to view all the restraints/indexes to see if they are (IN)ACTIVE.

[Zvoni]

(This seems to be a backup or restore command because otherwise I can't imagine why a index would be needed to be (de)activated.)

And if it is a Backup/Restore thing, are ALL connections to the Database closed?

EDIT: Something else: It always mystifies me, why people create a Table, and then in the next step ALTER it, instead of just putting the Constraints into the CREATE TABLE-Command itself.

Have you tried without the ALTER TABLE-Stuff, but directly within the Create Table-Command?
It might even fail, because from the Create Table it has the UNIQUE-Constraint, and tries to create the Index for that, but then in the next step it wants to ALTER a Column of that Constraint

Untested:

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

1. CREATE TABLE TBCOT
2. (
3.   DATUM DATE NOT NULL,
4.   FK_COMM CHAR(2) NOT NULL,
5.   SPEC_LONG BIGINT,
6.   SPEC_SHORT BIGINT,
7.   COMM_LONG BIGINT,
8.   COMM_SHORT BIGINT,
9.   SMALL_LONG BIGINT,
10.   SMALL_SHORT BIGINT,
11.   SPEC_NET INTEGER,
12.   COMM_NET INTEGER,
13.   SMALL_NET INTEGER,
14.   CONSTRAINT UNQ_TBCOT_COMM_U_DAT UNIQUE (FK_COMM,DATUM),
15.   CONSTRAINT FK_TBCOT_1 FOREIGN KEY (FK_COMM) REFERENCES TBKENNZAHLEN (COMM),
16.   CONSTRAINT CHK_DATUMAUFLEER CHECK (datum > '1.1.1960')
17. );
18.  
19. GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
20.  ON TBCOT TO  SYSDBA WITH GRANT OPTION GRANTED BY SYSDBA;

[rvk]

EDIT: Something else: It always mystifies me, why people create a Table, and then in the next step ALTER it, instead of just putting the Constraints into the CREATE TABLE-Command itself.

Have you tried without the ALTER TABLE-Stuff, but directly within the Create Table-Command?
It might even fail, because from the Create Table it has the UNIQUE-Constraint, and tries to create the Index for that, but then in the next step it wants to ALTER a Column of that Constraint

It happens that I myself also add the INDEX and CONSTRAINTS after the CREATE 
But that's more due to the fact that I have a possible DBASE import stage in between. After the import I can investigate missing (or incorrect) clientrecords and add them (because in the DOS version it was possible to delete client info without deleting order info). Those missing records are added again and after that the constraints are set.

But if the ALTER is directly after the CREATE it could be merged. But it shouldn't matter because directly after CREATE the table is empty and adding the CONSTRAINT would always work afterwards.

I suspect this is really a corrupt database or the constraints/indexes are set to INACTIVE.
Restoring the backup will activate them and it can't do that if invalid data is added.

[Nicole]

@ rvk
My organisation is, that names hint the content.
(there are a few exceptions of very old tables)

The name "fk_comm" says to me "foreign key" at a field by the name of comm.

This means to me:
There is a table tbkennzahlen. It has a privmary key. The name of the primary key is id + table, so pk = id_kennzahlen.

So "fk_comm" holds the value of the primary key=id_kennzahlen

================

Unfortunetely it is not an SQL snipet shows the error, but the restore-tool of Firebird, which is executed by the Freeware Firebird-Editor Pro.
Backup and restore works find. The only thing is the strange error-message.

Yes, I have backups of all my databases, at least I hope so. Unfortunately I am not very firm with databases, so I am not sure, what your sentences mean. I unserstand the words of it, but do not know, how to check or use.

Theses are the sentences I do not undestand.

We still need to know if the database is corrupted or the constraint is deactivated in the original database. Do you still have access to that original database?

You need to view all the restraints/indexes to see if they are (IN)ACTIVE.

@Zvoni
Sure, I may have made errors in the configuration of backup and restore. I used the tool just a handful of times in my lifetime an with this editor I did it for the first time.

The table is many years old. I have no idea, how I made it. Those keys and contraints I made by FlameRobin and usually it was a work of hours to bring them into my DB. After an eternity of clicking and clicking away error-messages, the constaints were "in".

@rvk 2:
It is sure, that I added the contraints AFTER the create. This is the way, either FlameRobin (the older version) works or the only way I understood to use it.

I am afraid, the SQL-history of the item is "lost in bytes".

To sum up:
Is there a way to find out, WHAT in the DB is corrupt? This database is - an important part of my life. This is not overdone.

Are the checking-tools?

[rvk]

Theses are the sentences I do not undestand.

We still need to know if the database is corrupted or the constraint is deactivated in the original database. Do you still have access to that original database?

You need to view all the restraints/indexes to see if they are (IN)ACTIVE.

You are restoring a backup. But the backup is made from a Firebird database (original). You need to get your hands on that original database and investigate it.
Is it corrupt? (You can check that with gbak/gfix).
Are the problem records (duplicates) in that DB?
If there are... Is the constraints in place and active (view all constraints)?

Check for corruption:
gfix -v -n copy1.fdb

See https://www.ibphoenix.com/resources/documents/how_to/doc_5#recognising-possible-corruption

Once you know what the problem is you can go from there.

(For example of the DB is corrupt we can go through the recovery steps. If the DB is not corrupt but the constraints are inactive it needs to be corrected.)

[Nicole]

Dear me!
It is midday and I messed around half the day.

As any newbie like me reads this:
You must not write just the path, but must put " around.
So it is cd "Program Files" if you want to go to the gfix in the Firebird directory.

And if you are there you can double click it fine and there is a text running and disappearing before you can read it.
If you call it from the command prompt of the powershell, - it is "not found" 
As reason I found after too long a time a security element of windows, you have to write "./myexe" as well with the "

Finally it ran. And there was no output.
I hope, this means, that there were no errors found in my database.

hm, clueless.
For my next question I open a new thread.

[rvk]

And if you are there you can double click it fine and there is a text running and disappearing before you can read it.

Woops. I was under the impression that you knew how to work with command console in Windows 

Press start button from Windows
Type cmd
Click the "Command Prompt"
Now you are in a command prompt console

Type the text below (where you change 2_5 into the correct version number):
cd "\Program Files\Firebird\Firebird_2_5\bin"

Now you are in the bin directory of Firebird.
Type the following
gfix -v -full -user sysdba -pass masterkey "name_and_path_of_your_database"

If it doesn't return anything the database should be good.
BTW. You could also have typed this (without the need to do the cd command):
"C:\Program Files\Firebird\Firebird_2_1\bin\gfix.exe" -v -full -user sysdba -pass masterkey "name_and_path_of_your_database"

~~
Next... if the database is good... you need to check if the records you say are duplicates, are present in your working database.
You can do that with the SELECT statements.

*) If there are no duplicate records... then I'm puzzled as to how they are generated in the backup.

*) If there are duplicates, you need to examine the status of the constraints.
select * from RDB$INDICES where RDB$SYSTEM_FLAG = 0
Are there any 1's in the RDB$INDEX_INACTIVE ?

[Nicole]

Finally I had made the Windows thing. I used Dos 6 and Win 3.11.
Then Win XP, the command shell, but forgot those things as I have not used them that long.
Databases are not my main-thing and I wish, it once would work forever, so I can forget about so much admin-work taking my time.

about select:
Such a select statement exists?!
More than select * from tbtable I never tried.

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

[rvk]

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.

[rvk]

about select:
Such a select statement exists?!
More than select * from tbtable I never tried.

You could try something like this:

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

1. SELECT FK_COMM, DATUM, COUNT(*) FROM TBCOT
2. GROUP BY FK_COMM, DATUM
3. HAVING COUNT(*) >= 2