db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Tuncay Baskan (─░nternet Grubu) <Tuncay.Bas...@intertech.com.tr>
Subject Nullable columns as foreign keys fail
Date Mon, 20 Jan 2003 22:18:30 GMT
Hello all,

In my schema I have a table definition which has a column references the
same table. The type of the column is integer and it is required="false".
Here is the relevant excerpt from the schema.xml file.

----------------------------------------------
<table name="task" idMethod="native">
	<column name="project_id"  required="true"  type="INTEGER"/>
	<column name="task_id"     required="true"  primaryKey="true"
type="INTEGER" autoIncrement="true"/>
	<column name="name"        required="true"  type="VARCHAR"
size="100"/>
	<column name="parent_id"   required="false" type="INTEGER"
default="1"/>
	
	<index name="fk_project_id"><index-column
name="project_id"/></index>
	<index name="fk_task_id"><index-column name="task_id"/></index>
	<index name="fk_parent_id"><index-column name="parent_id"/></index>
	
	<foreign-key foreignTable="task">
		<reference foreign="task_id" local="parent_id"/>
	</foreign-key>
	<foreign-key foreignTable="project">
		<reference foreign="project_id" local="project_id"/>
	</foreign-key>
</table>
----------------------------------------------

Now, when I try to create a Task object and call save method I get a "cannot
add child row, foreign key rule fails" message from MySQL. After long hours
of digging in the source I found that when Criteria prepares the SQL it
always puts "parent_id" column into the list. From my point of view, when I
set required="false" Torque should not insist of taking "parent_id" into
account. Is this a known problem, or am I making a mistake?

Simplest solution (or we may call it as crap), is to put a dummy task with
the id 0 by default so when I call save method of Task objects their parent
will automatically set to that task.

You may say it is no different than having NULL as parent. But, this time I
need to put a dummy project in the "project" table with the id 0 and again I
need to put a dummy department to "department" table. I have a lot of tables
in this way and it sounds too stupid to me.

I hope I'm making a mistake..

/tb.

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message