Обсуждение: turn off auto index for foreign key
Hi admins,
I would like to turn off the auto index for child table's foreign key. It is because i want to read the information_schema for table, column, index, unique and so on after creating the database. How to turn off the auto behavior? Or Is there a way to distinguish between auto one and user created index?
Thanks
Tommy Cheng
I would like to turn off the auto index for child table's foreign key. It is because i want to read the information_schema for table, column, index, unique and so on after creating the database. How to turn off the auto behavior? Or Is there a way to distinguish between auto one and user created index?
Thanks
Tommy Cheng
On Mon, Jun 9, 2008 at 8:48 PM, Tommy Cheng <csckcac@gmail.com> wrote: > Hi admins, > I would like to turn off the auto index for child table's foreign key. It > is because i want to read the information_schema for table, column, index, > unique and so on after creating the database. How to turn off the auto > behavior? Or Is there a way to distinguish between auto one and user created > index? There is no auto index creation for child relationships, only for the parent (unique index for primary key etc)
you are right. I double checked. So, is there any way to turn off auto index creation for the parent? (the best is changing postgresql database setting)
I am using postgres (PostgreSQL) 8.1.9 on CentOS 5 x86_64
I am using postgres (PostgreSQL) 8.1.9 on CentOS 5 x86_64
On Tue, Jun 10, 2008 at 11:50 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
There is no auto index creation for child relationships, only for theOn Mon, Jun 9, 2008 at 8:48 PM, Tommy Cheng <csckcac@gmail.com> wrote:
> Hi admins,
> I would like to turn off the auto index for child table's foreign key. It
> is because i want to read the information_schema for table, column, index,
> unique and so on after creating the database. How to turn off the auto
> behavior? Or Is there a way to distinguish between auto one and user created
> index?
parent (unique index for primary key etc)
On Mon, Jun 9, 2008 at 10:17 PM, Tommy Cheng <csckcac@gmail.com> wrote: > you are right. I double checked. So, is there any way to turn off auto index > creation for the parent? (the best is changing postgresql database setting) > I am using postgres (PostgreSQL) 8.1.9 on CentOS 5 x86_64 > Again, there is no auto index creation for foriegn keys. However if you declared the column unique or its the pkey (ala unique) (As Scott said) then there will an index. No you cant turn it off because its how postgres enforces uniqueness see http://www.postgresql.org/docs/8.1/interactive/index-unique-checks.html for more My guess, use the name of the index to try to distinguish. Auto generated ones will always be in the form: pkey: <table_name>_pkey(num) unique: <table_name>_<column_name>_key(num)
um...Your soln is possible. But if the user created an unique index using constraint name <table_name>_<column_name>_key5, it will not be able to distinguish it from other auto index.
On Tue, Jun 10, 2008 at 12:58 PM, Alex Hunsaker <badalex@gmail.com> wrote:
On Mon, Jun 9, 2008 at 10:17 PM, Tommy Cheng <csckcac@gmail.com> wrote:Again, there is no auto index creation for foriegn keys. However if
> you are right. I double checked. So, is there any way to turn off auto index
> creation for the parent? (the best is changing postgresql database setting)
> I am using postgres (PostgreSQL) 8.1.9 on CentOS 5 x86_64
>
you declared the column unique or its the pkey (ala unique) (As Scott
said) then there will an index.
No you cant turn it off because its how postgres enforces uniqueness
see http://www.postgresql.org/docs/8.1/interactive/index-unique-checks.html
for more
My guess, use the name of the index to try to distinguish. Auto
generated ones will always be in the form:
pkey: <table_name>_pkey(num)
unique: <table_name>_<column_name>_key(num)