Hi,
this is the feature – key is only valid(checked) for values but null is not
“value”.
This work same in all databases i have worked.
regards,
Karol Bieniaszewski
From: Paweł Świerzko p_swier...@poczta.onet.pl [firebird-support]
Sent: Wednesday, August 9, 2017 3:21 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Multi-column Foreign key
Hi,
I have encountered the following situation. Can you tell me - is it bug or
feature. Excuse me if it is a silly question.
I have two tables:
CREATE TABLE UNITES (
CODE VARCHAR(10) NOT NULL,
LOT VARCHAR(10) NOT NULL,
SITE VARCHAR(10) NOT NULL,
DESCRIPTION VARCHAR(100)
);
ALTER TABLE UNITES ADD CONSTRAINT PK_UNITES PRIMARY KEY (CODE, LOT, SITE);
CREATE TABLE LOCATION (
ID INTEGER NOT NULL,
UCODE VARCHAR(10),
ULOT VARCHAR(10),
USITE VARCHAR(10),
ZONE INTEGER,
WAREHOUSE INTEGER
);
ALTER TABLE LOCATION ADD CONSTRAINT PK_LOCATION PRIMARY KEY (ID);
ALTER TABLE LOCATION ADD CONSTRAINT FK_LOCATION_1 FOREIGN KEY (UCODE, ULOT,
USITE) REFERENCES UNITES (CODE, LOT, SITE);
So location references units using foreign key.
Let's create two unites from one lot:
INSERT INTO UNITES (CODE, LOT, SITE, DESCRIPTION) VALUES ('A1', 'L1', 'S1',
'abc');
INSERT INTO UNITES (CODE, LOT, SITE, DESCRIPTION) VALUES ('A2', 'L1', 'S1',
'bcd');
I expected that the following instruction will not be exectued:
INSERT INTO LOCATION (ID, UCODE, ULOT, USITE, ZONE, WAREHOUSE) VALUES (1, 'A3',
NULL, NULL, NULL, NULL);
because there is no A3 entry in unites table, but it IS! Firebird allows to add
record with value which doesn't exist in master table.
Thanks in advance
Pawel
---
Ta wiadomość została sprawdzona na obecność wirusów przez oprogramowanie
antywirusowe Avast.
https://www.avast.com/antivirus
