question about primary key
It seems to me that it's better when creating a table to first add a unique index on the column(s) you want to have a primary key on, and then to add the primary key with the alter table add constraint statement. It seems so, because the primary key will use the existing unique index instead of wasting space creating another index. Is this thinking right ? Or is there some other reason that one method would be preferred above another ? Thanks for any advice. Floyd ======================== -<<Floyd Wellershaus>>- Database Administrator Unix Administrator email: fwellers@yahoo.com Home: 703-430-0805 Cell: 703-477-6045 ======================== http://www.one.org/
Floyd Wellershaus wrote: > It seems to me that it's better when creating a table to first add > a unique index on the column(s) you want to have a primary key on, and > then to add the primary key with the alter table add constraint statement. > It seems so, because the primary key will use the existing unique index > instead of wasting space creating another index. > > Is this thinking right ? Or is there some other reason that one method > would be preferred above another ? I'll reiterate what the Clown said. Best to create the indexes to support constraints first, yes. You get to name them and they are easier to reorg later if needed. Also you get to place the index in the dbspace in which you want it live. These are why myschema automatically produces a CREATE INDEX statements for all hidden constraint indexes generating a dummy name from the constraint type and hidden index name. Art S. Kagel > Thanks for any advice. > > Floyd
On Feb 23, 8:00 am, fred <f...@bloomberg.com> wrote: > dbspace in which you want it live. These are why myschema automatically > produces a CREATE INDEX statements for all hidden constraint indexes > generating a dummy name from the constraint type and hidden index name. I am going to have to finally break down and start using myschema because that is a nice feature. I completely agree with sensible naming. Nothing like getting a message "violated constraint r1024_325." I always explicitly name my indexes and constraints (except not null). I include the table name and the kind of thing: Primary Key Constraint <table_name>_pk, Foreign key constraints: <table_name>_fk1, etc. (I haven't had a good reason to do more than number the foreign key constraints. Some people might include the name of the primary table in the name.) For indexes <table_name>_<number><ux|x>. employee_0ux would be the index for the primary key, employee_1x would be another index on the employee table that wasn't unique. I am sure there are other ways but when I look at my space usage reports I can group the tables together easily. Also, when I look at object access profile reports if I see employee_1x accessed more than employee_0ux I might need to look at some application SQL because I would be suspicious of a table that was accessed by a secondary key more than the primary key. Also, when you are hunting down a locking problem when you have the index names related to the table names it helps track down the locking issue faster.
bozon wrote: > On Feb 23, 8:00 am, fred <f...@bloomberg.com> wrote: > >> dbspace in which you want it live. These are why myschema automatically >> produces a CREATE INDEX statements for all hidden constraint indexes >> generating a dummy name from the constraint type and hidden index name. > > I am going to have to finally break down and start using myschema > because that is a nice feature. <satisfied grin> If you like that you'll love the extent warnings and extent recoding features. > I completely agree with sensible naming. Nothing like getting a > message "violated constraint r1024_325." I always explicitly name my > indexes and constraints (except not null). I include the table name > and the kind of thing: > > Primary Key Constraint <table_name>_pk, Foreign key constraints: > <table_name>_fk1, etc. (I haven't had a good reason to do more than Yup, do the same (great minds and all that ;-) except use a prefix instead of suffix PK_<tablename>, FK1_, AK1_, etc. > number the foreign key constraints. Some people might include the name > of the primary table in the name.) For indexes > <table_name>_<number><ux|x>. employee_0ux would be the index for the > primary key, employee_1x would be another index on the employee table > that wasn't unique. > > I am sure there are other ways but when I look at my space usage > reports I can group the tables together easily. Also, when I look at > object access profile reports if I see employee_1x accessed more than > employee_0ux I might need to look at some application SQL because I > would be suspicious of a table that was accessed by a secondary key > more than the primary key. > > Also, when you are hunting down a locking problem when you have the > index names related to the table names it helps track down the locking > issue faster. For sure. Art S. Kagel