changing default field value in archibus / sql

[Wanderer]

I've created new fields in Archibus before, but, haven't done much modifying of existing fields.

I need to modify the default value for a couple of my fields and the records save, but, when I run an update to my database schema, the ones that did not have a default already have saved fine, but, the ones that already had a default saved are failing.

I'm not certain if this is an Archibus thing or a SQL thing. I checked my SQL Bible (just like I did with my AutoCAD Bible :razz: ) but, couldn't find applicable entries just flipping through the index and related chapters.

Here's an excerpt of one of the errors:


Field was changed, prepare the ALTER TABLE statement: space_use
DROP INDEX LS._WA_SYS_00000001_385E379D
DROP INDEX LS._WA_SYS_00000029_385E379D
DROP INDEX LS._WA_SYS_00000031_385E379D
DROP INDEX LS._WA_SYS_00000011_385E379D
DROP INDEX LS._WA_SYS_00000023_385E379D
DROP INDEX LS._WA_SYS_00000020_385E379D
DROP INDEX LS._WA_SYS_00000018_385E379D
DROP INDEX LS._WA_SYS_00000017_385E379D
DROP INDEX LS._WA_SYS_00000015_385E379D
DROP INDEX LS._WA_SYS_00000010_385E379D
DROP INDEX LS._WA_SYS_0000000F_385E379D
DROP INDEX LS._WA_SYS_00000025_385E379D
DROP INDEX LS._WA_SYS_00000046_385E379D
DROP INDEX LS._WA_SYS_00000048_385E379D
Drop Default Value Constraint If Exists For SQLServer
Drop Constraint If Exists For SQLServer
Change Default Value For SQLServer
This operation is postponed:
ALTER TABLE ls ADD CONSTRAINT DF_ls_space_use_default DEFAULT 'RETAIL' FOR space_use
Executing the postponed Schema change
Error details: Column already has a DEFAULT bound to it.
Could not create constraint. See previous errors.
Executing the postponed Schema change
Error details: Column already has a DEFAULT bound to it.
Could not create constraint. See previous errors.


Of course, now that I'm looking back, I thought maybe I should save without a default, then add a different default, but, it looks like the one case which my users wanted left blank failed when I tried to remove the default.


Drop Default Value Constraint If Exists For SQLServer
Drop Constraint If Exists For SQLServer
Change Default Value For SQLServer
This operation is postponed:
ALTER TABLE cost_tran_recur ADD CONSTRAINT DF_cost_tran_recur_date_start_default DEFAULT NULL FOR date_start

I'd appreciate any advice or pointers to the right help file, etc, thanks!

[Wanderer]

Okay, so, I got my answer today.
I spoke to my consultant in our regular meeting and happened to ask him about this.

I'm still a noob with Archibus, so, when my local rep had taught me how to make field changes last winter, I'd been warned to use the settings as attached.

BUT, if you want to replace a default, you have to tick the option to Re-create from Scratch (in the Update Table Structure group, see screencap below).
You need to double and triple check that your values are valid (mine were selections from an enum list, so, obviously had to match one of the available options there), but, it went off without a hitch then.

96220
Thick Client
96221
WebCentral

This will be something I'll need to keep tucked away in the back of my mind. It's not often I have to make a change like this