db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Russell Simpkins" <RussellSimpk...@funnygarbage.com>
Subject RE: working with foreign keys and null references
Date Mon, 05 May 2003 01:32:41 GMT
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
	
	

Mime
View raw message