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-automations opened this issue Jan 24, 2009 · 15 comments

Comments

@firebird-automations
Copy link
Collaborator

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-automations
Copy link
Collaborator Author

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-automations
Copy link
Collaborator Author

Commented by: Eugenk Konkov (kes)

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

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

Eugenk,

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

@firebird-automations
Copy link
Collaborator Author

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-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Vlad Khorsun [ hvlad ]

@firebird-automations
Copy link
Collaborator Author

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-automations
Copy link
Collaborator Author

Modified by: @hvlad

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

resolution: Fixed [ 1 ]

Fix Version: 2.5 Beta 1 [ 10251 ]

@firebird-automations
Copy link
Collaborator Author

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-automations
Copy link
Collaborator Author

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-automations
Copy link
Collaborator Author

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-automations
Copy link
Collaborator Author

Commented by: @dyemanov

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

@firebird-automations
Copy link
Collaborator Author

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-automations
Copy link
Collaborator Author

Modified by: @pcisar

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Fix Version: 2.1.4 [ 10361 ]

@firebird-automations
Copy link
Collaborator Author

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
Development

No branches or pull requests

2 participants