外键如何工作？

问题描述

我以前主要使用MyISAM表，该表不支持外键。查看堆栈溢出时，我没有找到关于外键实际作用的简洁明了的解释。我对连接表最感兴趣，在连接表中您将拥有这样的架构：

I've mostly used MyISAM tables before, which don't support foreign keys. Looking on stack overflow I didn't find a nice, concise explanation of what a foreign key is actually doing. I'm mostly interested in join tables, where you would have a schema like this:

customers
id category_id

products
id category_id 

categories
id

customerproducts
customer_id product_id

如果我在customerproducts上有外键，它将确保只有有效的客户和只有有效的产品才能进入该表，但是如果我尝试将电话类别中的产品添加到指定为仅对复印机感兴趣的客户吗？这会导致违反外键约束吗？

If I have foreign keys on customerproducts, it will ensure that only valid customers and only valid products get into that table, but what about if I try to add a Product from the phones category to a customer earmarked as one only interested in copiers? Will this cause the foreign key constraints to be violated?

推荐答案

您不会有这样的架构-它不代表您感兴趣的事实。 SQL中的某些表。 （在PostgreSQL中测试）首先是客户和产品。

You wouldn't have a schema like that--it doesn't represent the facts you're interested in. Let's sketch out some tables in SQL. (Tested in PostgreSQL) First, customers and products.

-- Customer names aren't unique.
create table customers (
  cust_id integer primary key,
  cust_name varchar(15) not null
);
insert into customers values (1, 'Foo'), (2, 'Bar');

-- Product names are unique.
create table products (
  prod_id integer primary key,
  prod_name varchar(15) not null unique
);
insert into products values 
(150, 'Product 1'), (151, 'Product 2'), (152, 'Product 3');

产品有不同的类别。

create table categories (
  cat_name varchar(15) primary key
);
insert into categories values ('Cable'), ('Networking'), ('Phones');

每种产品可能会出现在多个类别中。

Each product might appear in several categories.

create table product_categories (
  prod_id integer not null references products,
  cat_name varchar(15) not null references categories,
  primary key (prod_id, cat_name)
);

insert into product_categories values 
(150, 'Cable'), (150, 'Networking'), (151, 'Networking'), (152, 'Phones');

客户可能对几种产品感兴趣。

A customer might be interested in several categories of products.

create table customer_category_interests (
  cust_id integer not null references customers,
  cat_name varchar(15) not null references categories,
  primary key (cust_id, cat_name)
);

-- Nobody's interested in phones
insert into customer_category_interests values
(1, 'Cable'), (1, 'Networking'), (2, 'Networking');

客户对首选类别中的每种产品都不感兴趣。请注意重叠的外键约束。

Customers aren't interested in every product in their preferred categories. Note the overlapping foreign key constraints.

create table product_interests (
  cust_id integer not null,
  prod_id integer not null,
  cat_name varchar(15) not null,
  foreign key (cust_id, cat_name) references customer_category_interests,
  foreign key (prod_id, cat_name) references product_categories,
  primary key (cust_id, prod_id, cat_name)
);

insert into product_interests values
(1, 150, 'Cable'), (2, 150, 'Networking');

下一个插入操作将失败，因为客户1对电话不感兴趣。

This next insert will fail, because customer 1 isn't interested in phones.

insert into product_interests values
(1, 152, 'Phones');

ERROR:  insert or update on table "product_interests" violates foreign key constraint "product_interests_cust_id_fkey"
DETAIL:  Key (cust_id, cat_name)=(1, Phones) is not present in table "customer_category_interests".

这篇关于外键如何工作？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！