On Wed, Jun 30, 2010 at 09:04:14AM -0400, Tim Romano scratched on the wall:
> Notwithstanding your insistence that INT and INTEGER are the same in SQLite, > *with respect to use in the PRIMARY KEY definition* there are subtle > differences. Yes. However, CREATE TABLE ... AS SELECT doesn't carry over any constraints, including primary key definitions. You can't get a PRIMARY KEY (of any type) out of CREATE TABLE ... AS SELECT, so the difference doesn't matter. CREATE TABLE ... AS SELECT creates a table from a result set, i.e. the output of a SELECT. Result sets are not tables. Result sets have no constraints. The CREATE TABLE statement has no idea what the original table(s) is, where the data came from, what constraints were originally on it, or anything else. It can only derive meaningful column affinities when the SELECT returns a direct column reference, and even that is a bit of a slight of hand. So the difference between INT and INTEGER doesn't matter, since there is no chance of the original PK constraint being defined in the new table. For the purpose of a CREATE TABLE .. AS SELECT, they are identical in all respects. > Corollary advice would be to eschew all use of the CREATE TABLE FOO AS > SELECT.... syntax because it produces a table with INT PRIMARY KEY > definition No, it doesn't. It produces a table with an "INT" definition. No "PRIMARY KEY", no "NOT NULL", no "UNIQUE", or any other constraint. Result sets do not have keys or constraints. The result set returned by SELECT * FROM FOO is not the same as FOO. > I mean cross-implementation portability not cross-platform portability. If I go and modify the source code so that a ROWID alias is only established when ORANGE PRIMARY KEY is used, I (and my customers) only have myself to blame. It is, by definition, incompatible. Having the mainline strive for portability is pointless and the fix, IMHO, is for the modified code to use a different file header so the files simply cannot be moved back and forth. Once you make a modification that deep, then for all intents and purposes it becomes a different platform. > As I said, all hell can break loose because the queries don't break -- they > simply return the wrong results which on their face may seem plausible and > could therefore go undetected as erroneous until well after the damage > (whatever it may be) has been done. This is shifting the topic from CREATE TABLE to foreign keys, but I'm still not convinced it is that big of a deal. Foreign keys reference a column(s) by name, and that column is not even required to be a primary key. If an "id" column shifts from a INTEGER PRIMARY KEY to a INT PRIMARY KEY the foreign key isn't going to care, and will still continue to function. And this has nothing to do with CREATE TABLE ... AS SELECT, since it will never produce a table with either a "PRIMARY KEY" or a "UNIQUE" constraint on it, so the created table can never be an FK target. If you try, the database won't even load. I still think this gets back to what others have been saying all along... CREATE TABLE ... AS SELECT is not meant to clone tables. It is primarily used to create "instanced views", and almost always as temp tables. They're especially common in data warehouse data mining techniques, where you might run a very complex query to pull out a set of central ID values, and then run a report on those IDs. If the report is extensive enough, you might not want the initial query re-run each time, so you just do it once, save the results, and use that pre-selected list to join. In that role CREATE TABLE ... AS SELECT serves its purpose very well producing the expected results. Your concerns about PKs and FKs don't even come into play, as they can't happen-- even within the same implementation. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
