Issue Details (XML | Word | Printable)

Key: CORE-2289
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Minor Minor
Assignee: Vlad Khorsun
Reporter: Eugenk Konkov
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Wrong (primary) constraint name is reported for the foreign key violation during FK creation

Created: 24/Jan/09 04:53 PM   Updated: 10/Mar/10 11:58 AM
Component/s: None
Affects Version/s: None
Fix Version/s: 2.5 Beta 1, 2.1.4

Time Tracking:
Not Specified

Planning Status: Unspecified


 Description  « Hide
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?


 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Adriano dos Santos Fernandes added a comment - 24/Jan/09 05:04 PM
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.

Eugenk Konkov added a comment - 24/Jan/09 06:20 PM
You are right, all errors caused by alter statement.
About which system tables you speak?

Sean Leyne added a comment - 24/Jan/09 11:17 PM
Eugenk,

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

Dmitry Yemanov added a comment - 25/Jan/09 05:05 AM
"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 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.

Vlad Khorsun added a comment - 25/Jan/09 07:04 AM
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 :-)

Adriano dos Santos Fernandes added a comment - 25/Jan/09 10:29 AM
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.

Vlad Khorsun added a comment - 25/Jan/09 10:36 AM
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.

Eugenk Konkov added a comment - 27/Jan/09 03:30 PM
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.

Dmitry Yemanov added a comment - 28/Jan/09 01:48 PM
I'm changing the ticket type and its title to reflect what has been actually fixed.