Geek asks and answers

How do foreign keys work?

I used to use MyISAM tables that do not support foreign keys. Looking at the stack overflow, I did not find a beautiful and concise explanation of what the foreign key actually does. What interests me most is the join tables, where you will have such a scheme:

customers
id category_id

products
id category_id 

categories
id

customerproducts
customer_id product_id

If I have foreign keys to customerproducts products, this ensures that only valid customers and only valid products get into this table, but what about if I try to add a product from the phone category to a client designed solely for copy machines? Does this violate external constraints?

+5
source share
1 answer

join, :

- ​​ . SQL. ( PostgreSQL). .

-- 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');

.

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');

.

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 interested in phones
insert into customer_category_interests values
(1, 'Cable'), (1, 'Networking'), (2, 'Networking');

-, , , , , ?

. .

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 .

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".
+1

More articles:


All Articles