如何使用复合主键设置SQLite数据库级联？ [英] How to set cascade on SQLite database with compound primary foreign key?

问题描述

 事件
我有一个超类型表和一个子类型表。 PatientId INTEGER，
 DateTime TEXT，
 EventTypeCode TEXT，
 PRIMARY KEY（PatientId，DateTime，EventTypeCode）
  

不同类型的事件都有自己的表，并且具有相同的主键，除了它是外来的。

 练习b $ b PatientId INTEGER，
 DateTime TEXT，
 EventTypeCode TEXT，
 PRIMARY KEY（PatientId，DateTime，EventTypeCode）ON CONFLICT IGNORE，
 CONSTRAINTPrimaryKey FOREIGN KEY（PatientId，EventTypeCode，DateTime）参考Event（PatientId，EventTypeCode，DateTime）ON DELETE CASCADE ON UPDATE CASCADE 
  / pre> 
 
 
当我试图删除Event中的一个条目时，我得到一个外键不匹配，当我在练习中删除它时，它会删除，但是在练习中，它不会级联。我需要做些什么才能使级联正常工作？我宁愿删除事件中的条目，并将其级联到练习..从我看到的例子，似乎应该如何工作... 
解决方案
这是因为你已经设置了一个无效的外键。尽管SQLite允许你设置它，但试图强制执行时会出错。
 
 
 
 
 
 
在外键约束中命名的父键列不是父表的主键，并且不受使用CREATE TABLE中指定的整理序列的唯一约束
 
 
外键应该针对唯一的目标进行设置。 PatientId引用Event（PatientID）列是无效的，因为表Event中的PatientID不是唯一的。 
 
 $ h $>编辑 
 
 
外键支持仅适用于SQLite 3.6.19或以上。根据您使用的工具，您还需要明确地启用 pragma foreign_keys   。例如，对于Firefox的SQLite管理器插件，请参阅 http：//代码。 google.com/p/sqlite-manager/wiki/ForeignKeys  
 
 
这对我有用
 
  小心！ 
  drop table if exists event; 
 drop table如果存在练习; 
 
 create table EVENT（
 PatientId INTEGER，
 DateTime TEXT，
 EventTypeCode TEXT，
 PRIMARY KEY（PatientId，DateTime，EventTypeCode））; 
 create table EXERCISE（
 PatientId INTEGER，
 DateTime TEXT，
 EventTypeCode TEXT，
 PRIMARY KEY（PatientId，DateTime，EventTypeCode）ON CONFLICT IGNORE，
 CONSTRAINTPrimaryKeyFOREIGN KEY（PatientId，EventTypeCode，DateTime）参考事件（PatientId，EventTypeCode，DateTime）ON DELETE CASCADE ON UPDATE CASCADE）; 
插入事件（patientid，datetime，eventtypecode）值（1,2,3）; 
插入Event（patientid，datetime，eventtypecode）值（4,5,6）; 
插入Event（patientid，datetime，eventtypecode）值（7,9,8）; 
插入到练习（patientid，datetime，eventtypecode）值（1,2,3）; 
插入练习（patientid，datetime，eventtypecode）值（7,9,8）; 
 
从patientid = 1的事件中删除; 
  
 
I have a db that's structured with a supertype table as well as subtype tables like so:
EVENT
PatientId INTEGER,
DateTime TEXT,
EventTypeCode TEXT,
PRIMARY KEY( PatientId, DateTime, EventTypeCode )
different types of events have their own tables and have the same primary key, except that it's foreign.
EXERCISE
PatientId INTEGER,
DateTime TEXT,
EventTypeCode TEXT,
PRIMARY KEY( PatientId, DateTime, EventTypeCode ) ON CONFLICT IGNORE,
CONSTRAINT "PrimaryKey" FOREIGN KEY ("PatientId", "EventTypeCode", "DateTime") REFERENCES "Event" ("PatientId", "EventTypeCode", "DateTime") ON DELETE CASCADE ON UPDATE CASCADE
When I try to delete an entry in Event, I get a foreign key mismatch and when I delete it in Exercise it deletes, but only in Exercise.. it does not cascade.  What do I need to do to get the cascade to work properly? I would prefer to delete the entry in Event and have it cascade to Exercise.. which from the examples I've seen seems like how it should work...
解决方案
It is because you have set up an invalid foreign key.  Even though SQLite allows you to set it up, it will error out when trying to enforce it.

http://www.sqlite.org/foreignkeys.html#fk_indexes

  
The parent key columns named in the foreign key constraint are not the primary key of the parent table and are not subject to a unique constraint using collating sequence specified in the CREATE TABLE
Foreign keys should be set up against a unique target. The column PatientId references Event(PatientID) is not valid, because PatientID alone in table Event is not unique.


EDIT

Foreign key support is only available on SQLite 3.6.19 or above.  Depending on what tool you use, you also need to enable pragma foreign_keys explicitly.  For SQLite Manager add-on for Firefox for example, see here http://code.google.com/p/sqlite-manager/wiki/ForeignKeys

This works for me

Caution!! don't drop your existing tables if they contain anything important. Try on a new db
drop table if exists event;
drop table if exists exercise;

create table EVENT (
PatientId INTEGER,
DateTime TEXT,
EventTypeCode TEXT,
PRIMARY KEY( PatientId, DateTime, EventTypeCode ));
create table EXERCISE(
PatientId INTEGER,
DateTime TEXT,
EventTypeCode TEXT,
PRIMARY KEY( PatientId, DateTime, EventTypeCode ) ON CONFLICT IGNORE,
CONSTRAINT "PrimaryKey" FOREIGN KEY ("PatientId", "EventTypeCode", "DateTime") REFERENCES "Event" ("PatientId", "EventTypeCode", "DateTime") ON DELETE CASCADE ON UPDATE CASCADE);
insert into Event (patientid, datetime, eventtypecode) values (1,2,3);
insert into Event (patientid, datetime, eventtypecode) values (4,5,6);
insert into Event (patientid, datetime, eventtypecode) values (7,9,8);
insert into Exercise (patientid, datetime, eventtypecode) values (1,2,3);
insert into Exercise (patientid, datetime, eventtypecode) values (7,9,8);

delete from event where patientid=1;


                        
这篇关于如何使用复合主键设置SQLite数据库级联？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！