Bugs item #1643375, was opened at 2007-01-24 11:32 Message generated for change (Comment added) made by romulog You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=482468&aid=1643375&group_id=56967
Please note that this message will contain a full copy of the comment thread, including the initial issue submission, for this request, not just the latest update. Category: SQL/Core Group: SQL CVS Head Status: Open Resolution: None Priority: 5 Private: No Submitted By: Romulo Goncalves (romulog) Assigned to: Niels Nes (nielsnes) Summary: Update primary keys.... Initial Comment: Dear developers, I create the following tables with some constraints: create table t1(id int, name varchar(1024)); alter table t1 add constraint id_p primary key(id); create table t2(id_f int, age int, foreign key(id_f) references t1(id)); alter table t2 add constraint age_p primary key(age); As you can see t2 has a foreign key (f_id) to the primary key in t1(id). I executed the following queries: [EMAIL PROTECTED] test]$ mclient sql>insert into t1 values(1,'romulo'); [ 1 ] sql>insert into t2 values(1,33); [ 1 ] sql>insert into t2 values(2,33); MAPI = [EMAIL PROTECTED]:50000 QUERY = insert into t2 values(2,33); ERROR = !SQLException:assert:INSERT INTO: PRIMARY KEY constraint 't2.age_p' violated sql> Until here no problem. However... sql>update t1 set id = 0 where id =1; [ 1 ] sql>select * from t1; % sys.t1, sys.t1 # table_name % id, name # name % int, varchar # type % 1, 6 # length [ 0, "romulo" ] sql>select * from t2; % sys.t2, sys.t2 # table_name % id_f, age # name % int, int # type % 1, 2 # length [ 1, 33 ] sql> The update operation against table t1 was possible and it should not. It should return a message error or a warning to use UPDATE CASCADED. Best Regards, Romulo ---------------------------------------------------------------------- >Comment By: Romulo Goncalves (romulog) Date: 2007-01-24 11:44 Message: Logged In: YES user_id=1498628 Originator: YES First, My dependencies do not cover the primary keys and foreign keys. Niels said that I did have to worry because they were already implemented. Well, my example just showed that they are not. Second: Yes, your error system looks nice, but why I do not have it in my system? Regards, Romulo ---------------------------------------------------------------------- Comment By: Fabian (mr-meltdown) Date: 2007-01-24 11:38 Message: Logged In: YES user_id=963970 Originator: NO hehe, this is you were implementing right? At least the dependencies here don't work, as it should bail out: armada=> create table t1(id int, name varchar(1024)); CREATE TABLE armada=> alter table t1 add constraint id_p primary key(id); NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "id_p" for table "t1" ALTER TABLE armada=> create table t2(id_f int, age int, foreign key(id_f) references t1(id)); CREATE TABLE armada=> alter table t2 add constraint age_p primary key(age); NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "age_p" for table "t2" ALTER TABLE armada=> insert into t1 values(1,'romulo'); INSERT 26736 1 armada=> insert into t2 values(1,33); INSERT 26737 1 armada=> insert into t2 values(2,33); ERROR: duplicate key violates unique constraint "age_p" armada=> update t1 set id = 0 where id =1; ERROR: update or delete on "t1" violates foreign key constraint "t2_id_f_fkey" on "t2" DETAIL: Key (id)=(1) is still referenced from table "t2". armada=> (don't you love the detail in which this error message explains you're doing something wrong?) ---------------------------------------------------------------------- You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=482468&aid=1643375&group_id=56967 ------------------------------------------------------------------------- Take Surveys. Earn Cash. Influence the Future of IT Join SourceForge.net's Techsay panel and you'll get the chance to share your opinions on IT & business topics through brief surveys - and earn cash http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV _______________________________________________ Monetdb-bugs mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/monetdb-bugs
