Hi! On Tue, Dec 22, 2015 at 12:50:09PM -0500, Nathan Edwards <n...@georgetown.edu> wrote: > > A number of my students used some form of the ForeignKey abuse shown > below. The first one B(anInt=2,afk=a.id) is pretty benign (though > unnecessary), but the really strange one is B(anInt=4,afk='Strange!'). > At least with sqlite3 as the backend, this is stored in the database as > a string (!!!!), even though the schema declares it as an INT and > formalizes the reference constraint to table A's id (also an INT).
Well, SQLite allows that: https://www.sqlite.org/faq.html#q3 (and see below...) > Python 2.7.3, SQLObject version 2.1.2. > > from sqlobject import * > > class A(SQLObject): > aFloat = FloatCol() > > class B(SQLObject): > anInt = IntCol() > afk = ForeignKey("A") > > sqlhub.processConnection = connectionForURI('sqlite:test.db3?debug=1') > > A.dropTable(ifExists=True) > B.dropTable(ifExists=True) > A.createTable() > B.createTable() > > a = A(aFloat=1.0) > > b = B(anInt=1,afk=a) > b = B(anInt=2,afk=a.id) > b = B(anInt=3,afk=str(a.id)) > # This succeeds and is inserted to sqlite database! > b = B(anInt=4,afk='Strange!') He-he. Yes, that a wart but it's hard to fix. The PRIMARY KEY (id) column in a referenced table can be of string type[1] so ForeignKey allows both ints and strings (a ForeignKey doesn't know the type of the id column it points to). If you want stricter foreign keys use stricter backends: MySQL with InnoDB tables or Postgres. 1. http://sqlobject.org/FAQ.html#non-integer-ids > # IntCols are checked! > # This throws a formencode exception > # b = B(anInt='a string',afk=a) > > for a in A.select(): > print a > for b in B.select(): > # ValueError exception thrown in attempt to dereference > print b,b.afk > > - n > > -- > Dr. Nathan Edwards n...@georgetown.edu > Department of Biochemistry and Molecular & Cellular Biology > Georgetown University Medical Center > Room 1217, Harris Building, > 3300 Whitehaven St, NW, Washington DC 20007 > Phone: 202-687-7042, Fax: 202-687-0057 Oleg. -- Oleg Broytman http://phdru.name/ p...@phdru.name Programmers don't die, they just GOSUB without RETURN. ------------------------------------------------------------------------------ _______________________________________________ sqlobject-discuss mailing list sqlobject-discuss@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
