Task :

Table 1:

CREATE TABLE `olap`.`test1` ( `id` INT NOT NULL, `name` VARCHAR(5) NOT NULL, PRIMARY KEY (`id`));

There is a table 2:

  CREATE TABLE `olap`.`test2` ( `id` INT NOT NULL AUTO_INCREMENT, `FKtest2` INT NULL, PRIMARY KEY (`id`), INDEX `id_idx` (`FKtest2` ASC), CONSTRAINT `id` FOREIGN KEY (`FKtest2`) REFERENCES `olap`.`test1` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION);

It is necessary to make it so that in Table 2 one could enter the value 0 in the FKtest2 field, i.e. so that the line from Table 2 is not yet set the line from Table 1

  • one
    Why 0? If there is no connection, write NULL there. - Yaant

1 answer 1

Zero does not work - FK will not allow. But NULL - why not ...

 mysql> CREATE TABLE `test1` ( -> `id` INT NOT NULL, -> `name` VARCHAR(5) NOT NULL, -> PRIMARY KEY (`id`)); Query OK, 0 rows affected (0.35 sec) mysql> INSERT INTO test1 -> SELECT 1,'name1' UNION ALL -> SELECT 2,'name2' ; Query OK, 2 rows affected (0.02 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> CREATE TABLE `test2` ( -> `id` INT NOT NULL AUTO_INCREMENT, -> `FKtest2` INT NULL, -> PRIMARY KEY (`id`), -> INDEX `id_idx` (`FKtest2` ASC), -> CONSTRAINT `id` -> FOREIGN KEY (`FKtest2`) -> REFERENCES `test1` (`id`) -> ON DELETE NO ACTION -> ON UPDATE NO ACTION); Query OK, 0 rows affected (0.42 sec) mysql> INSERT INTO test2 (FKtest2) -> SELECT 1 UNION ALL -> SELECT NULL ; Query OK, 2 rows affected (0.06 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM test1 LEFT JOIN test2 ON test1.id = test2.FKtest2 -> UNION -> SELECT * FROM test1 RIGHT JOIN test2 ON test1.id = test2.FKtest2; +------+-------+------+---------+ | id | name | id | FKtest2 | +------+-------+------+---------+ | 1 | name1 | 1 | 1 | | 2 | name2 | NULL | NULL | | NULL | NULL | 2 | NULL | +------+-------+------+---------+ 3 rows in set (0.02 sec)