Stored Procedures and Constraints

Thread View: borland.public.interbase.sql

2 messages

[T] Back to Threads [V] Tree View [G] Group Articles

2 total messages Started by Eric Lewis Wed, 27 Nov 2002 16:12

#11323

Author: Eric Lewis
Date: Wed, 27 Nov 2002 16:12

20 lines
1622 bytes

IB 6.0 - NT2000

I have a Stored procedure that calls two other SPs. The first called
procedure gets a generated number, inserts a new record into table "A" with
that number, and passes the number back to the calling procedure. Then the
next procedure called takes that number and inserts a new record into table
"B" that has a constraint stating a Foreign Key related to table "A".

When running the main procedure the following error occurs:

violation of FOREIGN KEY constraint "VALUE_HAS_VALUE" on table "B"

It seems to me that table "A" does not get a commit. So, the values do not
exist for the constraint to validate.

Any suggestions?

Thanks for your time.
--
Eric Lewis

Re: Stored Procedures and Constraints

#11329

Author: Bill Todd
Date: Wed, 27 Nov 2002 18:28

33 lines
2034 bytes


On Wed, 27 Nov 2002 10:12:36 -0600, "Eric Lewis"
<elewis@highergear.com> wrote:

>IB 6.0 - NT2000
>
>I have a Stored procedure that calls two other SPs. The first called
>procedure gets a generated number, inserts a new record into table "A" with
>that number, and passes the number back to the calling procedure. Then the
>next procedure called takes that number and inserts a new record into table
>"B" that has a constraint stating a Foreign Key related to table "A".
>
>When running the main procedure the following error occurs:
>
>violation of FOREIGN KEY constraint "VALUE_HAS_VALUE" on table "B"
>
>It seems to me that table "A" does not get a commit. So, the values do not
>exist for the constraint to validate.

As long as the master record and the detail record are inserted in the
same transaction it should work. Before you commit the transaction
SELECT the new record in the master table and you should see it.

>
>Any suggestions?
>
>Thanks for your time.


--
Bill (TeamB)
(TeamB cannot respond to questions received via email)

Thread Navigation

This is a paginated view of messages in the thread with full content displayed inline.

Messages are displayed in chronological order, with the original post highlighted in green.

Use pagination controls to navigate through all messages in large threads.

Back to All Threads