PostgreSQL, MonetDB and MySQL add foreign key to existing table

Question

When I add a foreign key to a table that already has data, what does each of these database management systems do?

Do they analyze each value of the column to confirm it is a value from the referenced table primary key ?

Or do they have some other optimized mechanism ? And if that's the case, what is that mechanism ?

score 0 · Accepted Answer · answered Mar 18 '18 at 00:47

0

I can't confirm for MonetDB, but in PostgreSQL and MySQL (and most probably on MonetDB too) the answer is yes, they will check every value and will raise an error if the key does not exists on the referenced table.

Notice that the referenced column doesnt need to be the primary key for the referenced table - you can reference any column as a foreign key to the other table.

answered Mar 18 '18 at 00:47

Dionei Miodutzki

657
7
16

And do any of them use the primary key index in that process of checking every foreign key value ? – João Amorim Mar 18 '18 at 01:07
No.. data is validated before the index creation. – Dionei Miodutzki Mar 18 '18 at 01:11
But in my case, what I needed to do was to create the tables, insert some data, create the primary keys and only after that, create the foreign keys and because of that I suppose there's already an index created for the primary key... that's why I was asking if these DMS use that index in the process of checking every foreign key value – João Amorim Mar 18 '18 at 01:15
Yes, data in the referenced table will be checked using the primary key index, probably (it's a planner decision, but its pretty probable). – Dionei Miodutzki Mar 18 '18 at 01:23

score 0 · Answer 2 · answered Mar 18 '18 at 15:13

Yes, of course, a constraint that is not enforced would make no sense. You can just try(this is for 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

Result:

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)

The error message is the same as for an actual insert or update. And you can see that the engine bails out once it en counters the first conflicting row.

PostgreSQL, MonetDB and MySQL add foreign key to existing table

2 Answers2