带有外键列表的SQLite字段 [英] SQLite field with a list of foreign keys

问题描述

我正在建立一个SQLite数据库，以保持我的房地产经纪人的名单。我已经能够使用外键来识别每个代理的列表，但是我想列出每个代理记录中的列表;从代理和上市之间的一对一关系转变为一对多的关系。

看这里： http://www.sqlite.org/draft/foreignkeys.html ，没有提到一个字段是列表外键。我试图做的是什么？

我认为，作为一个解决方法，我可以做一个包含关系本身的表，但似乎并不非常干净的解决方案。我看了这个线程： SQLite外键示例，但我不确定我的元素类似于他们在那里描述的多对多关系。

我也接受建议如何做得更好。我应该只在实际列表中包含列表代理的名称，并从那里查询？

  CREATE TABLE列表
 （
 listing_id INTEGER PRIMARY KEY AUTOINCREMENT，
 listing_address TEXT UNIQUE NOT NULL，
 acq_date DATE 
）
 
 CREATE TABLE代理
（
 agent_id INTEGER PRIMARY KEY AUTOINCREMENT，
 agent_name TEXT NOT NULL，
 agent_listings INTEGER，
 FOREIGN KEY（agent_listings）REFERENCES listing（listing_id）NOT NULL 
）
  

解决方案

您需要将外键添加到列表表来代替;对于一对多的关系来说，它是记录外键的许多方面。

  CREATE TABLE列表
 $ b listing_id INTEGER PRIMARY KEY AUTOINCREMENT，
 listing_address TEXT UNIQUE NOT NULL，
 acq_date DATE 
 agent_id INTEGER，
 FOREIGN KEY（agent_id）REFERENCES agent（agent_id） 
）
 
 CREATE TABLE代理
（
 agent_id INTEGER PRIMARY KEY AUTOINCREMENT，
 agent_name TEXT NOT NULL，
）
  

如果您希望列表由多个代理处理（处于多对多关系），你必须添加一个链接表：

  CREATE TABLE列表
（
 listing_id INTEGER PRIMARY KEY AUTOINCREMENT，
 listing_address TEXT UNIQUE NOT NULL，
 acq_date DATE 
）
 
 CREATE TABLE代理
（
 agent_id INTEGER PRIMARY KEY AU TOINCREMENT，
 agent_name TEXT NOT NULL，
）
 
 CREATE TABLE agent_listings 
（
 agent_id INTEGER NOT NULL，
 listing_id INTEGER NOT NULL， 
 UNIQUE（agent_id，listing_id），
 FOREIGN KEY（agent_id）REFERENCES agent（agent_id），
 FOREIGN KEY（listing_id）REFERENCES listing（listing_id），
）
  

I am building an SQLite DB to keep my real estate agents' listings. I have been able to use a foreign key to identify a listing for each agent, but I want to make a list of listings in each agent's record; moving from a one-to-one relationship between agents and listings to a one-to-many relationship.

Looking here: http://www.sqlite.org/draft/foreignkeys.html , there is no mention of a field that is a 'list of foreign keys'. Is what I'm trying doable?

I considered that, as a workaround, I might make a table that contains the relationships themselves, but that doesn't seem to be a very clean solution. I looked at this thread: SQLite foreign key examples , but I'm not sure that my elements are similar to the many-to-many relationship they describe there.

I am also open to suggestions on how this can be done better. Should I just include a listing agent's name on the actual listing table and just query from there?

CREATE TABLE listings
(
    listing_id        INTEGER PRIMARY KEY AUTOINCREMENT,
    listing_address   TEXT UNIQUE NOT NULL,
    acq_date          DATE
 )

CREATE TABLE agent
(
     agent_id         INTEGER PRIMARY KEY AUTOINCREMENT,
     agent_name       TEXT NOT NULL,
     agent_listings   INTEGER,
     FOREIGN KEY (agent_listings) REFERENCES listings (listing_id) NOT NULL
 )

解决方案

You need to add the foreign key to the listings table instead; for a one-to-many relationship it is the 'many' side that records the foreign key.

CREATE TABLE listings
(
    listing_id        INTEGER PRIMARY KEY AUTOINCREMENT,
    listing_address   TEXT UNIQUE NOT NULL,
    acq_date          DATE
    agent_id          INTEGER,
    FOREIGN KEY (agent_id) REFERENCES agent (agent_id)
)

CREATE TABLE agent
(
     agent_id         INTEGER PRIMARY KEY AUTOINCREMENT,
     agent_name       TEXT NOT NULL,
)

If you wanted listings to be handled by multiple agents (in a many-to-many relationship), you'll have to add a linking table:

CREATE TABLE listings
(
    listing_id        INTEGER PRIMARY KEY AUTOINCREMENT,
    listing_address   TEXT UNIQUE NOT NULL,
    acq_date          DATE
)

CREATE TABLE agent
(
    agent_id          INTEGER PRIMARY KEY AUTOINCREMENT,
    agent_name        TEXT NOT NULL,
)

CREATE TABLE agent_listings
(
    agent_id         INTEGER NOT NULL,
    listing_id       INTEGER NOT NULL,
    UNIQUE (agent_id, listing_id),
    FOREIGN KEY (agent_id) REFERENCES agent (agent_id),
    FOREIGN KEY (listing_id) REFERENCES listings (listing_id),
)

这篇关于带有外键列表的SQLite字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！