制作受“引用”约束的 varchar 数组；在 SQL 中

Question

想象

CREATE TABLE titles (
  bookTitle varchar(80)
);

CREATE TABLE borrowers (
  name varchar(80), --borrower's name
  checkedOut varchar(80)[] references titles(bookTitle) --list of checked out
);

一下，当然这不起作用，但它（希望）能够让人类读者明白我想要的东西：我希望借阅者在一列中有一个数组（因为一次可以签出多个标题）并且我想要确保只有标题表中的标题才可能出现在借阅者的已检出标题列表中。这个的语法是什么？

Answer 1

为什么不引入第三个表并将该借款人姓名的所有签出信息存储在其中？在我看来，将数组强制放入数据库字段就像苹果和橘子一样。

CREATE TABLE titles (
  bookTitle varchar(80)
);

CREATE TABLE borrowers (
  name varchar(80) --borrower's name
);

CREATE TABLE checkedout (
  name      varchar(80),
  bookTitle varchar(80)
);

Answer 2

通过存储数组，您可以说违反了原子性原则，因此违反了第一个法线形式。为什么不创建一个“正确的”关系数据模型呢？

如果一本书一次只能被一个人借阅，那么您的数据模型中需要一个 N:1 关系，这可以通过一个简单的 FOREIGN KEY 来实现：

（如果标题当前未被任何人借用，TITLE.BORROWED_BY 可以设置为 NULL。）

如果一本书可以同时被多人借阅（无论这意味着什么），您的模型将需要 M:N 关系，可以通过中间的附加“链接”表来建模：

Answer 3

你不能（直接）。关系数据库没有数组；它们有表（关系）以及它们之间的关系。因此，SQL 没有数组的概念。

你可以这样做：

create table foo
(
  id int not null primary key ,
  col_01 varchar(200) null ,
  col_02 varchar(200) null ,
  ...
  col_nn varchar(200) null ,
)

但这是非正常，违反了第一范式：它有重复的组。

你想要的模式是这样的

create table book
(
  id          int         not null primary key , -- primary key
  isbn        varchar(32)     null ,
  title varchar(80) not null , -- can't use title as a key as titles are not unique
)

-- you might have multiple copies of the same book
create table book_copy
(
  book_id     int not null ,
  copy_number int not null ,

  primary key ( book_id , copy_number ) ,

  foreign key ( book_id ) references book(id) ,

)

create table customer
(
  id      int         not null primary key ,
  surname varchar(32) not null ,
  name    varchar(32) not null ,
)

create table customer_has_book
(
  customer_id int not null ,
  book_id     int not null ,
  copy_number int not null ,

  primary key ( customer_id , book_id , copy_number ) , -- customer+book+copy number is unique

  unique ( book_id , copy_number ) , -- a given copy may only be borrowed one at a time      

  foreign key ( customer_id ) references customer(id) ,
  foreign key ( book_id , copy_number) references book_copy(book_id,copy_number) ,

)