Hi,
the problem is your default definition of the create and change user id. I
find it in a way strange, that you forbid null values on the one hand, and on
the other you define a default value of -1 (obviously to express, that this
is not a valid user id).
My proposol is, that you allow null values (you could forbid it for the create
user id, as if the row exists it was obviously created) and ommit the on
delete clause. That's for sure the better way.
The other solution could, not to define a foreign key and add a constraint.
Hopes, that'll help you
Am Dienstag, 31. Januar 2006 14:56 schrieb Dusan Kolesar:
> 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
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
