Hi,
> UNIQUE CONSTRAINT
>
> If you removed the unique constraint from OneToOneFields, what would
> you get?
>
> You'd get a ForeignKey.
>
> Therefore removing the 'unique' constraint makes no sense whatsoever.
>
> If you believe you do not want a unique constraint, ask yourself why
> you desire to use OneToOneField and not ForeignKey.
OK, quite possible you're right. Maybe what I needed was a ForeignKey.
> INDEX
>
> I can see why you might decide you don't need an index to optimize
> accesses to the reverse relation.. if you know what you are doing (and
> please note that I have no reason to believe that you do.) Perhaps the
> ForeignKey constructor should check kwargs for a preference first?
Yes, that's was my patch was doing - it checks for passed arguments
(kwargs).
If 'db_index' is there, it uses its value; else it uses default (right
now hardcoded) value.
> > [snip]
> > ALTER TABLE `forum_forumthread` ADD CONSTRAINT
> > forum_id_refs_id_3869a6a3 FOREIGN KEY (`forum_id`) REFERENCES
> > `forum_forum` (`id`);
> > [/snip]
>
> > Such 'foreign key' constraints create indexes in database (at least
> > mysql).
>
> Have you "snipped" the SQL that creates indexes? I see no indexes
> being created here. I see a constraint. Am I missing something?
OK, I've prepared an example - the model is:
---
from django.db import models
class Thread(models.Model):
title = models.CharField(max_length=255)
num_posts = models.PositiveIntegerField(default=0)
first_post = models.ForeignKey('Post', null=True,
db_column='first_post_id', related_name='thread1', db_index=False)
last_post = models.ForeignKey('Post', null=True,
db_column='last_post_id', related_name='thread2', db_index=False)
class Post(models.Model):
thread = models.ForeignKey(Thread, related_name='post_set')
parent = models.ForeignKey('self', null=True,
related_name='children', db_index=False)
content = models.TextField(default='')
ip = models.IPAddressField(null=True)
created = models.DateTimeField(auto_now_add=True)
---
So the model is a simple threaded-comments forum. Each Thread has
Posts. Each Post may have a parent Post.
Now the generated SQL (Mysql 5.0.67) (manage.py sql testapp) is:
---
BEGIN;
CREATE TABLE `testapp_thread` (
`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
`title` varchar(255) NOT NULL,
`num_posts` integer UNSIGNED NOT NULL,
`first_post_id` integer NULL,
`last_post_id` integer NULL
)
;
CREATE TABLE `testapp_post` (
`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
`thread_id` integer NOT NULL,
`parent_id` integer NULL,
`content` longtext NOT NULL,
`ip` char(15) NULL,
`created` datetime NOT NULL
)
;
ALTER TABLE `testapp_post` ADD CONSTRAINT thread_id_refs_id_72557ecb
FOREIGN KEY (`thread_id`) REFERENCES `testapp_thread` (`id`);
ALTER TABLE `testapp_thread` ADD CONSTRAINT
first_post_id_refs_id_7de2ceab FOREIGN KEY (`first_post_id`)
REFERENCES `testapp_post` (`id`);
ALTER TABLE `testapp_thread` ADD CONSTRAINT
last_post_id_refs_id_7de2ceab FOREIGN KEY (`last_post_id`) REFERENCES
`testapp_post` (`id`);
ALTER TABLE `testapp_post` ADD CONSTRAINT parent_id_refs_id_71c701b9
FOREIGN KEY (`parent_id`) REFERENCES `testapp_post` (`id`);
COMMIT;
---
I copied and executed the SQLs and ran the SHOW INDEX FROM commands
for each table:
---
mysql> SHOW INDEX FROM testapp_thread;
+----------------+------------+--------------------------------
+--------------+---------------+-----------+-------------+----------
+--------+------+------------+---------+
| Table | Non_unique | Key_name |
Seq_in_index | Column_name | Collation | Cardinality | Sub_part |
Packed | Null | Index_type | Comment |
+----------------+------------+--------------------------------
+--------------+---------------+-----------+-------------+----------
+--------+------+------------+---------+
| testapp_thread | 0 | PRIMARY
| 1 | id | A | 0 | NULL |
NULL | | BTREE | |
| testapp_thread | 1 | first_post_id_refs_id_7de2ceab
| 1 | first_post_id | A | NULL | NULL |
NULL | YES | BTREE | |
| testapp_thread | 1 | last_post_id_refs_id_7de2ceab
| 1 | last_post_id | A | NULL | NULL |
NULL | YES | BTREE | |
+----------------+------------+--------------------------------
+--------------+---------------+-----------+-------------+----------
+--------+------+------------+---------+
3 rows in set (0,00 sec)
mysql> SHOW INDEX FROM testapp_post;
+--------------+------------+----------------------------
+--------------+-------------+-----------+-------------+----------
+--------+------+------------+---------+
| Table | Non_unique | Key_name |
Seq_in_index | Column_name | Collation | Cardinality | Sub_part |
Packed | Null | Index_type | Comment |
+--------------+------------+----------------------------
+--------------+-------------+-----------+-------------+----------
+--------+------+------------+---------+
| testapp_post | 0 | PRIMARY |
1 | id | A | 0 | NULL | NULL | |
BTREE | |
| testapp_post | 1 | thread_id_refs_id_72557ecb |
1 | thread_id | A | NULL | NULL | NULL | |
BTREE | |
| testapp_post | 1 | parent_id_refs_id_71c701b9 |
1 | parent_id | A | NULL | NULL | NULL | YES |
BTREE | |
+--------------+------------+----------------------------
+--------------+-------------+-----------+-------------+----------
+--------+------+------------+---------+
3 rows in set (0,00 sec)
---
So as you see the tables have indexes.
Now I run `manage.py sqlindexes testapp` and execute it in mysql:
---
BEGIN;
CREATE INDEX `testapp_post_thread_id` ON `testapp_post` (`thread_id`);
COMMIT;
---
I run SHOW INDEX FROM again:
---
mysql> SHOW INDEX FROM testapp_post;
+--------------+------------+----------------------------
+--------------+-------------+-----------+-------------+----------
+--------+------+------------+---------+
| Table | Non_unique | Key_name |
Seq_in_index | Column_name | Collation | Cardinality | Sub_part |
Packed | Null | Index_type | Comment |
+--------------+------------+----------------------------
+--------------+-------------+-----------+-------------+----------
+--------+------+------------+---------+
| testapp_post | 0 | PRIMARY |
1 | id | A | 0 | NULL | NULL | |
BTREE | |
| testapp_post | 1 | parent_id_refs_id_71c701b9 |
1 | parent_id | A | NULL | NULL | NULL | YES |
BTREE | |
| testapp_post | 1 | testapp_post_thread_id |
1 | thread_id | A | NULL | NULL | NULL | |
BTREE | |
+--------------+------------+----------------------------
+--------------+-------------+-----------+-------------+----------
+--------+------+------------+---------+
3 rows in set (0,00 sec)
---
Testapp_thread didn't change; in testapp_post only the NAME of the
third index has changed.
So - the ADD CONSTRAINT commands do create indexes. Besides Mysql
manual states it:
---
index_name represents a foreign key ID. If given, this is ignored if
an index for the foreign key is defined explicitly. Otherwise, if
InnoDB creates an index for the foreign key, it uses index_name for
the index name.
---
http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html
btw. my tables are MyIsam type, so it holds true not only for Innodb.
Best regards,
MS
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django developers" group.
To post to this group, send email to django-developers@googlegroups.com
To unsubscribe from this group, send email to
django-developers+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/django-developers?hl=en
-~----------~----~----~----~------~----~------~--~---
