Hello,
"COPY INTO" command fails with "FOREIGN KEY constraint 'xxx' violated" whereas "INSERT INTO" succeeded. Simple 3 tables example (it works with 2 tables, one referencing the other):
CREATE TABLE a ( a1 VARCHAR(3) NOT NULL, CONSTRAINT pka PRIMARY KEY (a1) );
CREATE TABLE b ( b1 VARCHAR(3) NOT NULL, b2 VARCHAR(3) NOT NULL, CONSTRAINT pkb PRIMARY KEY (b1,b2), CONSTRAINT fk1 FOREIGN KEY (b2) REFERENCES a (a1) );
CREATE TABLE c ( c1 varchar(3) NOT NULL, c2 varchar(3) NOT NULL, c3 varchar(3), CONSTRAINT pkc PRIMARY KEY (c1,c2), CONSTRAINT fk2 FOREIGN KEY (c2) REFERENCES a (a1), CONSTRAINT fk3 FOREIGN KEY (c2,c3) REFERENCES b (b1,b2) );
Using INSERT INTO:
sql>insert into a values ('aaa'); 1 affected row sql>insert into c values ('cc1','aaa',null); 1 affected row
Using COPY INTO with one line input file "test.txt" containing : cc2|aaa|
sql>COPY INTO c FROM '/home/mat/test.txt' USING DELIMITERS '|',' more>' NULL as ''; SQLException:assert:INSERT INTO: FOREIGN KEY constraint 'c.fk3' violated
Bug or misunderstanding?
Thanks a lot, Matthieu
PS: mserver5 --version MonetDB server v5.18.3 (64-bit), based on kernel v1.36.3 (64-bit oids) Copyright (c) 1993-July 2008 CWI Copyright (c) August 2008-2010 MonetDB B.V., all rights reserved Visit http://monetdb.cwi.nl/ for further information Found 2.0GiB available memory, 2 available cpu cores Configured for prefix: /usr Libraries: libpcre: 7.8 2008-09-05 (compiled with 7.8) openssl: OpenSSL 0.9.8g 19 Oct 2007 (compiled with OpenSSL 0.9.8g 19 Oct 2007) libxml2: 2.7.5 (compiled with 2.7.5) Compiled by: root@ubuntu-karmic-64 (x86_64-pc-linux-gnu) Compilation: gcc -O2 -Wall -O2 -O6 -fomit-frame-pointer -finline-functions -falign-loops=4 -falign-jumps=4 -falign-functions=4 -fexpensive-optimizations -funroll-loops -frerun-cse-after-loop -frerun-loop-opt -ftree-vectorize Linking : ld -IPA -m elf_x86_64 -Wl,-Bsymbolic-functions