Does sqlite3 support foreign key constraints with m:n relations?
I can get cascade update/delete to work with 1:n relations, but not m:n.
Example, this schema works: (yes, it¹s from the Elmasri text; it shows a 1:n
relationship between EMPLOYEE and DEPARTMENT; also the 1:N relationship to
the weak entity DEPENDENT)
CREATE TABLE EMPLOYEE (
Fname VARCHAR(15) NOT NULL,
Minit CHAR,
Lname VARCHAR(15) NOT NULL,
Ssn CHAR(9) NOT NULL,
Bdate DATE,
Address VARCHAR,
Sex CHAR,
Salary DECIMAL(10,2),
Super_Ssn CHAR(9),
Dno INT DEFAULT 99,
CONSTRAINT EMPPK
PRIMARY KEY (Ssn),
CONSTRAINT EMPDEPTFK
FOREIGN KEY (Dno) REFERENCES DEPARTMENT (Dnumber)
ON DELETE SET DEFAULT ON UPDATE CASCADE
);
CREATE TABLE DEPARTMENT(
Dname VARCHAR (15) NOT NULL,
Dnumber INT NOT NULL,
Mgr_Ssn CHAR(9) NOT NULL DEFAULT '888665555',
Mgr_start_date DATE,
CONSTRAINT DEPTPK
PRIMARY KEY (Dnumber),
CONSTRAINT DEPTSK
UNIQUE (Dname)
);
CREATE TABLE DEPENDENT(
Essn CHAR(9) NOT NULL,
Dependent_Name VARCHAR(15) NOT NULL,
Sex CHAR,
Bdate DATE,
Relationship VARCHAR(8),
PRIMARY KEY (Essn, Dependent_Name),
FOREIGN KEY (Essn) REFERENCES EMPLOYEE(Ssn)
ON DELETE CASCADE ON UPDATE CASCADE
);
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. See below for the
two relations.
CREATE TABLE PROJECT(
Pname VARCHAR(15) NOT NULL,
Pnumber INT NOT NULL,
Plocation VARCHAR(15),
Dnum INT NOT NULL,
PRIMARY KEY(Pnumber),
UNIQUE (Pname),
FOREIGN KEY(Dnum) REFERENCES DEPARTMENT(Dnumber)
);
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)
);
Thanks for any help,
Kris Peters
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
