You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by jazz <ja...@sqmail.me> on 2024/01/30 20:52:31 UTC
DERBY-6303: Add ability to defer enforcement of unique indexes until transaction end
Hi,
It would be great if this issue could be implemented, I have a use case
for testing. How do I get access to JIRA to update the ticket? (to
include the sql files for testing).
When importing data into the database (which has foreign keys and unique
constraints) this is useful. Import fails due to a deferred constraint
violation due to the unique index
(SQL0000000140-0fe82566-018d-4f9a-090e-0000170eba31):
ERROR 23516: The transaction was aborted because of a deferred
constraint violation: Foreign key
'FOREIGN_KEY_director_talent_id_talent_talent_id' defined on
"APP"."DIRECTOR" referencing constraint
'SQL0000000140-0fe82566-018d-4f9a-090e-0000170eba31' defined on
"APP"."TALENT", key ''.
Import flow is (in ij):
autocommit off;
run('movies_insert.sql');
commit;
Foreign keys are created with deferred constraints (deferrable initially
deferred).
Any help for a workaround or fix is highly appreciated.
Best regards,
Bart
Re: DERBY-6303: Add ability to defer enforcement of unique indexes until transaction end
Posted by Bart van der Bilt <ja...@sqmail.me>.
On 2/1/24 00:51, Rick Hillegas wrote:
> When I run fxmovierentals6-all.sql, I get a violation of a different
> foreign key than the one mentioned in the comment at the end of the
> script. I get this error:
>
> ERROR 23516: The transaction was aborted because of a deferred
> constraint violation: Foreign key
> 'FK9_talent_talent_id_TalentPhoto_talent_id' defined on "APP"."TALENT"
> referencing constraint
> 'SQL0000000362-4dc6d895-018d-4f9a-090e-0000170eba31' defined on
> "APP"."TALENTPHOTO", key '(0,(2,90))'.
>
> You are missing 3 matching keys in the referenced table. The following
> query (added just before the failed COMMIT) returns the following result:
>
> ij> SELECT talent_id FROM talent WHERE talent_id NOT IN (SELECT
> talent_id FROM talentphoto);
> TALENT_ID
> -----------
> 0
> 1
> 2
>
Thanks for this query that really helped me! The data had indeed quite
some issues. After fixing them, and using normal PK's everything works
great.
No need for special tricks during data import now.
Thanks again for your help.
Best regards, Bart
P.D. 1: I am not sure how to test JIRA issue DERBY-6303 since the PK's
are also unique.
P.D. 2: Could this database be useful to include in the Derby
distribution as example?
> It appears that you need to fix your data.
>
> Hope this helps,
> -Rick
>
> On 1/31/24 1:06 PM, Bart van der Bilt wrote:
>>
>> On 1/31/24 00:51, Rick Hillegas wrote:
>>> On 1/30/24 12:52 PM, jazz wrote:
>>>> Hi,
>>>>
>>>> It would be great if this issue could be implemented, I have a use
>>>> case for testing. How do I get access to JIRA to update the ticket?
>>>> (to include the sql files for testing).
>>> You can request a JIRA account here:
>>> https://selfserve.apache.org/jira-account.html
>>
>> Thanks, much appreciated. I added two SQL files in the JIRA ticket
>> for testing.
>>
>>>>
>>>> When importing data into the database (which has foreign keys and
>>>> unique constraints) this is useful. Import fails due to a deferred
>>>> constraint violation due to the unique index
>>>> (SQL0000000140-0fe82566-018d-4f9a-090e-0000170eba31):
>>>>
>>>> ERROR 23516: The transaction was aborted because of a deferred
>>>> constraint violation: Foreign key
>>>> 'FOREIGN_KEY_director_talent_id_talent_talent_id' defined on
>>>> "APP"."DIRECTOR" referencing constraint
>>>> 'SQL0000000140-0fe82566-018d-4f9a-090e-0000170eba31' defined on
>>>> "APP"."TALENT", key ''.
>>>>
>>>> Import flow is (in ij):
>>>>
>>>> autocommit off;
>>>> run('movies_insert.sql');
>>>> commit;
>>>>
>>>> Foreign keys are created with deferred constraints (deferrable
>>>> initially deferred).
>>>>
>>>> Any help for a workaround or fix is highly appreciated.
>>>
>>> The only workaround which occurs to me is to replace your unique
>>> indexes with unique constraints.
>>
>> Thanks for the advice. This is what I did. Still no luck. Maybe I am
>> doing something wrong here. I guess the question is how to insert
>> data when foreign keys are already created.
>>
>> Tried to workaround this issue by creating the unique constraints
>> (they should be primary keys). Does it have to do something with the
>> created backing indexes which are not deferrable?
>>
>> Best regards, Bart
>>
>>>
>>> -Rick
>>>
>>>>
>>>> Best regards,
>>>>
>>>> Bart
>>>>
>>>
>
Re: DERBY-6303: Add ability to defer enforcement of unique indexes until transaction end
Posted by Rick Hillegas <ri...@gmail.com>.
When I run fxmovierentals6-all.sql, I get a violation of a different
foreign key than the one mentioned in the comment at the end of the
script. I get this error:
ERROR 23516: The transaction was aborted because of a deferred
constraint violation: Foreign key
'FK9_talent_talent_id_TalentPhoto_talent_id' defined on "APP"."TALENT"
referencing constraint
'SQL0000000362-4dc6d895-018d-4f9a-090e-0000170eba31' defined on
"APP"."TALENTPHOTO", key '(0,(2,90))'.
You are missing 3 matching keys in the referenced table. The following
query (added just before the failed COMMIT) returns the following result:
ij> SELECT talent_id FROM talent WHERE talent_id NOT IN (SELECT
talent_id FROM talentphoto);
TALENT_ID
-----------
0
1
2
It appears that you need to fix your data.
Hope this helps,
-Rick
On 1/31/24 1:06 PM, Bart van der Bilt wrote:
>
> On 1/31/24 00:51, Rick Hillegas wrote:
>> On 1/30/24 12:52 PM, jazz wrote:
>>> Hi,
>>>
>>> It would be great if this issue could be implemented, I have a use
>>> case for testing. How do I get access to JIRA to update the ticket?
>>> (to include the sql files for testing).
>> You can request a JIRA account here:
>> https://selfserve.apache.org/jira-account.html
>
> Thanks, much appreciated. I added two SQL files in the JIRA ticket for
> testing.
>
>>>
>>> When importing data into the database (which has foreign keys and
>>> unique constraints) this is useful. Import fails due to a deferred
>>> constraint violation due to the unique index
>>> (SQL0000000140-0fe82566-018d-4f9a-090e-0000170eba31):
>>>
>>> ERROR 23516: The transaction was aborted because of a deferred
>>> constraint violation: Foreign key
>>> 'FOREIGN_KEY_director_talent_id_talent_talent_id' defined on
>>> "APP"."DIRECTOR" referencing constraint
>>> 'SQL0000000140-0fe82566-018d-4f9a-090e-0000170eba31' defined on
>>> "APP"."TALENT", key ''.
>>>
>>> Import flow is (in ij):
>>>
>>> autocommit off;
>>> run('movies_insert.sql');
>>> commit;
>>>
>>> Foreign keys are created with deferred constraints (deferrable
>>> initially deferred).
>>>
>>> Any help for a workaround or fix is highly appreciated.
>>
>> The only workaround which occurs to me is to replace your unique
>> indexes with unique constraints.
>
> Thanks for the advice. This is what I did. Still no luck. Maybe I am
> doing something wrong here. I guess the question is how to insert data
> when foreign keys are already created.
>
> Tried to workaround this issue by creating the unique constraints
> (they should be primary keys). Does it have to do something with the
> created backing indexes which are not deferrable?
>
> Best regards, Bart
>
>>
>> -Rick
>>
>>>
>>> Best regards,
>>>
>>> Bart
>>>
>>
Re: DERBY-6303: Add ability to defer enforcement of unique indexes until transaction end
Posted by Bart van der Bilt <ja...@sqmail.me>.
On 1/31/24 00:51, Rick Hillegas wrote:
> On 1/30/24 12:52 PM, jazz wrote:
>> Hi,
>>
>> It would be great if this issue could be implemented, I have a use
>> case for testing. How do I get access to JIRA to update the ticket?
>> (to include the sql files for testing).
> You can request a JIRA account here:
> https://selfserve.apache.org/jira-account.html
Thanks, much appreciated. I added two SQL files in the JIRA ticket for
testing.
>>
>> When importing data into the database (which has foreign keys and
>> unique constraints) this is useful. Import fails due to a deferred
>> constraint violation due to the unique index
>> (SQL0000000140-0fe82566-018d-4f9a-090e-0000170eba31):
>>
>> ERROR 23516: The transaction was aborted because of a deferred
>> constraint violation: Foreign key
>> 'FOREIGN_KEY_director_talent_id_talent_talent_id' defined on
>> "APP"."DIRECTOR" referencing constraint
>> 'SQL0000000140-0fe82566-018d-4f9a-090e-0000170eba31' defined on
>> "APP"."TALENT", key ''.
>>
>> Import flow is (in ij):
>>
>> autocommit off;
>> run('movies_insert.sql');
>> commit;
>>
>> Foreign keys are created with deferred constraints (deferrable
>> initially deferred).
>>
>> Any help for a workaround or fix is highly appreciated.
>
> The only workaround which occurs to me is to replace your unique
> indexes with unique constraints.
Thanks for the advice. This is what I did. Still no luck. Maybe I am
doing something wrong here. I guess the question is how to insert data
when foreign keys are already created.
Tried to workaround this issue by creating the unique constraints (they
should be primary keys). Does it have to do something with the created
backing indexes which are not deferrable?
Best regards, Bart
>
> -Rick
>
>>
>> Best regards,
>>
>> Bart
>>
>
Re: DERBY-6303: Add ability to defer enforcement of unique indexes until transaction end
Posted by Rick Hillegas <ri...@gmail.com>.
On 1/30/24 12:52 PM, jazz wrote:
> Hi,
>
> It would be great if this issue could be implemented, I have a use
> case for testing. How do I get access to JIRA to update the ticket?
> (to include the sql files for testing).
You can request a JIRA account here:
https://selfserve.apache.org/jira-account.html
>
> When importing data into the database (which has foreign keys and
> unique constraints) this is useful. Import fails due to a deferred
> constraint violation due to the unique index
> (SQL0000000140-0fe82566-018d-4f9a-090e-0000170eba31):
>
> ERROR 23516: The transaction was aborted because of a deferred
> constraint violation: Foreign key
> 'FOREIGN_KEY_director_talent_id_talent_talent_id' defined on
> "APP"."DIRECTOR" referencing constraint
> 'SQL0000000140-0fe82566-018d-4f9a-090e-0000170eba31' defined on
> "APP"."TALENT", key ''.
>
> Import flow is (in ij):
>
> autocommit off;
> run('movies_insert.sql');
> commit;
>
> Foreign keys are created with deferred constraints (deferrable
> initially deferred).
>
> Any help for a workaround or fix is highly appreciated.
The only workaround which occurs to me is to replace your unique indexes
with unique constraints.
-Rick
>
> Best regards,
>
> Bart
>