PRAGMA foreign_keys=on;

CREATE TABLE IF NOT EXISTS signals (
    id INTEGER PRIMARY KEY,
	signal	TEXT NOT NULL,
	R1	INTEGER NOT NULL DEFAULT 0,
	R2	INTEGER NOT NULL DEFAULT 0,
	R3	INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS sig_pages (
    id INTEGER PRIMARY KEY,
	sig_index	INTEGER,
	v_signal_value	TEXT NOT NULL,
FOREIGN KEY (sig_index) REFERENCES signals(id)
);

CREATE TABLE IF NOT EXISTS sig_pages_R (
    id INTEGER PRIMARY KEY,
	v_sig_index	INTEGER NOT NULL,
	v_sig_root	TEXT NOT NULL,
	root_R	INTEGER NOT NULL,
FOREIGN KEY (v_sig_index) REFERENCES sig_pages(id)
);

INSERT INTO signals (signal, R1) values ("собака", 5 );
INSERT INTO signals (signal, R1) values ("кошка", 1 );
INSERT INTO signals (signal, R1) values ("собачонка", 3 );
INSERT INTO signals (signal, R1) values ("муха", 1 );

INSERT INTO sig_pages (sig_index, v_signal_value) values (2, "сидит на окне");
INSERT INTO sig_pages_R (v_sig_index, v_sig_root, root_R) values (1, "А", 2);

DELETE FROM signals
WHERE signals.id IN
(SELECT signals.id
FROM signals LEFT OUTER JOIN sig_pages ON sig_pages.sig_index = signals.id
LEFT OUTER JOIN sig_pages_R ON sig_pages_R.v_sig_index = sig_pages.id
WHERE
signals.R1 = 1)