How to register a foreign keys

I am trying to setup a foreign key in this table to reference Customers table column custId but I get the following error:

error 1: no such table: Customers(custId): file

I am not sure how to code the reference showing both the table and column. How do you code this in the following?

 migrator.registerMigration("createInvoice") { db in         
   try db.create(table: "Invoices") { t in
                t.autoIncrementedPrimaryKey("invoiceId")
                t.column("invoiceNumber", .text).notNull()
                t.column("invoiceCustId", .double).notNull()
                t.column("invoiceQuantity", .numeric).notNull()
                t.column("invoiceItemId", .numeric).notNull()
                t.column("invoiceAmount", .numeric).notNull()
                t.foreignKey(["invoiceCustId"], references: "Customers(custId)")

Hello @Eagle442BR,

The references argument accepts a table name, not an SQL snippet. "Customers(custId)" is an SQL snippet, and this explains why you see an error.

If the Customers table is already created when you create the Invoices table, and the primary key of the Customers table is custId, then you can write:

// Creates a foreign key from `Invoices(invoiceCustId)`
// to the primary key of `Customers`.
t.foreignKey(["invoiceCustId"], references: "Customers")

Otherwise, provide an explicit target column for the foreign key:

// Creates a foreign key from `Invoices(invoiceCustId)`
// to `Customers(custId)`.
t.foreignKey(["invoiceCustId"], references: "Customers", columns: ["custId"])

EDIT: I personally prefer to declare the foreign keys directly on the column declarations (for foreign keys that use a single column). For example:

try db.createTable(...) { t in
    ...
    t.column("invoiceCustId", .integer)
        .notNull()
        .indexed()
        .references("Customers", onDelete: .cascade)
}
1 Like

Thanks a bunch! I like they way you did.. directly on the column