Octavian Rasnita wrote:
A key could force a column to have unique values, but those values
could be null if I remember well, and if there are more null values, I
don't know if that column can be used for a relationship...
That's a valid concern, and initially I shared it. But it appears to
work - if the key value is null, a normal join will not pick any values
from the has_many table.
Let me demonstrate:
CREATE TABLE `a` (
`i` integer(11) NOT NULL DEFAULT '0',
`j` integer(11) DEFAULT '0',
INDEX (`i`),
INDEX (`j`),
PRIMARY KEY (`i`),
UNIQUE `a_j` (`j`)
) Type=InnoDB;
CREATE TABLE `b` (
`id` integer(11) NOT NULL DEFAULT '0',
`j` integer(11) DEFAULT '0',
INDEX (`id`),
INDEX (`j`),
PRIMARY KEY (`id`),
CONSTRAINT `b_fk_j` FOREIGN KEY (`j`) REFERENCES `a` (`j`) ON DELETE
CASCADE ON UPDATE CASCADE
) Type=InnoDB;
select * from a;
+---+------+
| i | j |
+---+------+
| 4 | NULL |
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+---+------+
select * from b;
+----+------+
| id | j |
+----+------+
| 7 | NULL |
| 8 | NULL |
| 9 | NULL |
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 3 |
| 6 | 3 |
+----+------+
select * from a join b on (a.j=b.j);
+---+------+----+------+
| i | j | id | j |
+---+------+----+------+
| 1 | 1 | 1 | 1 |
| 1 | 1 | 2 | 1 |
| 1 | 1 | 3 | 1 |
| 2 | 2 | 4 | 2 |
| 3 | 3 | 5 | 3 |
| 3 | 3 | 6 | 3 |
+---+------+----+------+
Of course, if we are interested in the NULL values of the key, we need
to use "left join"
select * from a left join b on (a.j=b.j);
+---+------+------+------+
| i | j | id | j |
+---+------+------+------+
| 4 | NULL | NULL | NULL |
| 1 | 1 | 1 | 1 |
| 1 | 1 | 2 | 1 |
| 1 | 1 | 3 | 1 |
| 2 | 2 | 4 | 2 |
| 3 | 3 | 5 | 3 |
| 3 | 3 | 6 | 3 |
+---+------+------+------+
Notice how even though several rows in b have NULL as key value, they
still do not appear
in the result of the join. The relationship works.
Note, (and I just checked this), if you declare that the key in a can be
NULL, and the key in b is declared NOT NULL, mysql still creates the
tables.
I checked with postgresql, and the same is valid there (it too allows a
NOT NULL foreign key pointing to a UNIQUE key which can be NULL, and
still returns the expected results after a join).
Best regards,
Matija Grabnar
_______________________________________________
List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
Wiki: http://dbix-class.shadowcatsystems.co.uk/
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/
Searchable Archive: http://www.mail-archive.com/[email protected]/
