Cannot Set Column Default Value

Creating tables and queries

Cannot Set Column Default Value

Postby MichaelB » Wed Jan 06, 2010 2:33 am

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   Expand viewCollapse view
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.
Michael
Open Office 3.2.0 OOO320m12 (Build:9483) on Vista Home Premium
MichaelB
 
Posts: 11
Joined: Wed Jan 06, 2010 12:29 am
Location: Australia

Re: Cannot Set Column Default Value

Postby r4zoli » Wed Jan 06, 2010 10:15 am

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

Edit: Issue 108100 submitted.
Last edited by r4zoli on Wed Jan 06, 2010 10:51 am, edited 1 time in total.
AOO 4.0 and LibO 4 on Win 8
Hungarian forum co-admin
User avatar
r4zoli
Volunteer
 
Posts: 2882
Joined: Mon Nov 19, 2007 8:23 pm
Location: Budapest, Hungary

Re: Cannot Set Column Default Value

Postby eremmel » Wed Jan 06, 2010 10:20 am

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   Expand viewCollapse view
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.
It's Microsoft marketing that tells you computers are qualified for non-technicians
eremmel
 
Posts: 1041
Joined: Tue Dec 30, 2008 1:15 am

Re: Cannot Set Column Default Value

Postby MichaelB » Wed Jan 06, 2010 2:24 pm

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.
Michael
Open Office 3.2.0 OOO320m12 (Build:9483) on Vista Home Premium
MichaelB
 
Posts: 11
Joined: Wed Jan 06, 2010 12:29 am
Location: Australia


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 3 guests