Repository: diesel-rs/diesel (language: rust)

Issue:
SQL UNIQUE with multiple foreign keys
<!--
If you want to report a bug, we added some points below you can fill out. If you want to request a feature, feel free to remove all the irrelevant text. In addition to this [issue tracker](https://github.com/diesel-rs/diesel/issues), you can also talk to Diesel maintainers and users on [Gitter](https://gitter.im/diesel-rs/diesel).
-->

## Setup

### Versions

- **Rust:** 1.49.0
- **Diesel:** 1.4.2
- **Database:** MariaDB
- **Operating System** Linux / Ubuntu

### Feature Flags

- **diesel:** mysql

## Problem Description


### What are you trying to accomplish?
I'm trying to create a schema for an existing database.

### What is the expected output?
A working schema.rs

### What is the actual output?
`Unexpected null for non-null column`

### Are you seeing any additional errors?
No

### Steps to reproduce

Put the following in an up.sql and do a migration:
```
CREATE TABLE tab_key1(
    id bigint NOT NULL auto_increment,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE tab_key2(
    id bigint NOT NULL auto_increment,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE tab_key3(
    id bigint NOT NULL auto_increment,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE tab_key4(
    id bigint NOT NULL auto_increment,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE tab_problem (
  id bigint NOT NULL auto_increment,
  key1 bigint NOT NULL,
  key2 bigint NOT NULL,
  key3 bigint NULL,
  some_value varchar(25) NULL,
  key4 bigint NULL,

  PRIMARY KEY (id),
  UNIQUE (key1, key2),
  CONSTRAINT `key1` FOREIGN KEY (key1) REFERENCES tab_key1(id),
  CONSTRAINT `key2` FOREIGN KEY (key2) REFERENCES tab_key2(id),
  CONSTRAINT `key3` FOREIGN KEY (key3) REFERENCES tab_key3(id),
  CONSTRAINT `key4` FOREIGN KEY (key4) REFERENCES tab_key4(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

```

<!--
Please include as much of your codebase as needed to reproduce the error.  If the relevant files are large, please consider linking to a public repository or a [Gist](https://gist.github.com/).

Please post as much of your database schema as necessary. If you are using `infer_schema!`, you can use `diesel print-schema` and post the relevant parts from that.
-->

I tried to create a minimal example to reproduce this for multiple hours now, but the minimal example always works as expected. The root of the problem is the usage of the 'UNIQUE' keyword with two or more foreign keys.

## Checklist

- [X] I have already looked over the [issue tracker](https://github.com/diesel-rs/diesel/issues) for similar issues.
- [X] This issue can be reproduced on Rust's stable channel. (Your issue will be
  closed if this is not the case)

<!--
Thank you for your submission!  You're helping make Diesel more robust 🎉

We'll try to respond as quickly as possible.
-->



Discussion / hints:
Please provide a corresponding schema (as `CREATE TABLE` statements) to reproduce this, otherwise this is not really actionable for any contributor.
As i said, i spend multiple hours already trying to do that. Will try again later
Ok updated. Today it was easier

Fix patch:
diff --git a/diesel_cli/src/infer_schema_internals/mysql.rs b/diesel_cli/src/infer_schema_internals/mysql.rs
index bce2a1f7754c..0b69762dce72 100644
--- a/diesel_cli/src/infer_schema_internals/mysql.rs
+++ b/diesel_cli/src/infer_schema_internals/mysql.rs
@@ -53,6 +53,7 @@ pub fn load_foreign_key_constraints(
     let constraints = tc::table
         .filter(tc::constraint_type.eq("FOREIGN KEY"))
         .filter(tc::table_schema.eq(schema_name))
+        .filter(kcu::referenced_column_name.is_not_null())
         .inner_join(
             kcu::table.on(tc::constraint_schema
                 .eq(kcu::constraint_schema)


Test patch:
diff --git a/diesel_cli/tests/print_schema.rs b/diesel_cli/tests/print_schema.rs
index 123a8b1ba787..2a9849446dd9 100644
--- a/diesel_cli/tests/print_schema.rs
+++ b/diesel_cli/tests/print_schema.rs
@@ -153,6 +153,11 @@ fn print_schema_with_unmappable_names_and_schema_name() {
     )
 }
 
+#[test]
+fn print_schema_with_seperate_unique_constraint_and_foreign_key() {
+    test_print_schema("print_schema_regression_test_for_2623", vec![])
+}
+
 #[test]
 fn schema_file_is_relative_to_project_root() {
     let p = project("schema_file_is_relative_to_project_root")
diff --git a/diesel_cli/tests/print_schema/print_schema_regression_test_for_2623/diesel.toml b/diesel_cli/tests/print_schema/print_schema_regression_test_for_2623/diesel.toml
new file mode 100644
index 000000000000..f57985adb185
--- /dev/null
+++ b/diesel_cli/tests/print_schema/print_schema_regression_test_for_2623/diesel.toml
@@ -0,0 +1,2 @@
+[print_schema]
+file = "src/schema.rs"
diff --git a/diesel_cli/tests/print_schema/print_schema_regression_test_for_2623/mysql/expected.rs b/diesel_cli/tests/print_schema/print_schema_regression_test_for_2623/mysql/expected.rs
new file mode 100644
index 000000000000..9a9d64e4245c
--- /dev/null
+++ b/diesel_cli/tests/print_schema/print_schema_regression_test_for_2623/mysql/expected.rs
@@ -0,0 +1,21 @@
+// @generated automatically by Diesel CLI.
+
+diesel::table! {
+    tab_key1 (id) {
+        id -> Bigint,
+    }
+}
+
+diesel::table! {
+    tab_problem (id) {
+        id -> Bigint,
+        key1 -> Bigint,
+    }
+}
+
+diesel::joinable!(tab_problem -> tab_key1 (key1));
+
+diesel::allow_tables_to_appear_in_same_query!(
+    tab_key1,
+    tab_problem,
+);
diff --git a/diesel_cli/tests/print_schema/print_schema_regression_test_for_2623/mysql/schema.sql b/diesel_cli/tests/print_schema/print_schema_regression_test_for_2623/mysql/schema.sql
new file mode 100644
index 000000000000..7a849aca1176
--- /dev/null
+++ b/diesel_cli/tests/print_schema/print_schema_regression_test_for_2623/mysql/schema.sql
@@ -0,0 +1,14 @@
+CREATE TABLE tab_key1(
+    id bigint NOT NULL auto_increment,
+    PRIMARY KEY (id)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+
+CREATE TABLE tab_problem (
+  id bigint NOT NULL auto_increment,
+  key1 bigint NOT NULL,
+
+  PRIMARY KEY (id),
+  UNIQUE (key1),
+  CONSTRAINT `key1` FOREIGN KEY (key1) REFERENCES tab_key1(id)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
diff --git a/diesel_cli/tests/print_schema/print_schema_regression_test_for_2623/postgres/expected.rs b/diesel_cli/tests/print_schema/print_schema_regression_test_for_2623/postgres/expected.rs
new file mode 100644
index 000000000000..7de538f2b012
--- /dev/null
+++ b/diesel_cli/tests/print_schema/print_schema_regression_test_for_2623/postgres/expected.rs
@@ -0,0 +1,21 @@
+// @generated automatically by Diesel CLI.
+
+diesel::table! {
+    tab1 (id) {
+        id -> Int8,
+    }
+}
+
+diesel::table! {
+    tab_problem (id) {
+        id -> Int8,
+        key1 -> Int8,
+    }
+}
+
+diesel::joinable!(tab_problem -> tab1 (key1));
+
+diesel::allow_tables_to_appear_in_same_query!(
+    tab1,
+    tab_problem,
+);
diff --git a/diesel_cli/tests/print_schema/print_schema_regression_test_for_2623/postgres/schema.sql b/diesel_cli/tests/print_schema/print_schema_regression_test_for_2623/postgres/schema.sql
new file mode 100644
index 000000000000..4dd7043bb817
--- /dev/null
+++ b/diesel_cli/tests/print_schema/print_schema_regression_test_for_2623/postgres/schema.sql
@@ -0,0 +1,13 @@
+CREATE TABLE tab1(
+    id bigserial NOT NULL,
+    PRIMARY KEY (id)
+);
+
+CREATE TABLE tab_problem (
+  id bigserial NOT NULL,
+  key1 bigint NOT NULL,
+
+  PRIMARY KEY (id),
+  UNIQUE (key1),
+  CONSTRAINT "key1" FOREIGN KEY (key1) REFERENCES tab1(id)
+);
diff --git a/diesel_cli/tests/print_schema/print_schema_regression_test_for_2623/sqlite/expected.rs b/diesel_cli/tests/print_schema/print_schema_regression_test_for_2623/sqlite/expected.rs
new file mode 100644
index 000000000000..09e33a6a0572
--- /dev/null
+++ b/diesel_cli/tests/print_schema/print_schema_regression_test_for_2623/sqlite/expected.rs
@@ -0,0 +1,21 @@
+// @generated automatically by Diesel CLI.
+
+diesel::table! {
+    tab_key1 (id) {
+        id -> Integer,
+    }
+}
+
+diesel::table! {
+    tab_problem (id) {
+        id -> Integer,
+        key1 -> BigInt,
+    }
+}
+
+diesel::joinable!(tab_problem -> tab_key1 (key1));
+
+diesel::allow_tables_to_appear_in_same_query!(
+    tab_key1,
+    tab_problem,
+);
diff --git a/diesel_cli/tests/print_schema/print_schema_regression_test_for_2623/sqlite/schema.sql b/diesel_cli/tests/print_schema/print_schema_regression_test_for_2623/sqlite/schema.sql
new file mode 100644
index 000000000000..8f269c144afa
--- /dev/null
+++ b/diesel_cli/tests/print_schema/print_schema_regression_test_for_2623/sqlite/schema.sql
@@ -0,0 +1,14 @@
+CREATE TABLE tab_key1(
+    id integer NOT NULL,
+    PRIMARY KEY (id)
+);
+
+
+CREATE TABLE tab_problem (
+  id integer NOT NULL,
+  key1 bigint NOT NULL,
+
+  PRIMARY KEY (id),
+  UNIQUE (key1),
+  CONSTRAINT `key1` FOREIGN KEY (key1) REFERENCES tab_key1(id)
+);