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

Issue:
sqlite3 foreign keys which don't name columns can't be inferred
<!--
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:** `rustc 1.25.0-nightly (616b66dca 2018-02-02)`
- **Diesel:** `diesel 1.1.0`
- **Database:** `sqlite3 3.16.0 2016-11-04 19:09:39 0e5ffd9123d6d2d2b8f3701e8a73cc98a3a7ff5f`
- **Operating System** macOS 10.12.6 (16G1114)

### Feature Flags

- **diesel:** `sqlite postgres`

## Problem Description

Running `diesel print-schema` fails, with very terse output:

```
$ RUST_BACKTRACE=full RUST_LOG=trace diesel print-schema
Unexpected null for non-null column
```

### Steps to reproduce

```
sqlite> .schema accounts
CREATE TABLE accounts (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  account TEXT NOT NULL,
  data_center_id INTEGER NOT NULL,
  auth_key BLOB NOT NULL,
  UNIQUE (account, data_center_id),
  FOREIGN KEY (data_center_id) REFERENCES data_centers
);
sqlite> .schema data_centers
CREATE TABLE data_centers (
  id INTEGER PRIMARY KEY
);
```

This produces: 

```
sqlite> PRAGMA FOREIGN_KEY_LIST('accounts');
id          seq         table         from            to          on_update   on_delete   match
----------  ----------  ------------  --------------  ----------  ----------  ----------  ----------
0           0           data_centers  data_center_id  -NULL-      NO ACTION   NO ACTION   NONE
```

`to` is `NULL` because `REFERENCES data_centers` doesn't name any columns; it's implicitly the same as `REFERENCES data_centers(id)`. I'm pretty sure this is due to the `pragma_foreign_key_list` pseudo-table doesn't expect a `NULL`.

https://github.com/diesel-rs/diesel/blob/07319d363aff713dbe52914aa2f512431f559864/diesel_infer_schema/infer_schema_internals/src/sqlite.rs#L27-L38

<!--
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.
-->

## Checklist

- [X] I have already looked over the [issue tracker](https://github.com/diesel-rs/diesel/issues) for similar issues.


<!--
Thank you for your submission!  You're helping make Diesel more robust 🎉

We'll try to respond as quickly as possible.
-->



Discussion / hints:
For any postgres user with partitioned tables who stumbles upon this. There is already another issue for that and it seems like it is fixed in newer versions of diesel-cli. https://github.com/diesel-rs/diesel/issues/2817

Fix patch:
diff --git a/diesel_cli/src/infer_schema_internals/sqlite.rs b/diesel_cli/src/infer_schema_internals/sqlite.rs
index 3db89e26a933..144cdd50393f 100644
--- a/diesel_cli/src/infer_schema_internals/sqlite.rs
+++ b/diesel_cli/src/infer_schema_internals/sqlite.rs
@@ -31,7 +31,7 @@ table! {
         seq -> Integer,
         _table -> VarChar,
         from -> VarChar,
-        to -> VarChar,
+        to -> Nullable<VarChar>,
         on_update -> VarChar,
         on_delete -> VarChar,
         _match -> VarChar,
@@ -71,20 +71,37 @@ pub fn load_foreign_key_constraints(
         .into_iter()
         .map(|child_table| {
             let query = format!("PRAGMA FOREIGN_KEY_LIST('{}')", child_table.sql_name);
-            Ok(sql::<pragma_foreign_key_list::SqlType>(&query)
+            sql::<pragma_foreign_key_list::SqlType>(&query)
                 .load::<ForeignKeyListRow>(connection)?
                 .into_iter()
                 .map(|row| {
                     let parent_table = TableName::from_name(row.parent_table);
-                    ForeignKeyConstraint {
+                    let primary_key = if let Some(primary_key) = row.primary_key {
+                        primary_key
+                    } else {
+                        let mut primary_keys = get_primary_keys(connection, &parent_table)?;
+                        if primary_keys.len() == 1 {
+                            primary_keys
+                                .pop()
+                                .expect("There is exactly one primary key in this list")
+                        } else {
+                            return Err(diesel::result::Error::DatabaseError(
+                                diesel::result::DatabaseErrorKind::Unknown,
+                                Box::new(String::from(
+                                    "Found more than one primary key for an implicit reference",
+                                )),
+                            ));
+                        }
+                    };
+                    Ok(ForeignKeyConstraint {
                         child_table: child_table.clone(),
                         parent_table,
                         foreign_key: row.foreign_key.clone(),
                         foreign_key_rust_name: row.foreign_key,
-                        primary_key: row.primary_key,
-                    }
+                        primary_key,
+                    })
                 })
-                .collect())
+                .collect::<Result<_, _>>()
         })
         .collect::<QueryResult<Vec<Vec<_>>>>()?;
     Ok(rows.into_iter().flatten().collect())
@@ -163,7 +180,7 @@ struct ForeignKeyListRow {
     _seq: i32,
     parent_table: String,
     foreign_key: String,
-    primary_key: String,
+    primary_key: Option<String>,
     _on_update: String,
     _on_delete: String,
     _match: String,


Test patch:
diff --git a/diesel_cli/tests/print_schema.rs b/diesel_cli/tests/print_schema.rs
index 9381b84750e5..35026da3986f 100644
--- a/diesel_cli/tests/print_schema.rs
+++ b/diesel_cli/tests/print_schema.rs
@@ -223,6 +223,12 @@ fn print_schema_array_type() {
     test_print_schema("print_schema_array_type", vec![])
 }
 
+#[test]
+#[cfg(feature = "sqlite")]
+fn print_schema_sqlite_implicit_foreign_key_reference() {
+    test_print_schema("print_schema_sqlite_implicit_foreign_key_reference", vec![]);
+}
+
 #[cfg(feature = "sqlite")]
 const BACKEND: &str = "sqlite";
 #[cfg(feature = "postgres")]
diff --git a/diesel_cli/tests/print_schema/print_schema_sqlite_implicit_foreign_key_reference/diesel.toml b/diesel_cli/tests/print_schema/print_schema_sqlite_implicit_foreign_key_reference/diesel.toml
new file mode 100644
index 000000000000..f57985adb185
--- /dev/null
+++ b/diesel_cli/tests/print_schema/print_schema_sqlite_implicit_foreign_key_reference/diesel.toml
@@ -0,0 +1,2 @@
+[print_schema]
+file = "src/schema.rs"
diff --git a/diesel_cli/tests/print_schema/print_schema_sqlite_implicit_foreign_key_reference/sqlite/expected.rs b/diesel_cli/tests/print_schema/print_schema_sqlite_implicit_foreign_key_reference/sqlite/expected.rs
new file mode 100644
index 000000000000..f791e8ac91f7
--- /dev/null
+++ b/diesel_cli/tests/print_schema/print_schema_sqlite_implicit_foreign_key_reference/sqlite/expected.rs
@@ -0,0 +1,24 @@
+// @generated automatically by Diesel CLI.
+
+diesel::table! {
+    accounts (id) {
+        id -> Integer,
+        account -> Text,
+        data_center_id -> Integer,
+        auth_key -> Binary,
+    }
+}
+
+diesel::table! {
+    data_centers (id) {
+        id -> Integer,
+        name -> Text,
+    }
+}
+
+diesel::joinable!(accounts -> data_centers (data_center_id));
+
+diesel::allow_tables_to_appear_in_same_query!(
+    accounts,
+    data_centers,
+);
diff --git a/diesel_cli/tests/print_schema/print_schema_sqlite_implicit_foreign_key_reference/sqlite/schema.sql b/diesel_cli/tests/print_schema/print_schema_sqlite_implicit_foreign_key_reference/sqlite/schema.sql
new file mode 100644
index 000000000000..0680c88cbf80
--- /dev/null
+++ b/diesel_cli/tests/print_schema/print_schema_sqlite_implicit_foreign_key_reference/sqlite/schema.sql
@@ -0,0 +1,13 @@
+CREATE TABLE data_centers (
+   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+   name TEXT NOT NULL
+);
+
+CREATE TABLE accounts (
+  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+  account TEXT NOT NULL,
+  data_center_id INTEGER NOT NULL,
+  auth_key BLOB NOT NULL,
+  UNIQUE (account, data_center_id),
+  FOREIGN KEY (data_center_id) REFERENCES data_centers
+);