Hi,
I just wanted to ask the sql experts here if the design I am thinking
about is ok.
Especially this table looks strange but at least its working. I am using
the same column (t_x_u_id) in two fk constraints. Although it is doing
what I intended I have never seen this and wasn't even sure if it would
work.
I did this to make sure that only certain combinations of p and m are
accepted if p is of a certain use_x_type (t_x_u) combination: They are
defined in m_x_t_x_u.
CREATE TABLE p_x_m_x_t_x_u (
p_id INTEGER NOT NULL,
t_x_u_id INTEGER NOT NULL,
m_id INTEGER NOT NULL,
FOREIGN KEY (p_id, t_x_u_id) REFERENCES p_x_t_x_u (p_id, t_x_u_id),
FOREIGN KEY (t_x_u_id, m_id) REFERENCES m_x_t_x_u (t_x_u_id, m_id)
);
Jan
Don't know if attaching works. I case not here is the full sql
---------------------------------------------------------------
PRAGMA foreign_keys=ON;
BEGIN TRANSACTION;
CREATE TABLE t (
t_id INTEGER PRIMARY KEY AUTOINCREMENT,
t_name TEXT UNIQUE NOT NULL
);
INSERT INTO t VALUES(1,'typ1');
INSERT INTO t VALUES(2,'typ2');
CREATE TABLE u (
u_id INTEGER PRIMARY KEY AUTOINCREMENT,
u_name TEXT UNIQUE NOT NULL
);
INSERT INTO u VALUES(1,'use1');
INSERT INTO u VALUES(2,'use2');
CREATE TABLE t_x_u (
t_x_u_id INTEGER PRIMARY KEY AUTOINCREMENT,
t_id INTEGER NOT NULL CONSTRAINT fk_t_id REFERENCES t (t_id) ON
DELETE RESTRICT ON UPDATE RESTRICT,
u_id INTEGER NOT NULL CONSTRAINT fk_u_id REFERENCES u (u_id) ON
DELETE RESTRICT ON UPDATE RESTRICT
);
INSERT INTO t_x_u VALUES(1,1,1);
INSERT INTO t_x_u VALUES(2,2,2);
CREATE TABLE m (
m_id INTEGER PRIMARY KEY AUTOINCREMENT,
m_name TEXT UNIQUE NOT NULL
);
INSERT INTO m VALUES(1,'material1');
INSERT INTO m VALUES(2,'material2');
CREATE TABLE m_x_t_x_u (
t_x_u_id INTEGER NOT NULL CONSTRAINT fk_t_x_u_id REFERENCES t_x_u
(t_x_u_id) ON DELETE RESTRICT ON UPDATE RESTRICT,
m_id INTEGER NOT NULL CONSTRAINT fk_m_id REFERENCES m (m_id) ON
DELETE RESTRICT ON UPDATE RESTRICT,
PRIMARY KEY (t_x_u_id, m_id)
);
INSERT INTO m_x_t_x_u VALUES(1,1);
INSERT INTO m_x_t_x_u VALUES(1,2);
INSERT INTO m_x_t_x_u VALUES(2,1);
CREATE TABLE p (
p_id INTEGER PRIMARY KEY AUTOINCREMENT,
p_name TEXT UNIQUE NOT NULL
);
INSERT INTO p VALUES(1,'product1');
INSERT INTO p VALUES(2,'product2');
--this table might be redundant. but I want an autoincrement in p.p_id
CREATE TABLE p_x_t_x_u (
p_id INTEGER UNIQUE NOT NULL CONSTRAINT fk_m_id REFERENCES m (m_id)
ON DELETE RESTRICT ON UPDATE RESTRICT,
t_x_u_id INTEGER NOT NULL CONSTRAINT fk_t_x_u_id REFERENCES t_x_u
(t_x_u_id) ON DELETE RESTRICT ON UPDATE RESTRICT,
PRIMARY KEY (p_id, t_x_u_id)
);
INSERT INTO p_x_t_x_u VALUES(1,1);
INSERT INTO p_x_t_x_u VALUES(2,2);
CREATE TABLE p_x_m_x_t_x_u (
p_id INTEGER NOT NULL,
t_x_u_id INTEGER NOT NULL,
m_id INTEGER NOT NULL,
FOREIGN KEY (p_id, t_x_u_id) REFERENCES p_x_t_x_u (p_id, t_x_u_id) ON
DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (t_x_u_id, m_id) REFERENCES m_x_t_x_u (t_x_u_id, m_id) ON
DELETE RESTRICT ON UPDATE RESTRICT
);
COMMIT;
--test ok combos
INSERT INTO p_x_m_x_t_x_u VALUES(1,1,1);
INSERT INTO p_x_m_x_t_x_u VALUES(1,1,2);
INSERT INTO p_x_m_x_t_x_u VALUES(2,2,1);
--test not ok combos
INSERT INTO p_x_m_x_t_x_u VALUES(1,2,1);
INSERT INTO p_x_m_x_t_x_u VALUES(1,2,2);
INSERT INTO p_x_m_x_t_x_u VALUES(2,2,2);
PRAGMA foreign_keys=ON;
BEGIN TRANSACTION;
CREATE TABLE t (
t_id INTEGER PRIMARY KEY AUTOINCREMENT,
t_name TEXT UNIQUE NOT NULL
);
INSERT INTO t VALUES(1,'typ1');
INSERT INTO t VALUES(2,'typ2');
CREATE TABLE u (
u_id INTEGER PRIMARY KEY AUTOINCREMENT,
u_name TEXT UNIQUE NOT NULL
);
INSERT INTO u VALUES(1,'use1');
INSERT INTO u VALUES(2,'use2');
CREATE TABLE t_x_u (
t_x_u_id INTEGER PRIMARY KEY AUTOINCREMENT,
t_id INTEGER NOT NULL CONSTRAINT fk_t_id REFERENCES t (t_id) ON DELETE
RESTRICT ON UPDATE RESTRICT,
u_id INTEGER NOT NULL CONSTRAINT fk_u_id REFERENCES u (u_id) ON DELETE
RESTRICT ON UPDATE RESTRICT
);
INSERT INTO t_x_u VALUES(1,1,1);
INSERT INTO t_x_u VALUES(2,2,2);
CREATE TABLE m (
m_id INTEGER PRIMARY KEY AUTOINCREMENT,
m_name TEXT UNIQUE NOT NULL
);
INSERT INTO m VALUES(1,'material1');
INSERT INTO m VALUES(2,'material2');
CREATE TABLE m_x_t_x_u (
t_x_u_id INTEGER NOT NULL CONSTRAINT fk_t_x_u_id REFERENCES t_x_u (t_x_u_id)
ON DELETE RESTRICT ON UPDATE RESTRICT,
m_id INTEGER NOT NULL CONSTRAINT fk_m_id REFERENCES m (m_id) ON DELETE
RESTRICT ON UPDATE RESTRICT,
PRIMARY KEY (t_x_u_id, m_id)
);
INSERT INTO m_x_t_x_u VALUES(1,1);
INSERT INTO m_x_t_x_u VALUES(1,2);
INSERT INTO m_x_t_x_u VALUES(2,1);
CREATE TABLE p (
p_id INTEGER PRIMARY KEY AUTOINCREMENT,
p_name TEXT UNIQUE NOT NULL
);
INSERT INTO p VALUES(1,'product1');
INSERT INTO p VALUES(2,'product2');
CREATE TABLE p_x_t_x_u (
p_id INTEGER UNIQUE NOT NULL CONSTRAINT fk_m_id REFERENCES m (m_id) ON DELETE
RESTRICT ON UPDATE RESTRICT,
t_x_u_id INTEGER NOT NULL CONSTRAINT fk_t_x_u_id REFERENCES t_x_u (t_x_u_id)
ON DELETE RESTRICT ON UPDATE RESTRICT,
PRIMARY KEY (p_id, t_x_u_id)
);
INSERT INTO p_x_t_x_u VALUES(1,1);
INSERT INTO p_x_t_x_u VALUES(2,2);
CREATE TABLE p_x_m_x_t_x_u (
p_id INTEGER NOT NULL,
t_x_u_id INTEGER NOT NULL,
m_id INTEGER NOT NULL,
FOREIGN KEY (p_id, t_x_u_id) REFERENCES p_x_t_x_u (p_id, t_x_u_id) ON DELETE
CASCADE ON UPDATE CASCADE,
FOREIGN KEY (t_x_u_id, m_id) REFERENCES m_x_t_x_u (t_x_u_id, m_id) ON DELETE
RESTRICT ON UPDATE RESTRICT
);
COMMIT;
--test ok combos
INSERT INTO p_x_m_x_t_x_u VALUES(1,1,1);
INSERT INTO p_x_m_x_t_x_u VALUES(1,1,2);
INSERT INTO p_x_m_x_t_x_u VALUES(2,2,1);
--test not ok combos
INSERT INTO p_x_m_x_t_x_u VALUES(1,2,1);
INSERT INTO p_x_m_x_t_x_u VALUES(1,2,2);
INSERT INTO p_x_m_x_t_x_u VALUES(2,2,2);_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
