gistfile1.sql

CREATE TABLE IF NOT EXISTS openclipart_issues(
  clipart INTEGER NOT NULL
  , reporter INTEGER DEFAULT NULL
  , PRIMARY KEY(clipart, reporter)
  , FOREIGN KEY(reporter) REFERENCES openclipart_users(id)
  , FOREIGN KEY(clipart) REFERENCES openclipart_clipart(id)
) CHARACTER SET utf8 COLLATE utf8_general_ci ENGINE=InnoDB;

INSERT INTO openclipart_issues(clipart, reporter)
  SELECT id, NULL
  FROM ocal_files
  WHERE upload_tags LIKE '%clipart_issue%';


-- ERROR 1452 (23000) at line 366: Cannot add or update a child row: a foreign key constraint fails (`ocal`.`openclipart_issues`, CONSTRAINT `openclipart_issues_ibfk_1` FOREIGN KEY (`reporter`) REFERENCES `openclipart_users` (`id`))

MariaDB [vicapow]> CREATE TABLE users ( id INT, UNIQUE (id) );
Query OK, 0 rows affected (0.02 sec)

MariaDB [vicapow]> INSERT INTO users ( id ) VALUES ( 1 );
Query OK, 1 row affected (0.01 sec)

MariaDB [vicapow]> CREATE TABLE issues ( clipart INT NOT NULL, reporter INT DEFAULT NULL, PRIMARY KEY (clipart, reporter), FOREIGN KEY (reporter) REFERENCES users (id) ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.00 sec)

MariaDB [vicapow]> INSERT INTO issues ( clipart, reporter ) VALUES ( 1, 1 );
Query OK, 1 row affected (0.00 sec)

MariaDB [vicapow]> INSERT INTO issues ( clipart, reporter ) VALUES ( 1, NULL );
ERROR 1048 (23000): Column 'reporter' cannot be null
MariaDB [vicapow]> DROP TABLE issues;
Query OK, 0 rows affected (0.01 sec)

MariaDB [vicapow]> CREATE TABLE issues ( clipart INT NOT NULL, reporter INT DEFAULT NULL, UNIQUE KEY (clipart, reporter), FOREIGN KEY (reporter) REFERENCES users (id) ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

MariaDB [vicapow]> INSERT INTO issues ( clipart, reporter ) VALUES ( 1, 1 );
Query OK, 1 row affected (0.00 sec)

MariaDB [vicapow]> INSERT INTO issues ( clipart, reporter ) VALUES ( 1, NULL );
Query OK, 1 row affected (0.00 sec)

MariaDB [vicapow]> INSERT INTO issues ( clipart, reporter ) VALUES ( 1, NULL );
Query OK, 1 row affected (0.00 sec)

MariaDB [vicapow]>

Cannot add or update a child row: a foreign key constraint fails (ocal.openclipart_issues, CONSTRAINT openclipart_issues_ibfk_1 FOREIGN KEY (clipart) REFERENCES openclipart_clipart (id))