Subject Re: [IBO] default value in table field
Author Helen Borrie
At 07:31 AM 01-06-01 +0000, you wrote:
>With IB 6 I have this situation
>table : FINFILE (
> NFILE VARCHAR(30) NOT NULL,
> DES60 VARCHAR(60) NOT NULL,
> NFILE_UP VARCHAR(30) DEFAULT CURRENT_TIMESTAMP NOT NULL,
> PRIMARY KEY (NFILE),
> UNIQUE (NFILE_UP));
>
>CREATE TRIGGER INS_NFILE_UNIQUE FOR FINFILE
>ACTIVE BEFORE INSERT POSITION 0
>AS
>BEGIN
> NEW.nfile_up = Upper(NEW.nfile);
>END
>
>I am at 3.6.Dg and use above table with TIBOTable ,TwwDataSource and
>TwwDBGrid. The grid has only 2 fileds (NFILE , DES60)
>
>I want let Db fill NFILE_UP but I receive the exception from
>IBA_Dataset.IMP : 'NFILE_UP is a required field'
>
>I tried following alternative steps :
>1) Not describe any field with table
>2) include in table all fields except NFILE_UP
>3) include all fields and define NFILE_UP required.False in
>TstringFields
>
>I always get exception.
>
>How can do it ?
>Using pure SQL I can insert a record with nodata in NFILE_UP.


Adalberto:

First, as to why this is happening: by making a UNIQUE constraint on NFILE_UP you have indirectly caused it to have a NOT NULL constraint as well. How this occurs is that IB automatically creates a unique index on the constrained column, and a unique index value in IB cannot evaluate to NULL. (As I have stated elsewhere, I consider this to be a bug. It should not be permissible to apply a UNIQUE constraint to a nullable column. Opinions differ. My argument is that the current situation causes problems like the one you are having!)

Second, I believe it may be that disabling the Required property on the TField object is having no effect on the REQUIRED attribute of the underlying TIB_Column object. Try doing it explicitly.
Open the IBO Fields Editor by clicking on the ellipsis (...) symbol on the SQL property in the Object Inspector.
Go to the ColumnAttributes tab.
Click the Prepare button to bring up the column list.
Select the NFILE_UP column.
Check the checkbox marked REQUIRED twice - the first time it will show true (Checked), the second time it will be clear and white. This sets the attribute explicitly false.

To test whether it has been stored, Unprepare the dataset, then prepare it again. If the REQUIRED checkbox is still white and clear, then you should be OK.
Click OK and test it by running the application.

As a comment, I don't understand why you want a UNIQUE constraint on the case-insensitive column. NFILE itself is unique. If you uppercase NFILE to populate NFILE_UP, then NFILE_UP will remove any distinctions made by proper-cased NFILE entries, e.g. Di Lorenzo and di Lorenzo will both be stored as DI LORENZO. A unique constraint on NFILE is thus going to prevent you from storing both.

Cheers,
Helen


All for Open and Open for All
InterBase Developer Initiative ยท http://www.interbase2000.org
_______________________________________________________