Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Wrong (primary) constraint name is reported for the foreign key violation during FK creation [CORE2289] #2714

Closed
firebird-issue-importer opened this issue Jan 24, 2009 · 15 comments

Comments

@firebird-issue-importer

Submitted by: Eugenk Konkov (kes)

Can not uderstand what is wrong:

alter table PACKET_DETAIL
add constraint PACKET_DETAIL_FK_PACKET_ID
foreign key (PACKET_ID)
references PACKET(ID)

1#⁠ violation of FOREIGN KEY constraint "".
2#⁠ violation of FOREIGN KEY constraint "PK_PACKET" on table "PACKET".
3#⁠ Foreign key reference target does not exist.
1- what violation?
2- what wrong on this violation?
3- What is target? For whick FK? What does not exist?

Commits: ba4d4fa 8c3db43 bf29465

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 24, 2009

Commented by: @asfernandes

What commands caused all the errors? From your description, I understand all caused by the alter table, but I doubt, because FB system tables does not have foreign keys.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 24, 2009

Commented by: Eugenk Konkov (kes)

You are right, all errors caused by alter statement.
About which system tables you speak?

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 25, 2009

Commented by: Sean Leyne (seanleyne)

Eugenk,

This issue should really discussed in the support list/forum, not here in the bug tracker.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 25, 2009

Commented by: @dyemanov

"Target" means the primary/unique key the foreign key refers to. So, as far as I understand, column PACKET_DETAIL.PACKET_ID has values not found in http://PACKET.ID, hence the violation. However, I'd agree that the text "FOREIGN KEY" along with the *primary key* name and table look not much clear to understand. I'm assigning this ticket to Vlad in a hope for an explanation whether this is a bug or as designed.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 25, 2009

Modified by: @dyemanov

assignee: Vlad Khorsun [ hvlad ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 25, 2009

Commented by: @hvlad

I have no idea of what is #⁠1 message. isql didn't report it while IBE does.
Perhaps its related with SQLERR message at messages2.sql number 470 :

(NULL, NULL, NULL, NULL, 13, 470, NULL, 'violation of FOREIGN KEY constraint "@1"', NULL, NULL);

Message #⁠2 was wrong and i fixed it in HEAD

Message #⁠3 requires just some knowledge of english language or access to some translator :-)

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 25, 2009

Modified by: @hvlad

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 2.5 Beta 1 [ 10251 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 25, 2009

Commented by: @asfernandes

Message #⁠3 means the record on the FK table references a non existent record of the PK table. There is two types of FK violation:

SQL> create database 't.fdb';
SQL> create table pk (n integer primary key);
SQL> create table fk (n integer references pk);
SQL> insert into pk values (1);
SQL> insert into fk values (1);
SQL> insert into fk values (2);
Statement failed, SQLCODE = -530
violation of FOREIGN KEY constraint "INTEG_3" on table "FK"
-Foreign key reference target does not exist
SQL> delete from pk;
Statement failed, SQLCODE = -530
violation of FOREIGN KEY constraint "INTEG_3" on table "FK"
-Foreign key references are present for the record

And I still don't understand why/how the error was caused by the ALTER TABLE statement as the user was said.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 25, 2009

Commented by: @hvlad

Adriano> And I still don't understand why/how the error was caused by the ALTER TABLE statement as the user was said.

Because detail table have records for which there is no corresponding parent record at time when FK is created.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 27, 2009

Commented by: Eugenk Konkov (kes)

I have restart server and
alter table PACKET_DETAIL
add constraint PACKET_DETAIL_FK_PACKET_ID
foreign key (PACKET_ID)
references PACKET(ID)
are executed without any errors.

>This issue should really discussed in the support list/forum, not here in the bug tracker.
I do not need support. I report it here because of it really uncleare to see such messages, far more I do not expect any errors at all. So I point developers for this problem.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 28, 2009

Commented by: @dyemanov

I'm changing the ticket type and its title to reflect what has been actually fixed.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 28, 2009

Modified by: @dyemanov

issuetype: Improvement [ 4 ] => Bug [ 1 ]

summary: Do error reporting more meaningfull => Wrong (primary) constraint name is reported for the foreign key violation during FK creation

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 8, 2009

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Mar 10, 2010

Modified by: @dyemanov

Fix Version: 2.1.4 [ 10361 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented May 27, 2015

Modified by: @pavel-zotov

status: Closed [ 6 ] => Closed [ 6 ]

QA Status: Done successfully

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
2 participants