Friday, January 13, 2006

deferred constraints part 2

What you have to be aware of when you create a deferrable primary key:

- the system will need an index for the primary key as you know
- if there already is an index for the column you want to add a primary key constraint to, then
- the server will check, if the index is usable state
- it will check if the index is a unique index (does not allow duplicate values)
- if the index is a unique index the server will check if the primary key you want ot add is deferrable
- if the existing index is unique and the primary key you want to add is not deferrable ( which is the default , remember),
then the existing index can be used for the primary key constraint
- if the existing is unique but the primary key you create is deferrable, the existing index cannot be used and needs to be dropped first, before a non unique index is created (an index which allows duplicate values).

Both, the dropping and the creating of the index might significantly consume time on the system!!
You should thing deep enough before you create a deferrable primary key. Maybe it is sufficiently enough for your application that the foreing key is deferrable and you do not need to create a deferrable primary key and so can use an already existing index.


Post a Comment

<< Home