Create table fails in Sybase

Trying to create a new table in Sybase with a primary key and I get the error Column 'race_date' is part of a primary key constraint and cannot be null. The GUI tool generates the following SQL to build the table CREATE TABLE thrdb.dbo.race_wps ( track CHAR(3), race_date INT, race_num INT, card_id CHAR(1), country CHAR(3), wps_pool MONEY NULL, PRIMARY KEY (race_date, track, race_num, card_id, country) ) If I modify it to be CREATE TABLE thrdb.dbo.race_wps ( track CHAR(3) NOT NULL, race_date INT NOT NULL, race_num INT NOT NULL, card_id CHAR(1) NOT NULL, country CHAR(3) NOT NULL, wps_pool MONEY NULL, PRIMARY KEY (race_date, track, race_num, card_id, country) ) All works as desired. Not sure if this is a Sybase specific setting on my server or an issue with the driver but it would be nice if unchecking the NULL box forced the NOT NULL keyword in place

Hi Doug, When this feature was created, columns were NOT NULL by default in a Sybase database which is why only columns marked as nullable gets a NULL keyword generated. Is this no longer the case, or can it be changed per database server installation? If you know any more about it, please let us know so we can adjust the behavior if needed. Best Regards, Hans

I am not 100% sure. I have asked a couple of our DBAs to find out exactly what might be going on (could have been a change between ASE 12.5 and 15)

Hans, Our parent company DBAs were able to run the create SQL on ASE 15.5 (same rev I am running) without adding the NOT NULL which tells me it is a server level setting (default value somewhere). So it may work or it may not. I can obviously work around it if needed but I would prefer that the GUI explicitly generate the NOT NULL syntax so no matter what the server config it will work and the create statements reflect exactly what I asked it to do.

Our DBA found the answer the database option ?allow nulls by default? is set to ?true? for the thrdb database. Here?s the what the doc says: · Setting the allow nulls by default option to true changes the default value of a column from not null to null, in compliance with the SQL standards. The Transact-SQL default value for a column is not null, meaning that null values are not allowed in a column unless null is specified in the · create table or alter table column definition. allow nulls by default true reverses this. You cannot use allow nulls by default to change the nullibility of a column during select into statements. Instead, use convert to specify the nullibility of the resulting columns. So if the option is set then the NOT NULL option has to be explicitly stated in order for a create to work. Not knowing how difficult this is to change I obviously leave it to you but as I said before I would prefer to have the tool generate the exact SQL I asked for rather than rely on a setting in the DB being set. I would assume this is also the case in SQL server but don't have one running to test on. Thanks (and I am really liking the new version)

Hi Doug, Thanks so much for your help on this. I've opened a ticket to get this fixed. I tried to find a way to read what the current value of "allow nulls by default" is for a certain database to adjust accordingly, but I didn't find anything. Unless you or your DBAs know of a trick to do so, we will instead fix this by generating either a NULL and NOT NULL constraint based on the Nullable setting. In other words, we will never assume one is the default. Best Regards, Hans

Hans, there are a couple of ways to do it, select name, status & 8192 from sysdatabases where name="CurrentDB" If you get 8192 back then it is set to allow nulls or sp_helpdb DBName and parse the status field for the string "allow nulls by default" I think however trying to do it that way might not be the best answer (for example what if you design a query on one server to run on multiple and they aren't the same). I would really think that setting the desired option explicitly is a better answer (either null or not null).

Hi Doug, I did see the sp_helpdb procedure, but parsing an output string that may differ from version to version is always risky. Either way, I agree that it is safer to generate both NULL and NOT NULL and don't worry about what is default. Best Regards, Hans

True (Sybase will change the text string just to mess with you), the help_db is simply doing a bitwise and on the status field so that won't change but I think the right answer is as outlined. Thanks for all the help (I thought I was going nuts for a bit).