Question

sqlite怎么给原有的表增加外键

Answer 1

-- 创建测试主表. ID 是主键.
CREATE TABLE test_main (
id INT NOT NULL,
value VARCHAR(10),
PRIMARY KEY(id)
);
-- 插入测试主表数据.
INSERT INTO test_main(id, value) VALUES (1, 'ONE');
INSERT INTO test_main(id, value) VALUES (2, 'TWO');
-- 创建测试子表. ( 注意， 这里要有一个 ON DELETE CASCADE )
CREATE TABLE test_sub (
id INT PRIMARY KEY,
main_id INT REFERENCES test_main(id) ON DELETE CASCADE,
value VARCHAR(10)
);
-- 插入测试子表数据.
INSERT INTO test_sub(id, main_id, value) VALUES (1, 1, 'ONEONE');
INSERT INTO test_sub(id, main_id, value) VALUES (2, 2, 'TWOTWO');
-- 测试删除主表.
sqlite> DELETE FROM test_main WHERE id = 1;

-- 查看 子表数据是否被删除.
sqlite> select * from test_sub;
2|2|TWOTWO