Why does Django add constraint when using Foreign Key instead of defining it in Create statement?

Question

I'm going through the Django tutorial and am defining the poll and choice models. When I run manage.py sql polls to see the sql statements, I get:

CREATE TABLE `polls_poll` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `question` varchar(200) NOT NULL,
    `pub_date` datetime NOT NULL
);

CREATE TABLE `polls_choice` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `poll_id` integer NOT NULL,
    `choice_text` varchar(200) NOT NULL,
    `votes` integer NOT NULL
);

ALTER TABLE `polls_choice` ADD CONSTRAINT `poll_id_refs_id_3aa09835`
    FOREIGN KEY (`poll_id`) REFERENCES `polls_poll` (`id`);

Why does Django use an ALTER statement when applying the Foreign Key instead of doing it in the Create table statement? Something like the following seems more succinct:

`poll_id integer NOT NULL REFERENCES polls_choice(id)`

David Wolever · Accepted Answer · 2013-04-09 21:47:05Z

5

To avoid dependency problems and circular references. For example, imagine two tables:

CREATE TABLE parent ( id, first_child_id );
CREATE TABLE child ( id, parent_id );

At least one of the references between parent and child must be added after the tables have been defined.

Now, because MySQL isn't particularly careful with referential integrity, this isn't a problem:

mysql> CREATE TABLE parent ( id INTEGER PRIMARY KEY, first_child_id INTEGER NOT NULL REFERENCES child(id) );
Query OK, 0 rows affected (0.00 sec)

But more pedantic database engines will complain:

test=# BEGIN;
BEGIN
test=# SET CONSTRAINTS ALL DEFERRED;
SET CONSTRAINTS
test=# CREATE TABLE parent (id INTEGER PRIMARY KEY, first_child_id INTEGER NOT NULL REFERENCES child(id));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "parent_pkey" for table "parent"
ERROR:  relation "child" does not exist

edited Apr 9, 2013 at 21:47

answered Apr 9, 2013 at 21:19

David Wolever

156k94 gold badges365 silver badges513 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Cianan Sims Over a year ago

Thank you for the insightful answer! Makes perfect sense.

Collectives™ on Stack Overflow

Why does Django add constraint when using Foreign Key instead of defining it in Create statement?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related