473,888 Members | 1,376 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Cyclic foreign key dependency & table inheritance

I've got a problem which I think may be a bug in Postgres, but I wonder if
I'm missing something. Two tables, A & B have foreign key relations to each
other. A 3rd table C, inherits from A. A stored procedure updates a row in
C, adds a row each in B & C. I get an integrity violation. All the foreign
keys are deferrable, and the stored procedure is called from within a
transaction with constraints deferred. (And the foreign keys do refer to
rows that exist.)

The cycle is handled in the DDL by creating A without the foreign keys,
creating B with the foreign keys referencing A, then using ALTER TABLE to
add the foreign key constraints onto A after A, B & C have all been created.
But if I create B with foreign keys referencing C instead of A, I don't get
the integrity violation. It's as though while evaluating the constraints at
the end of the transaction, it can find the C row I just inserted, but
cannot find the A row which is the C row's "base class".

Even stranger is that if I start with that theory and try to construct a
simple test case, I cannot reproduce it. But I can start with my much more
complex code and strip it down to a fairly bare example, and the problem
does reproduce--including that if I create B to refer to C instead of A, the
error goes away.

This is all in 7.3.4, on OS X 10.2.6. The sample gives the error message
"ERROR: $1 referential integrity violation - key referenced from
PatientReassign ment not found in PatientRelated" . If the definition of
PatientReassign ment is changed so that the foreign keys reference
PatientCall instead of PatientRelated, the error goes away. Here's the
sample:
------------ sample 1

create table "PatientRelated " (
"id" int8 primary key,
"ReassignmentId " int8 null
) without oids;

create table "PatientCal l" (
constraint "pk" primary key ("id")
) inherits ("PatientRelate d") without oids;

create table "PatientReassig nment" (
"id" int8 primary key,
"OrigId" int8 references "PatientRelated " deferrable not null,
"NewId" int8 references "PatientRelated " deferrable not null
) without oids;

alter table "PatientRelated " add constraint
"fk1" foreign key ("ReassignmentI d") references "PatientReassig nment"
deferrable;

create function "PatientCall_re assign" () returns int8 as '
begin
update "PatientCal l" set "ReassignmentId " = 3 where id = 1;
insert into "PatientCal l" values (2, NULL);
insert into "PatientReassig nment" values (3, 1, 2);
return 2;
end;
' language 'plpgsql';

insert into "PatientCal l" values (1, NULL);

begin; set constraints all deferred;
select "PatientCall_re assign" ();
commit;

------------
Yet this sample does *not* give an integrity violation, even though it's
exactly the same structure (unless I made a mistage) with different names:
------------ sample 2

create table base (
id int8 primary key,
transferid int8 null
) without oids;

create table owned (
constraint "pk" primary key (id)
) inherits (base) without oids;

create table transfer (
id int8 primary key,
origownedid int8 references owned deferrable not null,
newownedid int8 references owned deferrable not null
) without oids;

alter table base add constraint
"fk1" foreign key (transferid) references transfer deferrable;

create function test() returns int8 as '
begin
update owned set transferid = 3 where id = 1;
insert into owned values (2, NULL);
insert into transfer values (3, 1, 2);
return 0;
end;
' language 'plpgsql';

insert into owned values (1, NULL);

begin; set constraints all deferred; select test(); commit;

------------
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 11 '05 #1
4 4049
Hmm. I read my examples over and over, but as soon as it was emailed back to
me I noticed a mistake ;-)

In the second example, I had foreign key references to the derived table,
like so:

create table transfer (
id int8 primary key,
origownedid int8 references owned deferrable not null,
newownedid int8 references owned deferrable not null
) without oids;

And that working correctly is consistent with the first example. Changing it
to:

create table transfer (
id int8 primary key,
origownedid int8 references base deferrable not null,
newownedid int8 references base deferrable not null
) without oids;

Gives the constraint violation error, also consistent with the first
example.

So the behavior is consistent and easily explainable. Now my question is
reduced to: is this really correct behavior? Should a foreign key constraint
referencing a base table really not be satisfied by a row of a table that
inherits from the referenced base table??? That seems wrong to me.
--
Scott Ribe
sc********@kill erbytes.com
http://www.killerbytes.com/
(303) 665-7007 voice
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 11 '05 #2
Hmm. I read my examples over and over, but as soon as it was emailed back to
me I noticed a mistake ;-)

In the second example, I had foreign key references to the derived table,
like so:

create table transfer (
id int8 primary key,
origownedid int8 references owned deferrable not null,
newownedid int8 references owned deferrable not null
) without oids;

And that working correctly is consistent with the first example. Changing it
to:

create table transfer (
id int8 primary key,
origownedid int8 references base deferrable not null,
newownedid int8 references base deferrable not null
) without oids;

Gives the constraint violation error, also consistent with the first
example.

So the behavior is consistent and easily explainable. Now my question is
reduced to: is this really correct behavior? Should a foreign key constraint
referencing a base table really not be satisfied by a row of a table that
inherits from the referenced base table??? That seems wrong to me.
--
Scott Ribe
sc********@kill erbytes.com
http://www.killerbytes.com/
(303) 665-7007 voice
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 11 '05 #3
Scott Ribe <sc********@kil lerbytes.com> writes:
I've got a problem which I think may be a bug in Postgres, but I wonder if
I'm missing something. Two tables, A & B have foreign key relations to each
other. A 3rd table C, inherits from A.


C will not participate in the foreign key relationship. This is a
well-known shortcoming of the inheritance mechanism. It may get fixed
some day, but don't hold your breath ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

Nov 11 '05 #4
Scott Ribe <sc********@kil lerbytes.com> writes:
I've got a problem which I think may be a bug in Postgres, but I wonder if
I'm missing something. Two tables, A & B have foreign key relations to each
other. A 3rd table C, inherits from A.


C will not participate in the foreign key relationship. This is a
well-known shortcoming of the inheritance mechanism. It may get fixed
some day, but don't hold your breath ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

Nov 11 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
2789
by: Brian Sabolik | last post by:
I'm not sure if I've broken any Object Oriented rules or not, but ... I have projects in 2 different solutions that need to use each other's methods. Therefore I may have an "update" method in project 1-solution A accessing a "save" method in project 2-solution B as well as "getinfo" method in project 2 accessing a "read" method in project A. Is this permitted? I am getting the "dependency file in project cannot be copied... conflict...
3
2245
by: Dennis Lerche | last post by:
Hi I have a problem regarding cyclic dependency, yeahh I know bad design. But right at this moment I can't see how it should be redesigned to avoid this. The problem is that I just can't get it to compile ..... I have two classes each having their own header files, including each other. A forward decleration doesn't seem to be enough because they also call function calls within the classes. How do I solve this ???
0
1418
by: Scott Ribe | last post by:
I've got a problem which I think may be a bug in Postgres, but I wonder if I'm missing something. Two tables, A & B have foreign key relations to each other. A 3rd table C, inherits from A. A stored procedure updates a row in C, adds a row each in B & C. I get an integrity violation. All the foreign keys are deferrable, and the stored procedure is called from within a transaction with constraints deferred. (And the foreign keys do refer to...
6
2816
by: Brendan Jurd | last post by:
Hi all, I read on the manual page for Inheritance that: "A limitation of the inheritance feature is that indexes (including unique constraints) and foreign key constraints only apply to single tables, not to their inheritance children. Thus, in the above example, specifying that another table's column REFERENCES cities(name) would allow the other table to contain city names but not capital names. This deficiency will probably be fixed...
10
17883
by: D. Dante Lorenso | last post by:
I'd like to run a clean up command on my tables to eliminate rows that I'm no longer using in the database. I want to do something like this: DELETE FROM tablename WHERE IS_REFERENCED_BY_FOREIGN_KEY IS FALSE; Does anyone know how something like this could be done in PostgreSQL? I know I can search all the tables that
3
5398
by: Alex Satrapa | last post by:
There's some mention in the (old!) documentation that constraints such as foreign keys won't include data from inheriting tables, eg: CREATE TABLE foo ( id SERIAL PRIMARY KEY ); CREATE TABLE bar ( attribute integer NOT NULL ) INHERITS (foo);
3
2756
by: fc2004 | last post by:
Hi, Is there any tools that could report where cyclic header dependency happens? this would be useful when working with a large project where tens or hundreds of headers files may form complex inclusion relationships. Another question I have is how to solve this dependency when "typedef" is used. usually we can use forward declaration like "struc A; " to solve references like "struct A* p". But in many places we have
4
1739
by: sakis.panou | last post by:
Hi all, Can anyone explain to me why the copy constructor of the COuterClass is getting called for this one? Let me start by saying I reckon this is seriously bad way of implementing anything of the sort, we have a way around this. I am simply trying to understand the order or creation and the reason for the call to the COuterClass copy constructor. Any help would be seriously appreciated. Thanks in advance. class COuterClass;
1
677
by: pallav | last post by:
I have to header files, circuit.h and latch.h that reference each other and are causing a cyclic dependency. latch.h file #include "circuit.h" typedef boost::shared_ptr<struct LatchLatchPtr; class Latch {
3
1775
by: soup007 | last post by:
Hi, I am having some difficulties with cyclic dependency between two classes. Situation is something like following - ///A.h #include "B.h" class A { { int X; public:
0
9961
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
11185
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10778
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10887
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10439
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9597
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7990
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
1
4642
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3252
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.