Nested attributes and referential integrity

nested_attributes.rb

require 'sequel'
require 'sqlite3'
require 'logger'

DB = Sequel.sqlite
DB.loggers << Logger.new($stdout)

DB.instance_eval do
  
  create_table :items do
    primary_key :id
  end
  
  create_table :assets do
    primary_key :id
    foreign_key :item_id, :items, null: false
    
    String      :name
  end

end

class Item < Sequel::Model
  one_to_one :asset
  
  plugin :nested_attributes
  
  nested_attributes :asset
end

class Asset < Sequel::Model
  many_to_one :item
end

Item.create asset_attributes: { name: 'foo' }

output

I, [2012-06-26T12:29:38.530782 #5187]  INFO -- : (0.000222s) PRAGMA foreign_keys = 1
I, [2012-06-26T12:29:38.534586 #5187]  INFO -- : (0.000053s) PRAGMA case_sensitive_like = 1
I, [2012-06-26T12:29:38.535019 #5187]  INFO -- : (0.000313s) CREATE TABLE `items` (`id` integer PRIMARY KEY AUTOINCREMENT)
I, [2012-06-26T12:29:38.535474 #5187]  INFO -- : (0.000122s) CREATE TABLE `assets` (`id` integer PRIMARY KEY AUTOINCREMENT, `item_id` integer NOT NULL REFERENCES `items`, `name` varchar(255))
I, [2012-06-26T12:29:38.536612 #5187]  INFO -- : (0.000595s) PRAGMA table_info('items')
I, [2012-06-26T12:29:38.540932 #5187]  INFO -- : (0.000174s) PRAGMA table_info('assets')
I, [2012-06-26T12:29:38.542817 #5187]  INFO -- : (0.000115s) SELECT sqlite_version() LIMIT 1
I, [2012-06-26T12:29:38.542947 #5187]  INFO -- : (0.000030s) BEGIN
I, [2012-06-26T12:29:38.543203 #5187]  INFO -- : (0.000075s) INSERT INTO `items` DEFAULT VALUES
E, [2012-06-26T12:29:38.548508 #5187] ERROR -- : SQLite3::ConstraintException: assets.item_id may not be NULL: INSERT INTO `assets` (`name`) VALUES ('foo')
I, [2012-06-26T12:29:38.548717 #5187]  INFO -- : (0.000068s) ROLLBACK
Sequel::DatabaseError: SQLite3::ConstraintException: assets.item_id may not be NULL

method close	in database.rb at line 97
method ensure in prepare	in database.rb at line 97
method prepare	in database.rb at line 97
method execute	in database.rb at line 134
method block (2 levels) in _execute	in sqlite.rb at line 176
method log_yield	in logging.rb at line 37
method block in _execute	in sqlite.rb at line 176
method block in synchronize	in connecting.rb at line 229
method hold	in threaded.rb at line 92
method synchronize	in connecting.rb at line 229
method _execute	in sqlite.rb at line 167
method execute_insert	in sqlite.rb at line 144
method execute_insert	in actions.rb at line 760
method insert	in actions.rb at line 299
method _insert_raw	in base.rb at line 1522
method _insert	in base.rb at line 1504
method block (2 levels) in _save	in base.rb at line 1559
method around_create	in base.rb at line 846
method block in _save	in base.rb at line 1556
method around_save	in base.rb at line 846
method _save	in base.rb at line 1551
method block (2 levels) in save	in base.rb at line 1232
method block in checked_transaction	in base.rb at line 1700
method block in transaction	in query.rb at line 262
method block in synchronize	in connecting.rb at line 229
method hold	in threaded.rb at line 92
method synchronize	in connecting.rb at line 229
method transaction	in query.rb at line 261
method checked_transaction	in base.rb at line 1700
method block in save	in base.rb at line 1232
method checked_save_failure	in base.rb at line 1688
method save	in base.rb at line 1232
method block in nested_attributes_create	in nested_attributes.rb at line 171
method call	in instance_hooks.rb at line 61
method block in run_after_instance_hooks	in instance_hooks.rb at line 61
method each	in instance_hooks.rb at line 61
method run_after_instance_hooks	in instance_hooks.rb at line 61
method after_save	in instance_hooks.rb at line 38
method block in _save	in base.rb at line 1589
method around_save	in base.rb at line 846
method _save	in base.rb at line 1551
method block (2 levels) in save	in base.rb at line 1232
method block in checked_transaction	in base.rb at line 1700
method _transaction	in query.rb at line 300
method block in transaction	in query.rb at line 263
method block in synchronize	in connecting.rb at line 229
method hold	in threaded.rb at line 105
method synchronize	in connecting.rb at line 229
method transaction	in query.rb at line 261
method checked_transaction	in base.rb at line 1700
method block in save	in base.rb at line 1232
method checked_save_failure	in base.rb at line 1688
method save	in base.rb at line 1232
method create	in base.rb at line 163
method <main>	in untitled at line 35

output_no_constraint

I, [2012-06-26T12:31:22.368055 #5260]  INFO -- : (0.000214s) PRAGMA foreign_keys = 1
I, [2012-06-26T12:31:22.368178 #5260]  INFO -- : (0.000024s) PRAGMA case_sensitive_like = 1
I, [2012-06-26T12:31:22.368632 #5260]  INFO -- : (0.000381s) CREATE TABLE `items` (`id` integer PRIMARY KEY AUTOINCREMENT)
I, [2012-06-26T12:31:22.369221 #5260]  INFO -- : (0.000123s) CREATE TABLE `assets` (`id` integer PRIMARY KEY AUTOINCREMENT, `item_id` integer REFERENCES `items`, `name` varchar(255))
I, [2012-06-26T12:31:22.370364 #5260]  INFO -- : (0.000597s) PRAGMA table_info('items')
I, [2012-06-26T12:31:22.375247 #5260]  INFO -- : (0.000191s) PRAGMA table_info('assets')
I, [2012-06-26T12:31:22.377084 #5260]  INFO -- : (0.000101s) SELECT sqlite_version() LIMIT 1
I, [2012-06-26T12:31:22.377197 #5260]  INFO -- : (0.000028s) BEGIN
I, [2012-06-26T12:31:22.377444 #5260]  INFO -- : (0.000076s) INSERT INTO `items` DEFAULT VALUES
I, [2012-06-26T12:31:22.377798 #5260]  INFO -- : (0.000063s) INSERT INTO `assets` (`name`) VALUES ('foo')
I, [2012-06-26T12:31:22.378147 #5260]  INFO -- : (0.000116s) SELECT * FROM `assets` WHERE (`id` = 1) LIMIT 1
I, [2012-06-26T12:31:22.383572 #5260]  INFO -- : (0.000130s) UPDATE `assets` SET `item_id` = NULL WHERE ((`item_id` = 1) AND (`id` != 1))
I, [2012-06-26T12:31:22.383909 #5260]  INFO -- : (0.000070s) UPDATE `assets` SET `item_id` = 1, `name` = 'foo' WHERE (`id` = 1)
I, [2012-06-26T12:31:22.384296 #5260]  INFO -- : (0.000103s) SELECT * FROM `items` WHERE (`id` = 1) LIMIT 1
I, [2012-06-26T12:31:22.384402 #5260]  INFO -- : (0.000038s) COMMIT