OSEC

Neohapsis is currently accepting applications for employment. For more information, please visit our website www.neohapsis.com or email hr@neohapsis.com
 
How to define 2 FK into 1 table

From: Dusan Kolesar (d.kolesargmail.com)
Date: Tue Jan 31 2006 - 07:56:39 CST

Hello

I want to define folowing DB structure:
CREATE TABLE "SYSUSER"
(
   "ID" Integer NOT NULL DEFAULT SERIAL,
   "TSTAMP" Timestamp NOT NULL DEFAULT TIMESTAMP,
   "NAME" Varchar(30) ASCII NOT NULL,
   "PASSWORD" Varchar(30) ASCII NOT NULL,
   PRIMARY KEY ("ID")
)
//
CREATE TABLE "ADDRESS"
(
   "ID" Integer NOT NULL DEFAULT SERIAL,
   "TSTAMP" Timestamp NOT NULL DEFAULT TIMESTAMP,
   "CREATE_USER_ID" Integer NOT NULL DEFAULT -1,
   "CHANGE_USER_ID" Integer NOT NULL DEFAULT -1,
   "NAME" Varchar(254) ASCII NOT NULL DEFAULT '',
   PRIMARY KEY ("ID"),
   FOREIGN KEY "SYSUSER_CREATE_ID" ("CREATE_USER_ID")
     REFERENCES "SYSUSER" ("ID") ON DELETE SET DEFAULT,
   FOREIGN KEY "SYSUSER_CHANGE_ID" ("CHANGE_USER_ID")
     REFERENCES "SYSUSER" ("ID") ON DELETE SET DEFAULT
)
//
General error;-7040 POS(1) Referential constraint not allowed

Table Address has 2 foreign keys referencing into 1 table (delete rule is
SET NULL).
I don't understand reason why I can't define such FK.
It is posible only with delete rule is RESTRICT.

Dusan

--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb