[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
Re: What is a "deferred" violation?
Toby,
Thanks a lot for this input. All I can say is I wish I had thought of it. :-)
I think I see the problem now. Thanks again. You have been extremely helpful.
Best regards.
--- Tobias Downer <toby@mckoi.com> wrote:
> Are the child and parent being inserted on different connections, or are
> records being removed from the parent table by another connection?
>
> Note that this constraint violation is occuring at commit time.
> Consider the following - a client connection starts a transaction and
> establishes a foreign reference from a child to a parent table. Before
> this transaction commits, another transaction deletes the parent record
> and commits the table with the record deleted. When the first
> transaction tries to commit, the engine will see that the referencing
> record no longer exists so committing the insert will not be allowed
> because it would break referential integrity, and so the transaction is
> rolled back.
>
> For example (greatly simplified),
>
> CREATE TABLE Parent ( id INTEGER, PRIMARY KEY (id) );
> CREATE TABLE Child ( parent_id INTEGER,
> FOREIGN KEY (parent_id) REFERENCES Parent );
> INSERT INTO Parent ( id ) VALUES ( 1 ), ( 2 );
> -- switch to Connection A --
> SET AUTO COMMIT OFF;
> INSERT INTO Child ( parent_id ) VALUES ( 2 );
> -- switch to Connection B --
> DELETE FROM Parent WHERE id = 2;
> -- switch to Connection A --
> COMMIT;
>
> The last commit will fail with the error you are seeing. The database
> will be left with Parent containing one entry (1) and the Child table
> being empty.
>
> Note that in the above senario, at no time will the database 'appear' to
> be breaking the referential constraints you've set up. Also note that
> even if auto-commit is on for all your commands, you may still receive
> this error if the DELETE operation occurs in the middle of the INSERT
> operation. This is one of those situations when complex
> multi-connection access to the database can trip you up.
>
> Toby.
>
> M. A. Sridhar wrote:
> > That is very strange. I'm adding a record to each of two tables, a parent
> and
> > a child, in that order. Usually the engine correctly adds the records,
> but
> > sometimes it throws this error. To my knowledge, there is no constraint
> > violation in the data, but perhaps more importantly, the records are
> being
> > added in two separate operations, and there is no transaction in progress
> > (meaning that every transaction is being immediately committed on that
> > connection). So there is no reason for a 'deferred' violation. And no, I
> am
> > not using any special keywords in the constraint definition.
> >
> > And as I said, the error is intermittent -- doesn't always happen. Any
> ideas
> > on how to resolve it?
> >
> > --- Tobias Downer <toby@mckoi.com> wrote:
> >
> >>A deferred constraint violation occurs at commit time and an immediate
> >>constraint violation occurs at update/insert time. You may have
> >>received this error here because another concurrent transaction modified
> >>the foreign table, so while the immediate constraint check passed (the
> >>change is correct with regard to the current transactions view of the
> >>database), it failed at commit time because of a change by another
> >>transaction.
> >>
> >>How is the foreign key set up? Are you using INITIALLY IMMEDIATE or
> >>INITIALLY DEFERRED keywords in the constraint definition? If the
> >>foreign key is INITIALLY DEFERRED then you will only see deferred
> >>constraint violation errors at commit time. The default is INITIALLY
> >>IMMEDIATE.
> >>
> >>Toby.
> >>
> >>M. A. Sridhar wrote:
> >>
> >>>Hi folks,
> >>>
> >>>This is probably a dumb question, but can someone explain this error to
> >>
> >>me:
> >>
> >>>com.mckoi.database.jdbc.MSQLException: Deferred foreign key constraint
> >>>violation (RefOutgoing_mail35) Columns = APP.Unsent_recipient( item_id )
> >>
> >>->
> >>
> >>>APP.Outgoing_mail( item_id )
> >>>
> >>>Does this mean that it is trying to add a record into Unsent_recipient
> >>
> >>whose
> >>
> >>>item_id value does not match any record in the Outgoing_mail table? What
> >>
> >>is
> >>
> >>>the significance of the term 'deferred' there?
> >>>
> >>>TIA.
> >>>
> >>>=====
> >>>------
> >>>M. A. Sridhar
> >>>m_a_sridhar@yahoo.com
>
>
>
> ---------------------------------------------------------------
> Mckoi SQL Database mailing list http://www.mckoi.com/database/
> To unsubscribe, send a message to mckoidb-unsubscribe@mckoi.com
>
=====
------
M. A. Sridhar
m_a_sridhar@yahoo.com
__________________________________________________
Do you Yahoo!?
Yahoo! Tax Center - forms, calculators, tips, more
http://taxes.yahoo.com/