Thanks for the explanation Jeremy, that makes more sense now. I guess what caught me by surprise was that setter had this additional logic of clearing FK on the old row instead of just trying to insert into the DB, but it makes sense to have it given the scenario you outlined. Thanks again!
On Sunday, 14 February 2021 at 15:02:36 UTC+8 Jeremy Evans wrote: > On Sat, Feb 13, 2021 at 8:16 PM Dawid Janczak <[email protected]> wrote: > >> Sure thing, >> >> *DB setup* >> >> require 'sequel' >> DB = Sequel.postgres(database: 'sequel_test', host: 'localhost', user: >> 'postgres') >> >> *Migration* >> >> Sequel.migration do >> change do >> create_table(:orders) do >> primary_key :id >> end >> >> create_table(:payments) do >> primary_key :id >> foreign_key :order_id, :orders, null: false, index: { unique: true } >> end >> end >> end >> >> *Example* >> >> require './db' >> >> class Payment < Sequel::Model >> end >> >> class Order < Sequel::Model >> one_to_one :payment >> end >> >> ##### >> # Example 1: Assigning an already existing one_to_one relation using the >> setter causes (incorrect in my opinion) not null error on FK >> ##### >> >> o = Order.create >> o.payment = Payment.new >> >> # Following line fails with ERROR: null value in column "order_id" of >> relation "payments" violates not-null constraint (PG::NotNullViolation) >> DETAIL: Failing row contains (1, null). >> o.payment = Payment.new >> >> # The above generates the following logs in Postgres: >> # Feb 11 13:32:24 hp postgres[156074]: 2021-02-11 13:32:24.769 CST >> [156074] ERROR: null value in column "order_id" of relation "payments" >> violates not-null constraint >> # Feb 11 13:32:24 hp postgres[156074]: 2021-02-11 13:32:24.769 CST >> [156074] DETAIL: Failing row contains (7, null). >> # Feb 11 13:32:24 hp postgres[156074]: 2021-02-11 13:32:24.769 CST >> [156074] STATEMENT: UPDATE "payments" SET "order_id" = NULL WHERE >> ("order_id" = 6) >> > > This is expected. In a one_to_one association, when you change the > currently associated object, it sets the foreign key value of the existing > associated object to NULL, before setting the foreign key value of the new > associated object to the primary key value of the receiver.. > > Let's say it didn't do that. Then both rows in the payments table would > have the same order id. If you did order.refresh.payment, which row from > the payments table would be returned, the first one or the second one? It > could be either, the result would not be deterministic. > > One reason it works this way so a unique constraint (which can be used to > enforce a one_to_one association) will actually work. In your case you also > have a NOT NULL constraint, so that fails instead of the UNIQUE constraint > failing. > > If you don't want the current behavior, pass a :setter association option > for how you want the payment= method to work. > > Thanks, > Jeremy > -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/sequel-talk/b81ec690-5798-46a5-ab05-083b328a31a1n%40googlegroups.com.
