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
