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.
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.
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.
