如何在SQLITE3中创建一对多关系?我有两个表:
Mans:
_id name
1 antony
2 fred
and point
_id date point
1 23 77
24 99
2 25 78
5 0
我不懂SQL语法,请帮忙。
如何在SQLITE3中创建一对多关系?我有两个表:
Mans:
_id name
1 antony
2 fred
and point
_id date point
1 23 77
24 99
2 25 78
5 0
我不懂SQL语法,请帮忙。
CREATE TABLE (points) points_id INT
FOREIGN KEY(man_id) REFERENCES mans(PrimaryKeyField)
ON DELETE CASCADE ON UPDATE CASCADE
CREATE TABLE referal(
referal_id INTEGER UNIQUE NOT NULL PRIMARY KEY, //A customer can only be 1 referal.
referal_method TEXT, //How were they refered? By phone?
referer_id INTEGER , //Who refered them?
FOREIGN KEY(referer_id) REFERENCES referer(referer_id)); //Trace more about referer.
ON UPDATE CASCADE ON DELETE CASCADE
不属于我的答案,因为删除最后一个推荐人不应删除引荐人。CREATE TABLE all_candy
(candy_num SERIAL PRIMARY KEY,
candy_maker CHAR(25));
CREATE TABLE hard_candy
(candy_num INT,
candy_flavor CHAR(20),
FOREIGN KEY (candy_num) REFERENCES all_candy
ON DELETE CASCADE)
hard_candy
)中删除一颗硬糖,那么也会从所有糖果表(all_candy
)中删除它,因为硬糖是糖果的一种,如果糖果类型已更改(例如停产的糖果),则无论如何都需要执行新的INSERT命令。BEGIN TRANSACTION;
CREATE TABLE candy(id integer primary key not null, name text, description text);
INSERT INTO candy VALUES(1,'Laffy Taffy', 'Delicious, soft candy.');
INSERT INTO candy VALUES(2,'Pop Rocks', 'A candy that explodes in your mouth.');
COMMIT;
BEGIN TRANSACTION;
CREATE TABLE hard_candy(id integer primary key not null, name text, description text, foreign key(id,name,description) references hard_candy ON DELETE CASCADE ON UPDATE CASCADE);
INSERT INTO hard_candy VALUES(2,'Pop Rocks', 'A candy that explodes in your mouth.');
COMMIT;
对id为2的表格的描述字段进行了各种更新。
CREATE TABLE (MySecondTable) Foo INT FOREIGN KEY(Foo) REFERENCES MyFirstTable(PrimaryKeyField) ON DELETE CASCASDE ON UPDATE CASCASDE
仅适用于v3.6.1及以上版本