Hi Stefan, as column c3 is null, I think the constraint fk3 should NOT trigger the error. The foreign key being partly null, the constraint does not apply. At least this is how Postgresql and Oracle behave... Franck Le lundi 19 avril 2010 à 17:25 +0200, Stefan Manegold a écrit :
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
-- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4199 |
------------------------------------------------------------------------------ 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