Adding a Primary Key
Kate wanted to add a primary key constraint to a table with the index stored in a separate dbspace (i001) and with a meaningful name, but ALTER TABLE ... ADD CONSTRAINT ... IN i001 failed. The answer from Madison Pruet, Celso Coimbra and Art Kagel: there's no IN clause on ALTER TABLE; first CREATE UNIQUE INDEX pk_detail ON detail(...) IN i001, then ALTER TABLE detail ADD CONSTRAINT PRIMARY KEY(...) CONSTRAINT pk_detail. Art added that an index created beforehand survives dropping the constraint (and dropping the index while the constraint exists just hides/renames it), and that index and constraint name spaces are separate. Resolved.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Informix 11.50.FC5 on Linux Redhat 4.6
So I am playing with ER, and I need to add a primary key to my table. The
problem is that I want the new key to be built in the index space, not with
the table, and I want to name it logically so I can find it.
So Here is the statement I tried, but it failed:
alter table detail add constraint primary key
(receipt,salesdate,sequence)constraint pk_detail in i001;
Where does the in clause go, or is that not an option for alter table?
And if I have to drop the primary key and redo it by first creating a create
unique index ... in i001; and then alter table to add primary key, how to do
drop that primary key with the funky " 1263_099884" name?
Thanks!
Kate Tomchik [ kate@iiug.org ] www.iiug.org
International Informix Users Group Board of Directors
A computer lets you make more mistakes faster than any invention in human
history - with the possible exceptions of handguns and tequila.
Mitch Ratliffe
you can create a unique index first - followed by the constraint
definition.
From: "kate" <kate@iiug.org>
To: ids@iiug.org
Date: 11/04/2010 12:21 PM
Subject: Adding a Primary Key [21857]
Sent by: ids-bounces@iiug.org
Informix 11.50.FC5 on Linux Redhat 4.6
So I am playing with ER, and I need to add a primary key to my table. The
problem is that I want the new key to be built in the index space, not with
the table, and I want to name it logically so I can find it.
So Here is the statement I tried, but it failed:
alter table detail add constraint primary key
(receipt,salesdate,sequence)constraint pk_detail in i001;
Where does the in clause go, or is that not an option for alter table?
And if I have to drop the primary key and redo it by first creating a
create
unique index ... in i001; and then alter table to add primary key, how to
do
drop that primary key with the funky " 1263_099884" name?
Thanks!
Kate Tomchik [ kate@iiug.org ] www.iiug.org
International Informix Users Group Board of Directors
A computer lets you make more mistakes faster than any invention in human
history - with the possible exceptions of handguns and tequila.
Mitch Ratliffe
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Try
Create unique index pk_detail on detail (receipt,salesdate,sequence) in i001;
alter table detail add constraint primary key
(receipt,salesdate,sequence)constraint pk_detail;
when you drop the constraint PK, the functional index will be droped too.
Take care about the foreign key referencing this PK, they will be dropped too.
Celso Cabral Coimbra
Administrador de Banco de Dados
ClearTech Ltda
"Trust at the heart of Communications"
Tel. (11) 3576-4509
-----Mensagem original-----
De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Em nome de kate
Enviada em: quinta-feira, 4 de novembro de 2010 15:21
Para: ids@iiug.org
Assunto: Adding a Primary Key [21857]
Informix 11.50.FC5 on Linux Redhat 4.6
So I am playing with ER, and I need to add a primary key to my table. The
problem is that I want the new key to be built in the index space, not with
the table, and I want to name it logically so I can find it.
So Here is the statement I tried, but it failed:
alter table detail add constraint primary key
(receipt,salesdate,sequence)constraint pk_detail in i001;
Where does the in clause go, or is that not an option for alter table?
And if I have to drop the primary key and redo it by first creating a create
unique index ... in i001; and then alter table to add primary key, how to do
drop that primary key with the funky " 1263_099884" name?
Thanks!
Kate Tomchik [ kate@iiug.org ] www.iiug.org
International Informix Users Group Board of Directors
A computer lets you make more mistakes faster than any invention in human
history - with the possible exceptions of handguns and tequila.
Mitch Ratliffe
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
You have to create the primary key unique index BEFORE creating the
constraint and put the IN clause on the CREATE UNIQUE INDEX statement. So:
CREATE UNIQUE INDEX pk_detail ON detail(receipt,salesdate,sequence) IN i001;
ALTER TABLE detail ADD CONSTRAINT PRIMARY KEY(receipt,salesdate,sequence)CONSTRAINT pk_detail;
The index and constraint names do not HAVE to have the same name, but they
can (indexes and constraints have separate name spaces) and I find naming
the index and constraint the same when possible helps developers and other
SQL novices to read and understand the DDL later on.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Thu, Nov 4, 2010 at 1:20 PM, kate <kate@iiug.org> wrote:
> Informix 11.50.FC5 on Linux Redhat 4.6
>
> So I am playing with ER, and I need to add a primary key to my table. The
> problem is that I want the new key to be built in the index space, not with
> the table, and I want to name it logically so I can find it.
>
> So Here is the statement I tried, but it failed:
>
> alter table detail add constraint primary key
> (receipt,salesdate,sequence)> constraint pk_detail in i001;
>
> Where does the in clause go, or is that not an option for alter table?
>
> And if I have to drop the primary key and redo it by first creating a
> create
> unique index ... in i001; and then alter table to add primary key, how to
> do
> drop that primary key with the funky " 1263_099884" name?
>
> Thanks!
> Kate Tomchik [ kate@iiug.org ] www.iiug.org
> International Informix Users Group Board of Directors
>
> A computer lets you make more mistakes faster than any invention in human
> history - with the possible exceptions of handguns and tequila.
> Mitch Ratliffe
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--0016e640d50699632004943dcf89
Celso,
You are incorrect. If you create the index BEFORE creating the constraint
and later drop the constraint then the index will NOT be dropped it will
survive. In addition, however, if you drop the index manually while the
constraint still exists, then the index will not actually be dropped, it
will just be renamed to a hidden index and will still exist to support the
constraint.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Thu, Nov 4, 2010 at 1:31 PM, Celso Cabral Coimbra <
ccoimbra@cleartech.com.br> wrote:
> Try
>
> Create unique index pk_detail on detail (receipt,salesdate,sequence) in> i001;
>
> alter table detail add constraint primary key
> (receipt,salesdate,sequence)> constraint pk_detail;
>
> when you drop the constraint PK, the functional index will be droped too.
>
> Take care about the foreign key referencing this PK, they will be dropped
> too.
>
> Celso Cabral Coimbra
> Administrador de Banco de Dados
> ClearTech Ltda
> "Trust at the heart of Communications"
> Tel. (11) 3576-4509
>
> -----Mensagem original-----
> De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Em nome de kate
> Enviada em: quinta-feira, 4 de novembro de 2010 15:21
> Para: ids@iiug.org
> Assunto: Adding a Primary Key [21857]
>
> Informix 11.50.FC5 on Linux Redhat 4.6
>
> So I am playing with ER, and I need to add a primary key to my table. The
> problem is that I want the new key to be built in the index space, not with
> the table, and I want to name it logically so I can find it.
>
> So Here is the statement I tried, but it failed:
>
> alter table detail add constraint primary key
> (receipt,salesdate,sequence)> constraint pk_detail in i001;
>
> Where does the in clause go, or is that not an option for alter table?
>
> And if I have to drop the primary key and redo it by first creating a
> create
> unique index ... in i001; and then alter table to add primary key, how to
> do
> drop that primary key with the funky " 1263_099884" name?
>
> Thanks!
> Kate Tomchik [ kate@iiug.org ] www.iiug.org
> International Informix Users Group Board of Directors
>
> A computer lets you make more mistakes faster than any invention in human
> history - with the possible exceptions of handguns and tequila.
> Mitch Ratliffe
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--0016361e7c68dc8ef504943df5b7
Art,
You didn't understand what I mean, at least you didn't understand my intention.
I told the same that you told, create the index first and after create the
constraint.
Of couse to create the index the constraint can't exist. Otherwise it will
return an error.
After that I made two comments
First comment:
when you drop the constraint PK, the functional index will be droped too.
--> related to the functional index "1263_099884" that is the kate's doubt,
that mean that kate do not need to worry about this index, because when she
drops the primary key this index would be dropped too.
Second comment:
> Take care about the foreign key referencing this PK, they will be dropped
Best regards,
Celso Cabral Coimbra
Administrador de Banco de Dados
ClearTech Ltda
"Trust at the heart of Communications"
Tel. (11) 3576-4509
-----Mensagem original-----
De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Em nome de Art Kagel
Enviada em: quinta-feira, 4 de novembro de 2010 16:02
Para: ids@iiug.org
Assunto: Re: Adding a Primary Key [21861]
Celso,
You are incorrect. If you create the index BEFORE creating the constraint
and later drop the constraint then the index will NOT be dropped it will
survive. In addition, however, if you drop the index manually while the
constraint still exists, then the index will not actually be dropped, it
will just be renamed to a hidden index and will still exist to support the
constraint.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Thu, Nov 4, 2010 at 1:31 PM, Celso Cabral Coimbra <
ccoimbra@cleartech.com.br> wrote:
> Try
>
> Create unique index pk_detail on detail (receipt,salesdate,sequence) in> i001;
>
> alter table detail add constraint primary key
> (receipt,salesdate,sequence)> constraint pk_detail;
>
> when you drop the constraint PK, the functional index will be droped too.
>
> Take care about the foreign key referencing this PK, they will be dropped
> too.
>
> Celso Cabral Coimbra
> Administrador de Banco de Dados
> ClearTech Ltda
> "Trust at the heart of Communications"
> Tel. (11) 3576-4509
>
> -----Mensagem original-----
> De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Em nome de kate
> Enviada em: quinta-feira, 4 de novembro de 2010 15:21
> Para: ids@iiug.org
> Assunto: Adding a Primary Key [21857]
>
> Informix 11.50.FC5 on Linux Redhat 4.6
>
> So I am playing with ER, and I need to add a primary key to my table. The
> problem is that I want the new key to be built in the index space, not with
> the table, and I want to name it logically so I can find it.
>
> So Here is the statement I tried, but it failed:
>
> alter table detail add constraint primary key
> (receipt,salesdate,sequence)> constraint pk_detail in i001;
>
> Where does the in clause go, or is that not an option for alter table?
>
> And if I have to drop the primary key and redo it by first creating a
> create
> unique index ... in i001; and then alter table to add primary key, how to
> do
> drop that primary key with the funky " 1263_099884" name?
>
> Thanks!
> Kate Tomchik [ kate@iiug.org ] www.iiug.org
> International Informix Users Group Board of Directors
>
> A computer lets you make more mistakes faster than any invention in human
> history - with the possible exceptions of handguns and tequila.
> Mitch Ratliffe
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--0016361e7c68dc8ef504943df5b7
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.