Hi,
I can reproduce the behavior with the Feb2010 release branch.
IMHO, the error message with copy into is indeed correct. Since table b is empty, c.fk3 is violated by any addition of data to table c.
Hence, it is IMHO a bug that the insert into does not seem to check the foreign key condition properly.
Could you please file a bug report with all information to repoduce the problem?
As additional information, here are the respective paln that might give some indication (mainly to Niels ;-)), why the insert into might not check the foreign key constraint properly (in this case):
sql>\fraw sql>plan insert into c values ('cc1','aaa',null); % .plan # table_name % rel # name % clob # type % 70 # length insert( | table(sys.c) [ c.c1 NOT NULL, c.c2 NOT NULL, c.c3, c.%TID% NOT NULL ] | [ convert('cc1') as L13, convert('aaa') as L14, 'NULL' as L15 ] ) sql>plan COPY INTO c FROM '/home/mat/test.txt' USING DELIMITERS '|','\n' NULL as ''; % .plan # table_name % rel # name % clob # type % 70 # length insert( | table(sys.c) [ c.c1 NOT NULL, c.c2 NOT NULL, c.c3, c.%TID% NOT NULL ] | table [ c.c1 NOT NULL, c.c2 NOT NULL, c.c3 ] )
Stefan
On Mon, Apr 19, 2010 at 02:08:55PM +0200, Matthieu Guamis wrote:
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
Download Intel® Parallel Studio Eval Try the new software tools for yourself. Speed compiling, find bugs proactively, and fine-tune applications for parallel performance. See why Intel Parallel Studio got high marks during beta. http://p.sf.net/sfu/intel-sw-dev
MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users