SQLite 外键示例

2022-01-09 00:00:00 python sql insert foreign-keys sqlite

我不是 sql/sqlite 方面的专家..假设我们有两个表:

I am not an expert in sql / sqlite.. suppose we have two tables:

CREATE TABLE child (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
);

CREATE TABLE MyTableB(
  dog TEXT, 
  FOREIGN KEY(dogList) REFERENCES child(id)
);

将如何插入?我的 createTable 操作是否正确?我想拥有:一个孩子可以养不止一只狗狗可以生更多的孩子

how will the INSERT? is correct my createTable operations? I would like to have: a child can have more than one dog a dog can have more children

如果我想要所有孩子并为每个孩子提供与该孩子相关的狗的列表，该怎么办?

推荐答案

多对多

为了支持一个孩子有零个或多个狗和一个狗属于零个或多个孩子，您的数据库表结构需要支持多对多关系.这需要三个表:

CREATE TABLE child (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT
);


CREATE TABLE dog (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    dog TEXT
);

CREATE TABLE child_dog {
    child_id INTEGER,
    dog_id INTEGER,
    FOREIGN KEY(child_id) REFERENCES child(id),
    FOREIGN KEY(dog_id) REFERENCES dog(id)
};

如何插入

对三个表中的每一个的插入必须是单独的 SQL 语句，但可以发生在同一事务的上下文中.插入 child_dog 表(称为映射表)必须在插入 child 和 dog 表之后进行.这有两个相关的原因:

How to Insert

An insert into each of the three tables must be separate SQL statements, but can take place in the context of same transaction. Inserts into the child_dog table (known as the mapping table) must happen after inserts into the child and dog tables. This is for two related reasons:

  1. 您需要知道孩子和狗的标识符才能插入到这个表中.
  2. 由于外键约束，如果引用的子项和/或狗在数据库或事务中不存在，则插入 child_dog 表将失败.

以下是插入的一些示例 SQL 语句:

Here are some example SQL statements for insert:

INSERT INTO child VALUES(NULL, 'bobby');
SELECT last_insert_rowid(); -- gives the id of bobby, assume 2 for this example
INSERT INTO dog VALUES(NULL, 'spot');
SELECT last_insert_rowid(); -- gives the id of spot, assume 4 for this example
INSERT INTO child_dog VALUES(2, 4);

在 Python 中插入

虽然你的问题没有提到 python，但这个问题有一个 python 标签，所以我假设你想知道如何在 python 中做到这一点.python 中的 sqlite3 模块提供了一个不错的小快捷方式，使您不必显式运行 'last_insert_rowid()' 函数.

Inserting In Python

Although your question did not mention python, there is a python tag on this question so I'll assume you want to know how to do this in python. The sqlite3 module in python provides a nice little shortcut which saves you from having to run the 'last_insert_rowid()' function explicitly.

# Import the sqlite3 module
import sqlite3
# Create a connection and cursor to your database
conn = sqlite3.connect('example.db')
c = conn.cursor()
# Insert bobby
c.execute("""INSERT INTO child VALUES(NULL, 'bobby')""")
# The python module puts the last row id inserted into a variable on the cursor
bobby_id = c.lastrowid
# Insert spot
c.execute("""INSERT INTO dog VALUES(NULL, 'spot')""")
spot_id = c.lastrowid
# Insert the mapping
c.execute("""INSERT INTO child_dog VALUES(?, ?)""", (bobby_id, spot_id));
# Commit
conn.commit()
conn.close()

相关文章