its more a database issue. read this
http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=ddl-constraints.html#DDL-CONSTRAINTS-FK
a foreign key constraint basically tells the db that the column value in the object table
must be a valid entry in the foreign table.
As a work around, if my column does not always have to have a value (NULL o.k.) I don't apply
a foreign key constraint. You can, in the case of Postgresql, apply a trigger.
http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=sql-createtrigger.html
your trigger would simply validate the value if and only if the value is not null.
hope that helps.
-----Original Message-----
From: Stephen Eaton [mailto:seaton@gateway.net.au]
Sent: Sat 5/3/2003 10:31 PM
To: torque-user@db.apache.org
Cc:
Subject: working with foreign keys and null references
G'day one and all,
I'm having some issues with my schema since migrating from mysql to
postgres. In particular I'm trying to insert a record that has a foriegn key
that is not required however I keep getting the following error
"service_fk_1 referential integrity violation - key referenced from service
not found in a_contact"
The following is the table in question, as you can see I am using the single
table inheritance model, the field in question, swim_contact_id is only
required on one of the sub-classes and hence should be null most of the
time, however postgres/torque spits the dummy everytime I try to insert a
record with that field null field, I have also tried setting it to 0 as
well.
Do I just have to have dummy records in the foriegn tables at index 0 (not
really that desirable)
<table name="service" idMethod="idbroker">
<column name="id" primaryKey="true" required="true" inheritance="false"
type="BIGINT"/>
<column name="service" required="true" default="null" inheritance="single"
type="VARCHAR" size="16">
<inheritance key="EmailService" class="AEmailService"
extends="mysouthwest.swim.om.Service"/>
<inheritance key="SwimService" class="ASwimService"
extends="mysouthwest.swim.om.Service"/>
<inheritance key="UrlService" class="AUrlService"
extends="mysouthwest.swim.om.Service"/>
<inheritance key="KeywordService" class="AKeywordService"
extends="mysouthwest.swim.om.Service"/>
<inheritance key="CategoryService" class="ACategoryService"
extends="mysouthwest.swim.om.Service"/>
<inheritance key="AdvertisingService" class="AAdvertisingService"
extends="mysouthwest.swim.om.Service"/>
<inheritance key="MiscService" class="AMiscService"
extends="mysouthwest.swim.om.Service"/>
</column>
<column name="status" required="true" default="null" inheritance="false"
type="VARCHAR" size="16"/>
<column name="date_start" required="true" inheritance="false"
type="DATE"/>
<column name="date_renew" required="false" inheritance="false"
type="DATE"/>
<column name="org_id" required="false" inheritance="false"
type="INTEGER"/>
<column name="invoice_id" required="true" inheritance="false"
type="INTEGER"/>
<column name="description_id" required="false" inheritance="false"
type="INTEGER"/>
<column name="hits" required="true" default="0" inheritance="false"
type="INTEGER"/>
<column name="qty" required="true" default="0" inheritance="false"
type="INTEGER"/>
<column name="email" required="false" default="null" inheritance="false"
type="VARCHAR" size="255"/>
<column name="url" required="false" default="null" inheritance="false"
type="VARCHAR" size="255"/>
<column name="notification_id" required="false" default="0"
inheritance="false" type="INTEGER"/>
<column name="swim_contact_id" required="false" default="0"
inheritance="false" type="INTEGER"/>
<foreign-key foreignTable="a_contact">
<reference local="swim_contact_id" foreign="contact_id"/>
</foreign-key>
<foreign-key foreignTable="a_notification">
<reference local="notification_id" foreign="id"/>
</foreign-key>
<foreign-key foreignTable="service_description">
<reference local="description_id" foreign="id"/>
</foreign-key>
<foreign-key foreignTable="a_organisation">
<reference local="org_id" foreign="org_id"/>
</foreign-key>
<foreign-key foreignTable="invoice">
<reference local="invoice_id" foreign="id"/>
</foreign-key>
</table>
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
|