AW: AW: ALTER FRAGMENT x UNIQUE CONSTRAINT
david@smooth1.co.uk wrote
>
> What do you mean the flags changed,what flags, where?
>
> A unique constraint implies a unique index to validate the uniqueness
> efficiently.
>
> What is a simple unique index? As opposed to what?
>
> It was a unique index and now it is a unique index what is the
> difference?
Connect to sysmaste database:
select s.partnum, hex(s.flags) flags , f.indexname
from sysptnkey s, bla:sysfragments f
where s.partnum = f.partn
and f.tabid = (select t.tabid from bla:systables t
where t.tabname = 'bla')
-- adjust database and table name to your settings
-- for attached indices, you'll have to join systables and sysindexes
instaed
-- of sysfragments.
partnum 2097331
flags 0x00000298
indexname 124_97
-- This is the result of the query before the ALTER FRAGMENT
partnum 2097332
flags 0x00000218
indexname 124_97
-- This is the result of the query after the ALTER FRAGMENT
Note, the differrence, flag 0x80 is missing ! This flag indicates a
unique index implements a unique key constarint.
A unique key that does not have this flag is a unique key, but
does not implement unique constraint.
The difference is that on a unique indes uniqueness will be checked
immediately, for a unique constraint it will fail only at commit time.
e.g suppose you have table bla , col1 ineger, and values 1,2,3,4 in the
table.
With unique constraint , the SQL statement
begin work;
update bla ste col1 = col1 + 1 ;
commit;
will succeed, as the uniques of the result will be check at commit time.
With an ordianry unique index , the stament will fail with -346/-100 ,
as the update of the first row ( 1 + 1 ) will collide with unique key
'2'
which is already there.
Superboer [superboer7@t-online.de] wrote:
> and if the alter fragment changes the unq constraint to an unq index
> then this is
> a bug for sure.
As I said, I'd vote for a bug, but not sure whether this behaviour is
documented
somewhere (maybe in a place I did not search)
In any case , it is inconsistent behaviour. In system catalog tables of
the database
the unique constraint is still defined and enabled.
I reported this as an defect to our devlopment and we will see.
Reagrds
Tilman