Before I used Oracle 8i and I didn't have this problem.
Your explanation means that I should not use NULL values with FOREIGN
KEY. It is not convenient.
Thanks.
Anhaus, Thomas пишет:
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]
