[Dbix-class] Re: has_many/belongs_to relationship to non-primary key

[Matija Grabnar]

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