Cannot Set Column Default Value

[MichaelB]

Hi all. I have tried to find an answer to this in the forums in vain so I am posting this to see whether anyone else has experienced the same problem.

I am reading through Andrew Pitonyak's AndrewBase document (http://www.pitonyak.org/database/) and tried his example creating DEALER and ITEMS tables (around page 24). The tables are very simple:

Code: Select all

DEALER table
Field    Field Type             Comment
ID       Integer [INTEGER] Table's primary key
NAME     Text [VARCHAR]    Dealer name.

ITEM Table
Field    Field Type             Comment
ID       Integer [INTEGER] Table's primary key
NAME     Text [VARCHAR]    Item name.
DEALER   Integer [INTEGER] Dealer ID.

Following the document, I want to set a default value of 0 (zero) for the DEALER column in the ITEM table. Base seems to accept it in the Table Design screen. But something goes wrong when establishing a relationship between the tables (Tools | Relationships...). I can create the link between the tables (ID in the DEALER table and DEALER in the ITEM table). When I edit the link to set Update Options to Update Cascade and Delete Options to Set Default, I get and error message saying:

missing DEFAULT value on column DEALER in statement [ALTER TABLE "ITEM" ADD FOREIGN KEY ("DEALER") REFERENCES "DEALER" ("ID") ON UPDATE CASCADE ON DELETE SET DEFAULT]

Clicking OK deletes the relationship between the tables.

If I then go back to edit the table in the Table Design screen I find the default value is no longer there.

[r4zoli]

I found same error on OOo 3.2RC1, I will submit an issue.

Edit: Issue 108100 submitted.

[eremmel]

I was able to simulate your problem. It seems that Base is not updating the database with the default value one can specify in 'edit table'. However when you change your database via menu Tools->SQL... and run a command like:

Code: Select all

alter table T2 alter column C2 set default 0;

than the default value is stored in the database and you can change your relation as wanted. Note that the default value stored in the database is not reflected in the default value that you see in 'edit tables'. The latter seems to be used by base for input (you can read this from the text at the very right: Enter a default value for this field. When you later enter data in the table, this string will be used in each new record for the field selected and the former for things like relations deep into the database engine.
The tag 'Default value' in 'edit table' is misleading here and should read 'Input default value'.
We have here a design issue becaue 'Input default values' should be property of form etc and not of database design. In 'edit table' there is a mix between table-design and table-grid-input properties.

[MichaelB]

Thanks for your answers. I'm glad it wasn't something I was doing wrong. After posting I had discovered that using Tools | SQL and entering the appropriate SQL commands I was able to set up the relation as eremmel suggests. So for the time being I can work around it.

Thanks again.