Примеры внешнего ключа SQLite

Я не эксперт в sql / sqlite.. предположим, у нас есть две таблицы:

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

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

как будет вставка? правильно мои операции createTable? Я хотел бы иметь: у ребенка может быть больше чем одна собака, у собаки может быть больше детей

РЕДАКТИРОВАТЬ

Что если бы я хотел, чтобы у всех детей и для каждого ребенка был список собак, связанных с этим ребенком?

Источник

3 ответа

Решение

Многие-ко-многим

Для поддержки ребенка, имеющего ноль или более собак, и собаки, принадлежащей нолю или более детей, структура таблицы базы данных должна поддерживать отношение " многие ко многим". Для этого требуется три таблицы:

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. Это по двум связанным причинам:

  1. Вам необходимо знать идентификаторы ребенка и собаки, чтобы вставить их в эту таблицу.
  2. Из-за ограничения внешнего ключа вставка в таблицу child_dog завершится неудачно, если указанный дочерний элемент и / или собака не существует в базе данных или транзакции.

Вот несколько примеров операторов SQL для вставки:

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. Модуль sqlite3 в python предоставляет приятный небольшой ярлык, который избавляет вас от необходимости явно запускать функцию 'last_insert_rowid()'.

# 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()
Источник

Вам нужно иметь три таблицы для этого. Это пример Many-to-Many Отношения.

Child
- ChildID (PK)
- Name

Dog
- DogID   (PK)
- DogName

Child_Dog
- ChildID (FK)   
- DogID   (FK)
Источник 
      CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);
Источник
Другие вопросы по тегам