Kristine Peters wrote: > Does sqlite3 support foreign key constraints with m:n relations?
In your logical model, you have a M:N relation. In the phyiscal database, however, you implement this as a separate table which can be seen as having two 1:N relations, so what you have are two separate foreign keys with their own constraints. > Once I initialize the database and set the pragma for foreign keys, I can > update and/or delete with cascading, but when I add a PROJECT relation and a > WORKS_ON relation that has two keys that act as foreign keys, one to > EMPLOYEE and one to PROJECT, then I cannot change either EMPLOYEE ssn or > PROJECT Pnumber without a foreign_key constraint error. In my test, updates and deletes in the two parent tables cascade just fine. > CREATE TABLE WORKS_ON( > Essn CHAR(9) NOT NULL, > Pno INT NOT NULL, > Hours DECIMAL(3,1) NOT NULL, > PRIMARY KEY(Essn, Pno), > FOREIGN KEY(Essn) REFERENCES EMPLOYEE(Ssn), > FOREIGN KEY(Pno) REFERENCES PROJECT(PNUMBER) > ); I hope in your tests you had "ON UPDATE CASCADE" etc. there? Regards, Clemens _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
