Alexey Gaidukov wrote :
>
>a testcase.
>
>
>
>CREATE TABLE "TTT"."DID_DOC"
>(
> "COUNTER" Integer NOT NULL,
> "RESID" Varchar (10) UNICODE NOT NULL,
> "RES_NUM" Smallint
>)
>//
>CREATE TABLE "TTT"."PATOMOR"
>(
> "ID" Integer NOT NULL,
> "COUNTER" Integer NOT NULL,
> "RESID" Varchar (10) UNICODE NOT NULL,
> "RES_NUM" Smallint,
> PRIMARY KEY ("ID")
>)
>//
>CREATE UNIQUE INDEX "DID_DOC_UNIQ" ON "TTT"."DID_DOC"("COUNTER"
>ASC,"RESID" ASC,"RES_NUM" ASC)
>//
>CREATE INDEX "PATOMOR_COUNTER" ON "TTT"."PATOMOR"("COUNTER" ASC)
>//
>ALTER TABLE "TTT"."PATOMOR" FOREIGN KEY "PATOMOR_DID_DOC_FK"
>("COUNTER","RESID","RES_NUM") REFERENCES "TTT"."DID_DOC"
>("COUNTER","RESID","RES_NUM") ON DELETE RESTRICT
>//
>insert into ttt.did_doc values(1,'1',null)
>//
>insert into ttt.patomor values(1,1,'1',null)
>//
>delete from ttt.did_doc where counter=1
>
>
>
>
>If remove null value from the PATOMOR_DID_DOC_FK condition the it will
>work as I expect.
>
>insert into ttt.did_doc values(2,'1',2)
>//
>insert into ttt.patomor values(2,2,'1',2)
>//
>delete from ttt.did_doc where counter=2
>
>---- Error -------------------------------
>Auto Commit: On, SQL Mode: Internal, Isolation Level: Committed
> Integrity constraint violation;350 POS(1) Referential integrity
>violated:PATOMOR_DID_DOC_FK,TTT,PATOMOR
>
>
>
>--
>MaxDB Discussion Mailing List
>For list archives: http://lists.mysql.com/maxdb
>To unsubscribe:
>http://lists.mysql.com/maxdb?>[EMAIL PROTECTED]
>
>
This is the defined behavior. If you have a look into the documentation,
you find
the definition for matching rows :
Inserting and Modifying Rows in the Referenced Table
The following restrictions apply when rows in the referencing table are
added or modified:
Let Z be an inserted or modified row. Rows can only be inserted or
modified if one of the following conditions is fulfilled for the
associated referenced table (referenced_table):
* Z is a matching row.
* Z contains a NULL value in one of the referencing columns
(referencing_column).
* The referential CONSTRAINT definition defines the DELETE rule
ON DEFAULT SET DEFAULT, and Z contains the DEFAULT value in each
referencing column.
i.e. two rows do not match, if at least one column contains a null
value. You can
also say, that two null values are not considered to be equal.
Best Regards,
Thomas
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
