I think dain suggested supplying e.g. INTEGER NOT NULL as the sql type.
Thank you, thank you, thank you!
Now, is this the way we are going to do it for the foreseeable future? Or, is this just a temporary way to do this and there will be another in the future?
It's obviously not a very good way. The nullable tag is useful independently of the "archaic sql syntax" with pks problem shared by mySQL, firebird, and no doubt others, however it is not an ideal solution for the pk problem either.
I wonder if a pluggable sql generator would be a good idea. Perhaps a highly configurable one could be supplied by default, and for really weird dbs you could write your own?
I submitted a patch that checks for primary key fields when the tables are being created and appends NOT NULL to the field definition.. Maybe a fix can be created based on something like that?
Are there any databases that don't support not null or explicitly declaring pk columns not null? I seem to recall that I suggested that before I came up with the tag and someone objected. I can't remember why, though.
SQL Server 2000
A table can have only one PRIMARY KEY constraint, and a column that participates in the PRIMARY KEY constraint cannot accept null values. Because PRIMARY KEY constraints ensure unique data, they are often defined for identity column.
This column is a primary key, which implies that other tables may rely on this column as a unique identifier for rows. Both UNIQUE and NOT NULL are implied by PRIMARY KEY. See PRIMARY KEY for more information.
* A PRIMARY KEY is a unique KEY with the extra constraint that all key columns must be defined as NOT NULL. In MySQL the key is named PRIMARY. A table can have only one PRIMARY KEY. If you don't have a PRIMARY KEY and some applications ask for the PRIMARY KEY in your tables, MySQL will return the first UNIQUE key, which doesn't have any NULL columns, as the PRIMARY KEY.
Oracle.. Sorry no reference :-) My Oracle 8 books says that a primary key column cannot contain nulls..
All these databases enforce a different constraint that is some syntax of "UNIQUE" that is a unique value but allows nulls.. But in reality, I think that is a different beast and should be configured differently.