Re: Primary Key Constraint Question

Bappy wrote: > >Sub: Primary key constraint > >I need a little clarification on the subject of 'Primary Key >Constraint'. I have never worked with this before. > >SYSTEM INFO: > Unix, Informix Online Server 7.1 and 7.2 > > In ORACLE, as I was told, when you build a "Primary Key >constaint", you can specify a dbspace where ORACLE will store the index >file related to this "Primary Key". When creating an index, Informix >let's you specify a dbspace where the index file will be stored. > >QUESTION: >Assuming informix will create an index file when a 'PRIMARY KEY' >contraint is used ( and apperantly hidden from me), Is there any way to >tell informix server to put this file in a specific dbspace? Bappy (is that your real name?) this one belongs in the FAQ if it's not already there. No, INformix does not allow for any easy way to do this but you can exploit some other behaviors: 1. Create the table but do not specify the primay key constraint. 2. Create a unique index on the columns you had designated as the PK. Specify the dbspace for the index. 3. ALTER TABLE yazoo ADD CONSTRAINT UNIQUE (col1, col2, ..) CONSTRAINT yazoo_pk -- Optional: just naming the constraint The engine will note that a unique index already exists on those columns and will use that to support the uniqueness constraint. In case you hadn't already guessed this, a uniqueness constraint uses a unique index to recognize violations. However, when the index is built together with the constraint, the index name begins with a space character, making it syntactically impossible to delete the index with the "delete index" command. This index will be vulnerble to fumble- fingered database admins, with potential for arcane errors for people depending on the constraint. -- -- Jake (Lost in thought and won't ask for directions) . . _..-'( )`-.._ ./'. '||\\\\. }\\_/{ .//||` .`\\. ./'.|'.'||||\\\\|.. )o o( ..|//||||`.`|.`\\. ./'..|'.|| |||||\\`````` \\,@,/ ''''''/||||| ||.`|..`\\. ./'.||'.|||| ||||||||||||. ||| .|||||||||||| ||||.`||.`\\. /'|||'.|||||| ||||||||||||{ | }|||||||||||| ||||||.`|||`\\ '.|||'.||||||| ||||||||||||{ | }|||||||||||| |||||||.`|||.` '.||| ||||||||| |/' ``\\||`` | ''||/'' `\\| ||||||||| |||.` |/' \\./' `\\./ \\!|\\ /|!/ \\./' `\\./ `\\| V V V }' `\\ /' `{ V V V \\ \\ \\ V / / / +-----------------------------------------------------------+ | Impeccable Logic: A thought process which successfully | | resists chicken bites | +-----------------------------------------------------------+