PostgreSQL、MonetDB和MySQL向现有表中添加外键

Question

当我向已经有数据的表中添加外键时，每个数据库管理系统都做了什么？

他们是否分析列的每个值以确认它是来自引用表主键的值？

或者他们还有其他的优化机制？如果是这样的话，那是什么机制？

Answer 1

我无法确认MonetDB，但在PostgreSQL和MySQL中(很可能也是在MonetDB上)，答案是肯定的，它们将检查每个值，如果引用的表中不存在键，则会引发错误。

注意，引用的列不需要成为被引用表的主键-您可以将任何列作为外键引用到另一个表。

Answer 2

是的，当然，没有强制执行的约束是没有意义的。您可以尝试一下(这是针对Postgres的)：

DROP SCHEMA tmp CASCADE;
CREATE SCHEMA tmp ;
SET search_path=tmp;

CREATE TABLE one
        ( one_id SERIAL NOT NULL PRIMARY KEY
        , name varchar
        );
INSERT INTO one(name)
SELECT 'name_' || gs::text
FROM generate_series(1,10) gs ;

CREATE TABLE two
        ( two_id SERIAL NOT NULL PRIMARY KEY
        , one_id INTEGER -- REFERENCES one(one_id)
        );

INSERT INTO two(one_id)
SELECT one_id
FROM one ;

DELETE FROM one WHERE one_id%5=0;

ALTER TABLE two
        ADD FOREIGN KEY (one_id) REFERENCES one(one_id)
        ;

\d one
\d two

结果：

NOTICE:  drop cascades to 2 other objects
DETAIL:  drop cascades to table tmp.one
drop cascades to table tmp.two
DROP SCHEMA
CREATE SCHEMA
SET
CREATE TABLE
INSERT 0 10
CREATE TABLE
INSERT 0 10
DELETE 2
ERROR:  insert or update on table "two" violates foreign key constraint "two_one_id_fkey"
DETAIL:  Key (one_id)=(5) is not present in table "one".
                                  Table "tmp.one"
 Column |       Type        |                      Modifiers                       
--------+-------------------+------------------------------------------------------
 one_id | integer           | not null default nextval('one_one_id_seq'::regclass)
 name   | character varying | 
Indexes:
    "one_pkey" PRIMARY KEY, btree (one_id)

                             Table "tmp.two"
 Column |  Type   |                      Modifiers                       
--------+---------+------------------------------------------------------
 two_id | integer | not null default nextval('two_two_id_seq'::regclass)
 one_id | integer | 
Indexes:
    "two_pkey" PRIMARY KEY, btree (two_id)

错误消息与实际插入或更新相同。您可以看到，一旦引擎对抗了第一个冲突行，它就会停止工作。