Table creation with foreign keys fails with error "unhashable list"

[bikash119]

Passing foreign keys while creating table fails with error Replication Steps

db = database('data/sample.db')
db['Table1'].create(id=int,name=str,pk='id')
db['Table2'].create(id=int,text=str,created_by=int,pk='id',foreign_keys=[('created_by','Table1','id')])

The below example works

db = database('data/sample.db')
db['Table1'].create(id=int,name=str,pk='int')
db['Table2'].create(id=int,text=str,created_by=int,pk='id')
db['Table2].add_foreign_key('created_by','Table1','id')

I confirmed it is sqlite_utils version issue. When I tried with Version: 3.37, passing foreign keys during table creation works fine.

from sqlite_utils import Database
db = Database('data/sample.db')
db['Table1'].create({"id":int,"name":str},pk='id')
db['Table2'].create({"id":int,"text":str,"created_by":int},pk='id'
                    ,foreign_keys=[('created_by','Table1','id')])

[samueller]

The latest version of fastlite uses sqlite_minutils Version: 3.37.0.post1. Still gives an error when adding foreign keys in the create function, but works fine with calling add_foreign_key on the table after creation.

[bikash119]

sqlite_minutils is stripped down version of sqlite-utils. Its not a fork of sqlite-utils so the latest changes of sqlite-utils may not have been included to sqlite-minutils. Pardon me, if my analysis is wrong and pls correct me if this doesn't makes sense.

[samueller]

Its not a fork of sqlite-utils so the latest changes of sqlite-utils may not have been included to sqlite-minutils.

Sure, but I would've expected that version 3.37 of sqlite-minutils includes the latest changes of version 3.37 of sqlite-utils. I haven't actually tried adding foreign keys during table creation in sqlite-utils, just going off your original post.

[Deufel]

I was testing some data model schema creation and this works for me maybe this helps;

db = Database('deleteMe.db',recreate=True)

# Should be default...i think... 
db.execute("PRAGMA journal_mode = WAL")
db.execute("PRAGMA busy_timeout = 5000")
db.execute("PRAGMA foreign_keys = ON")

# classes
class User:         id:int; email:str ; password_hash:str; role: str; first_name: str; last_name: str ; username: str ; birthday: str ; phone: str; role: str
class Event:        id:int; license:str; license_class:str; acct_name: str; acct_address: str; acct_county: str ; acct_owners: str; acct_phone: str; event_start: str; event_stops: str; event_address: str
class Subscription: id:int; user_id:int; state_id:int; expiration_date: str
class PaymentMethod:id:int; user_id:int; stripe_customer_id:str; stripe_payment_method_id: str; last_four_digits: str; card_type: str; expiry_month: int; expiry_year: int; is_default: int
class FavoriteEvent:id:int; user_id:int; event_id:int ; status:str

# Create tables with foreign keys defined
db.create(User, if_not_exists=True)
db.create(Event, if_not_exists=True)

db.create(Subscription, 
          foreign_keys=("user_id", "user"),
          if_not_exists=True)

db.create(PaymentMethod, 
          foreign_keys=("user_id", "user"),
          if_not_exists=True)

db.create(FavoriteEvent, 
          foreign_keys=(("user_id", "user"),("event_id","event")),
          if_not_exists=True)

# Generate diagram 
diagram(db.tables)

[bikash119]

I tried the below and these worked too.

users,students,grades = db.t.users,db.t.students,db.t.grades
subjects, topics,questions,scores = db.t.subjects,db.t.topics,db.t.questions,db.t.score

if users not in db.t:
    grades.create(id=uuid4,text=str,pk="id")
    users.create(id=uuid4, email=str, name=str, oauth_provider=str,userid=str,pk='id')
    subjects.create(id=uuid4,name=str,pk="id"
                    ,not_null=["name"])
    students.create(id=uuid4,name=str,pk="id"
                    ,not_null=["name","user_id","grade_id"]
                    ,user_id=uuid4
                    ,grade_id=uuid4
                    ,foreign_keys=[("user_id","users","id"),("grade_id","grades","id")]
                    )

Thank you @Deufel . Closing this issue.