Re: On columns with default ___ not null

Ken Miles writes: > Why doesn't this work? > > I create a table with a column spec something like the following: > > part_number char(12) default " " not null, > > The description from the SQL Reference manual suggests that the default > will be put into the column on an insert if I don't specify anything > but this is not always what happens. In an ISQL form I get the message > "column .. does not allow nulls". A similar thing happens in ESQL/C if > the variable for the column is NULL. In ISQL Query it does work if I > don't mention the part_number column in an insert statement but this isn't > an option in an ISQL form which needs to display the column field. > > Is this a bug or feature? > > Ken Miles > HP Vancouver Division > kenm@vcd.hp.com It's a feature. If you do not specify the field then it will default. But if you give a value to the field (and Null is a value meaning not known) then it will attempt to put the value in the field. On entering null you will run into the constraint on the field and the engine will reject the entry. In forms if the field appears on the screen and the user does not enter anything to it or deletes the contents of the field ISQL assumes the value to be NULL and will specify this for the insert/update which will then fail due to the not null constraint. For forms you need to specify that the field on the form has a default of spaces or zero to ensure that you do not hit this problem. Cheers - Jim -------------------------------------------------------------------- Name: Jim Gordon Internet: jgordon@ssf-sys.DHL.COM Company: DHL Systems Inc Phone: (415) 358-5911 (Work) Address: 1700 S. Amphlett Blvd. (415) 882-9728 (Home) San Mateo, CA 94402 Fax: (415) 571-6429 --------------------------------------------------------------------