Ask a Question related to Informix, Design and Development.
-
Bill Hamilton #1
Changing Primary Index Question
GlacierYesterday I had to add a column to a table that had a 3-column
primary index
and this new column (a nullable column) had to become part of the primary
key.
The only way I could make it happen was to unload the table with null in
that spot,
drop the table, add the table, reload the table, add put back all other
contraints.
Just for my future reference, is there an easier way (under IDS 7.31) ?
sending to informix-list
Bill Hamilton Guest
-
default index for primary key of a table
Vinita.Bansal@trilogy.com writes: Postgres does not support UNIQUE constraints (and PRIMARY KEY implies a UNIQUE constraint) without an index.... -
converting unique index into primary key
I need to convert an existing unique index on a very heavily inserted table into a primary key. Alter table works, but locks the table for too... -
default index created for primary key
Hi, I want to turn off the default setting in postgres for index creation on primary key of a table. Is it possible and how? Regards Vinita ... -
duplicate values in the index, primary key, or relationship
I have a MSA2K db. Table: customers Columns: fileNumber: (primary) Indexed(No Duplicates) fileName Client ClientType fileYear boxNumber:... -
is primary key implicitly included in an index?
on the following: CREATE TABLE T (id INTEGER NOT NULL PRIMARY KEY, Name varchar(50) NOT NULL); I want the following query to run very fast:... -
John Carlson #2 Re: Changing Primary Index Question
On Wed, 3 Sep 2003 10:12:18 -0500, "Bill Hamilton" <bham@finsco.com>
wrote:
As far as I know, the definition of a PK doesn't allow a nullable>
>GlacierYesterday I had to add a column to a table that had a 3-column
>primary index
>and this new column (a nullable column) had to become part of the primary
>key.
>The only way I could make it happen was to unload the table with null in
>that spot,
>drop the table, add the table, reload the table, add put back all other
>contraints.
>
>Just for my future reference, is there an easier way (under IDS 7.31) ?
>
column . . . . am I wrong?
John Carlson Guest
-
Paul Watson #3 Re: Changing Primary Index Question
Nope I think you are right, at least with a single column index.
John Carlson wrote:-->
> On Wed, 3 Sep 2003 10:12:18 -0500, "Bill Hamilton" <bham@finsco.com>
> wrote:
>>> >
> >GlacierYesterday I had to add a column to a table that had a 3-column
> >primary index
> >and this new column (a nullable column) had to become part of the primary
> >key.
> >The only way I could make it happen was to unload the table with null in
> >that spot,
> >drop the table, add the table, reload the table, add put back all other
> >contraints.
> >
> >Just for my future reference, is there an easier way (under IDS 7.31) ?
> >
> As far as I know, the definition of a PK doesn't allow a nullable
> column . . . . am I wrong?
Paul Watson #
Oninit Ltd # Growing old is mandatory
Tel: +44 1436 672201 # Growing up is optional
Fax: +44 1436 678693 #
Mob: +44 7818 003457 #
[url]www.oninit.com[/url] #
Paul Watson Guest
-
Murray Wood \(IList\) #4 RE: Changing Primary Index Question
A column in a primary key cannot be null.
1. Update that column so it is not null.
2. Unload and delete rows with null.
MW
sending to informix-list> -----Original Message-----
> From: [email]owner-informix-list@iiug.org[/email]
> [mailto:owner-informix-list@iiug.org]On Behalf Of Bill Hamilton
> Sent: Thursday, 4 September 2003 3:12 a.m.
> To: informix-list iiug
> Subject: Changing Primary Index Question
>
>
> GlacierYesterday I had to add a column to a table that had a 3-column
> primary index
> and this new column (a nullable column) had to become part of
> the primary
> key.
> The only way I could make it happen was to unload the table
> with null in
> that spot,
> drop the table, add the table, reload the table, add put back
> all other
> contraints.
>
> Just for my future reference, is there an easier way (under
> IDS 7.31) ?
>
> sending to informix-list
Murray Wood \(IList\) Guest
-
Bill Hamilton #5 Re: Changing Primary Index Question
With a single column index, you could have just one row that had a null in
that column.
With multiple columns, I believe you can have as many as you want, so long
as the whole key is unique on each row.
----- Original Message -----
From: "John Carlson" <john_carlson@whsmithusa.com>
To: <informix-list@iiug.org>
Sent: Wednesday, September 03, 2003 1:24 PM
Subject: Re: Changing Primary Index Question
sending to informix-list> As far as I know, the definition of a PK doesn't allow a nullable
> column . . . . am I wrong?
Bill Hamilton Guest
-
Francisco Roldan #6 RE: Changing Primary Index Question
But one row with a null value on one of its
primary key fields doesn't make sense
because of the primary key's definition.
Regards
-----Mensaje original-----
De: Bill Hamilton [mailto:bham@finsco.com]
Enviado el: Miércoles, 03 de Septiembre de 2003 02:50 p.m.
Para: John Carlson; [email]informix-list@iiug.org[/email]
Asunto: Re: Changing Primary Index Question
With a single column index, you could have just one row that had a null in
that column. With multiple columns, I believe you can have as many as you
want, so long as the whole key is unique on each row.
----- Original Message -----
From: "John Carlson" <john_carlson@whsmithusa.com>
To: <informix-list@iiug.org>
Sent: Wednesday, September 03, 2003 1:24 PM
Subject: Re: Changing Primary Index Question
sending to informix-list> As far as I know, the definition of a PK doesn't allow a nullable
> column . . . . am I wrong?
sending to informix-list
Francisco Roldan Guest
-
ramesh.vasudevan@verizon.com #7 Re: Changing Primary Index Question
All:
Yes .. Primary key field doesn't allow NULL value. That is the major
difference UNIQUE constraint and PRIMARY KEY constraint.
btw - UNIQUE index is different from UNIQUE constraint.
Thank You
Ramesh Vasudevan
"John Carlson"
<john_carlson@whsm To: [email]informix-list@iiug.org[/email]
ithusa.com> cc:
Sent by: Subject: Re: Changing Primary Index Question
owner-informix-lis
[email]t@iiug.org[/email]
09/03/03 02:24 PM
Please respond to
"John Carlson"
On Wed, 3 Sep 2003 10:12:18 -0500, "Bill Hamilton" <bham@finsco.com>
wrote:
As far as I know, the definition of a PK doesn't allow a nullable>
>GlacierYesterday I had to add a column to a table that had a 3-column
>primary index
>and this new column (a nullable column) had to become part of the primary
>key.
>The only way I could make it happen was to unload the table with null in
>that spot,
>drop the table, add the table, reload the table, add put back all other
>contraints.
>
>Just for my future reference, is there an easier way (under IDS 7.31) ?
>
column . . . . am I wrong?
sending to informix-list
ramesh.vasudevan@verizon.com Guest
-
Art S. Kagel #8 Re: Changing Primary Index Question
On Wed, 03 Sep 2003 11:12:18 -0400, Bill Hamilton wrote:
1- BEGIN WORK;> GlacierYesterday I had to add a column to a table that had a 3-column primary
> index
> and this new column (a nullable column) had to become part of the primary key.
> The only way I could make it happen was to unload the table with null in that
> spot,
> drop the table, add the table, reload the table, add put back all other
> contraints.
>
> Just for my future reference, is there an easier way (under IDS 7.31) ?
>
> sending to informix-list
2- SET CONSTRAINTS ALL DEFERRED;
3- ALTER TABLE ... DROP CONSTRAINT <primary constraint name>;
4- ALTER TABLE ... ADD (newcol...);
5- Populate newcol so it is not NULL;
6- ALTER TABLE ... ADD CONSTRAINT PRIMARY KEY (<old & new key cols>)...;
7- Restablish FOREIGN keys on referring tables (they'll be dropped along with
the primary key on this table).
8- COMMIT WORK;
Art S. Kagel
Art S. Kagel Guest
-
Abraham Kirubakaran #9 Re: Changing Primary Index Question
You can add the column dynamically, but to make it a
part of the index, you will have to recreate the
index, since this "NULL" column is part of a
multi-column primary key, you will get away with it,
but will be out of luck if this is the only column in
the index.
--- [email]ramesh.vasudevan@verizon.com[/email] wrote:>
> All:
>
> Yes .. Primary key field doesn't allow NULL value.
> That is the major
> difference UNIQUE constraint and PRIMARY KEY
> constraint.
> btw - UNIQUE index is different from UNIQUE
> constraint.
>
> Thank You
> Ramesh Vasudevan
>
>
>
>
>
>
>
> "John Carlson"
>
>
> <john_carlson@whsm To:
> [email]informix-list@iiug.org[/email]
>
> ithusa.com> cc:
>
>
> Sent by:
> Subject: Re: Changing Primary Index Question
>
> owner-informix-lis
>
>
> [email]t@iiug.org[/email]
>
>
>
>
>
>
>
>
> 09/03/03 02:24 PM
>
>
> Please respond to
>
>
> "John Carlson"
>
>
>
>
>
>
>
>
>
>
>
>
> On Wed, 3 Sep 2003 10:12:18 -0500, "Bill Hamilton"
> <bham@finsco.com>
> wrote:
>> that had a 3-column> >
> >GlacierYesterday I had to add a column to a table> become part of the primary> >primary index
> >and this new column (a nullable column) had to> the table with null in> >key.
> >The only way I could make it happen was to unload> add put back all other> >that spot,
> >drop the table, add the table, reload the table,> way (under IDS 7.31) ?> >contraints.
> >
> >Just for my future reference, is there an easier>> >
> As far as I know, the definition of a PK doesn't
> allow a nullable
> column . . . . am I wrong?
>
>
>
>
>
>
> sending to informix-list
__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
[url]http://sitebuilder.yahoo.com[/url]
sending to informix-list
Abraham Kirubakaran Guest
Reply With QuoteSimilar Questions and Discussions
Posting Permissions
- You may not post new threads
- You may post replies
- You may not post attachments
- You may not edit your posts
