FK violation when creating FK, but tables are empty!

[Martijn Tonies]

Hi all,

I'm trying to create a Foreign Key Constraint and I'm getting the following
error:

violation of FOREIGN KEY constraint "FK_LOG_ITEM_LOG" on table "LOG_ITEM"
Foreign key reference target does not exist


However, both tables LOG (master) and LOG_ITEM (detail) are empty!


Statement:
ALTER TABLE LOG_ITEM ADD CONSTRAINT FK_LOG_ITEM_LOG
FOREIGN KEY (LOGID) REFERENCES "LOG"
(LOGID)
ON DELETE CASCADE
ON UPDATE NO ACTION


Firebird version:
WI-V6.3.0.26074 Firebird 2.5


Any clue?

With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!

[Mark Rotteveel]

Post by Martijn Tonies
Hi all,
I'm trying to create a Foreign Key Constraint and I'm getting the following
violation of FOREIGN KEY constraint "FK_LOG_ITEM_LOG" on table "LOG_ITEM"
Foreign key reference target does not exist
However, both tables LOG (master) and LOG_ITEM (detail) are empty!
ALTER TABLE LOG_ITEM ADD CONSTRAINT FK_LOG_ITEM_LOG
FOREIGN KEY (LOGID) REFERENCES "LOG"
(LOGID)
ON DELETE CASCADE
ON UPDATE NO ACTION
WI-V6.3.0.26074 Firebird 2.5
Any clue?

Doesn't this error indicate that the table or column you are referencing
doesn't exist? Did you commit the LOG table, is the table actually called
"LOG", and not "Log", does the column LOGID exist?

Mark

[Helen Borrie]

Post by Martijn Tonies

Post by Martijn Tonies
Hi all,
I'm trying to create a Foreign Key Constraint and I'm getting the following
violation of FOREIGN KEY constraint "FK_LOG_ITEM_LOG" on table

"LOG_ITEM"

Post by Martijn Tonies
Foreign key reference target does not exist
However, both tables LOG (master) and LOG_ITEM (detail) are empty!
ALTER TABLE LOG_ITEM ADD CONSTRAINT FK_LOG_ITEM_LOG
FOREIGN KEY (LOGID) REFERENCES "LOG"
(LOGID)
ON DELETE CASCADE
ON UPDATE NO ACTION
WI-V6.3.0.26074 Firebird 2.5
Any clue?

Doesn't this error indicate that the table or column you are referencing
doesn't exist? Did you commit the LOG table, is the table actually called
"LOG", and not "Log", does the column LOGID exist?

...

Or could be there's no PK or unique constraint on "LOG".LOGID.

./hb

[Martijn Tonies]

Post by Helen Borrie

Post by Martijn Tonies

Post by Martijn Tonies
I'm trying to create a Foreign Key Constraint and I'm getting the following
violation of FOREIGN KEY constraint "FK_LOG_ITEM_LOG" on table

"LOG_ITEM"

Post by Martijn Tonies
Foreign key reference target does not exist
However, both tables LOG (master) and LOG_ITEM (detail) are empty!
ALTER TABLE LOG_ITEM ADD CONSTRAINT FK_LOG_ITEM_LOG
FOREIGN KEY (LOGID) REFERENCES "LOG"
(LOGID)
ON DELETE CASCADE
ON UPDATE NO ACTION
WI-V6.3.0.26074 Firebird 2.5
Any clue?

Doesn't this error indicate that the table or column you are referencing
doesn't exist? Did you commit the LOG table, is the table actually called
"LOG", and not "Log", does the column LOGID exist?

Or could be there's no PK or unique constraint on "LOG".LOGID.

...

no, generated statement based on selecting the constraint from a drop down
box. ;)

With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!

[Martijn Tonies]

That is, more or less.

I restarted Firebird, problem is gone.

Even though I already did a sweep and everything, it sounds like there
was some kind of left over transaction or something?

With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!

[Tupy... nambá]

Dear sirs,
 
 
These days, when restoring a backup of us, I noted that some foreign keys have not been recreated at a new copy of a DB.
 
I donŽt know exactly when this happens = if the backup itself is generated without these faulting FKŽs or if the fail happens at the DB recreation process.
 
I ask for your gentle help to resolve this problem. Any indication can be of value and will be welcome.
 
In advance, my thanks.
 
Best regards,
Roberto Camargo
Rio de Janeiro/Brazil

[Non-text portions of this message have been removed]

[Martijn Tonies]

Hello Mark,

Post by Martijn Tonies

Post by Martijn Tonies
I'm trying to create a Foreign Key Constraint and I'm getting the following
violation of FOREIGN KEY constraint "FK_LOG_ITEM_LOG" on table

"LOG_ITEM"

Post by Martijn Tonies
Foreign key reference target does not exist
However, both tables LOG (master) and LOG_ITEM (detail) are empty!
ALTER TABLE LOG_ITEM ADD CONSTRAINT FK_LOG_ITEM_LOG
FOREIGN KEY (LOGID) REFERENCES "LOG"
(LOGID)
ON DELETE CASCADE
ON UPDATE NO ACTION
WI-V6.3.0.26074 Firebird 2.5
Any clue?

Doesn't this error indicate that the table or column you are referencing
doesn't exist? Did you commit the LOG table, is the table actually called
"LOG", and not "Log", does the column LOGID exist?

...

The table is LOG, with column LOGID as the PK.

Unknown columns gets error:

unsuccessful metadata update
could not find UNIQUE or PRIMARY KEY constraint in table LOG with specified
columns

Unknown table gets error:

unsuccessful metadata update
Table LOG2 not found


The statement was generated by Database Workbench, I added quotes because
I thought maybe there was a bug in DBW and it would help, same result
though.

With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!