CODEWITHSUNDEEP
mysqlsqldjango
Cianan Sims
Cianan Sims

Reputation: 3429

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

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)`

Upvotes: 4

Views: 574

Answers (1)

David Wolever
David Wolever

Reputation: 154644

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

Upvotes: 5

Related Questions