Creating a Primary Key constraint creates a new index

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

Creating a Primary Key constraint creates a new index

Vilius Vaivada
Hi all,

I'm using hsqldb 2.2.8. It seems like adding a primary key constraint via "alter table add constraint xxx primary key (column)" creates a new index on the table, "IDX". Is that expected behaviour? My understanding was it was only the case prior to 1.8 release? Is there any way around it? This makes my schema for HSQLDB look different to MySQL and Oracle ones...

Thanks a lot

------------------------------------------------------------------------------
Keep Your Developer Skills Current with LearnDevNow!
The most comprehensive online learning library for Microsoft developers
is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3,
Metro Style Apps, more. Free future releases when you subscribe now!
http://p.sf.net/sfu/learndevnow-d2d
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|

Re: Creating a Primary Key constraint creates a new index

Vilius Vaivada
I think what I'm uncomfortable with is the fact that the index is not of the  

On Wed, Feb 8, 2012 at 11:20 AM, Vilius Vaivada <[hidden email]> wrote:
Hi all,

I'm using hsqldb 2.2.8. It seems like adding a primary key constraint via "alter table add constraint xxx primary key (column)" creates a new index on the table, "IDX". Is that expected behaviour? My understanding was it was only the case prior to 1.8 release? Is there any way around it? This makes my schema for HSQLDB look different to MySQL and Oracle ones...

Thanks a lot


------------------------------------------------------------------------------
Keep Your Developer Skills Current with LearnDevNow!
The most comprehensive online learning library for Microsoft developers
is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3,
Metro Style Apps, more. Free future releases when you subscribe now!
http://p.sf.net/sfu/learndevnow-d2d
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|

Re: Creating a Primary Key constraint creates a new index

Vilius Vaivada
Sorry for the previous message.

I think the key here is that the index is not named "sys_idx_whatever" (which is the case if I specify the column to be primary key when creating the database).



On Wed, Feb 8, 2012 at 11:31 AM, Vilius Vaivada <[hidden email]> wrote:
I think what I'm uncomfortable with is the fact that the index is not of the  


On Wed, Feb 8, 2012 at 11:20 AM, Vilius Vaivada <[hidden email]> wrote:
Hi all,

I'm using hsqldb 2.2.8. It seems like adding a primary key constraint via "alter table add constraint xxx primary key (column)" creates a new index on the table, "IDX". Is that expected behaviour? My understanding was it was only the case prior to 1.8 release? Is there any way around it? This makes my schema for HSQLDB look different to MySQL and Oracle ones...

Thanks a lot



------------------------------------------------------------------------------
Keep Your Developer Skills Current with LearnDevNow!
The most comprehensive online learning library for Microsoft developers
is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3,
Metro Style Apps, more. Free future releases when you subscribe now!
http://p.sf.net/sfu/learndevnow-d2d
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|

Re: Creating a Primary Key constraint creates a new index

Vilius Vaivada
Follow-up: I tried adding a primary key constraint on an existing column via 

ALTER TABLE tableName ALTER COLUMN columnName VARCHAR(2) PRIMARY KEY

,but that just throws a parse exception for unexpected token (that seems to happen if I specify anything more than the datatype in my "alter column" statement (e.g. default, [not] null)).

On Wed, Feb 8, 2012 at 11:33 AM, Vilius Vaivada <[hidden email]> wrote:
Sorry for the previous message.

I think the key here is that the index is not named "sys_idx_whatever" (which is the case if I specify the column to be primary key when creating the database).



On Wed, Feb 8, 2012 at 11:31 AM, Vilius Vaivada <[hidden email]> wrote:
I think what I'm uncomfortable with is the fact that the index is not of the  


On Wed, Feb 8, 2012 at 11:20 AM, Vilius Vaivada <[hidden email]> wrote:
Hi all,

I'm using hsqldb 2.2.8. It seems like adding a primary key constraint via "alter table add constraint xxx primary key (column)" creates a new index on the table, "IDX". Is that expected behaviour? My understanding was it was only the case prior to 1.8 release? Is there any way around it? This makes my schema for HSQLDB look different to MySQL and Oracle ones...

Thanks a lot




------------------------------------------------------------------------------
Keep Your Developer Skills Current with LearnDevNow!
The most comprehensive online learning library for Microsoft developers
is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3,
Metro Style Apps, more. Free future releases when you subscribe now!
http://p.sf.net/sfu/learndevnow-d2d
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|

Re: Creating a Primary Key constraint creates a new index

Fred Toussi-2
In reply to this post by Vilius Vaivada
This index name will not persist after a SHUTDOWN and RESTART. The name will then be in the usual form SYS_IDX_MNOP..
 
But I will change the implementation to use the SYS_ name from start.
 
Fred
 
 
On Wed, Feb 8, 2012, at 11:33, Vilius Vaivada wrote:
Sorry for the previous message.
 
I think the key here is that the index is not named "sys_idx_whatever" (which is the case if I specify the column to be primary key when creating the database).
 


On Wed, Feb 8, 2012 at 11:31 AM, Vilius Vaivada <[hidden email]> wrote:
I think what I'm uncomfortable with is the fact that the index is not of the  


On Wed, Feb 8, 2012 at 11:20 AM, Vilius Vaivada <[hidden email]> wrote:
Hi all,
 
I'm using hsqldb 2.2.8. It seems like adding a primary key constraint via "alter table add constraint xxx primary key (column)" creates a new index on the table, "IDX". Is that expected behaviour? My understanding was it was only the case prior to 1.8 release? Is there any way around it? This makes my schema for HSQLDB look different to MySQL and Oracle ones...
 
Thanks a lot
------------------------------------------------------------------------------
Keep Your Developer Skills Current with LearnDevNow!
The most comprehensive online learning library for Microsoft developers
is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3,
Metro Style Apps, more. Free future releases when you subscribe now!
http://p.sf.net/sfu/learndevnow-d2d
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user

 

------------------------------------------------------------------------------
Keep Your Developer Skills Current with LearnDevNow!
The most comprehensive online learning library for Microsoft developers
is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3,
Metro Style Apps, more. Free future releases when you subscribe now!
http://p.sf.net/sfu/learndevnow-d2d
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|

Re: Creating a Primary Key constraint creates a new index

Vilius Vaivada
Hi Fred,

That was quick, thanks for that! When do you expect to be able to release the fix?

The restart route is not particularly relevant to us, the DB is only alive that one time.

Thanks again,
VIlius

On Wed, Feb 8, 2012 at 12:02 PM, Fred Toussi <[hidden email]> wrote:
This index name will not persist after a SHUTDOWN and RESTART. The name will then be in the usual form SYS_IDX_MNOP..
 
But I will change the implementation to use the SYS_ name from start.
 
Fred
 
 
On Wed, Feb 8, 2012, at 11:33, Vilius Vaivada wrote:
Sorry for the previous message.
 
I think the key here is that the index is not named "sys_idx_whatever" (which is the case if I specify the column to be primary key when creating the database).
 


On Wed, Feb 8, 2012 at 11:31 AM, Vilius Vaivada <[hidden email]> wrote:
I think what I'm uncomfortable with is the fact that the index is not of the  


On Wed, Feb 8, 2012 at 11:20 AM, Vilius Vaivada <[hidden email]> wrote:
Hi all,
 
I'm using hsqldb 2.2.8. It seems like adding a primary key constraint via "alter table add constraint xxx primary key (column)" creates a new index on the table, "IDX". Is that expected behaviour? My understanding was it was only the case prior to 1.8 release? Is there any way around it? This makes my schema for HSQLDB look different to MySQL and Oracle ones...
 
Thanks a lot
------------------------------------------------------------------------------
Keep Your Developer Skills Current with LearnDevNow!
The most comprehensive online learning library for Microsoft developers
is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3,
Metro Style Apps, more. Free future releases when you subscribe now!
http://p.sf.net/sfu/learndevnow-d2d
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user

 

------------------------------------------------------------------------------
Keep Your Developer Skills Current with LearnDevNow!
The most comprehensive online learning library for Microsoft developers
is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3,
Metro Style Apps, more. Free future releases when you subscribe now!
http://p.sf.net/sfu/learndevnow-d2d
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user



------------------------------------------------------------------------------
Keep Your Developer Skills Current with LearnDevNow!
The most comprehensive online learning library for Microsoft developers
is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3,
Metro Style Apps, more. Free future releases when you subscribe now!
http://p.sf.net/sfu/learndevnow-d2d
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|

Re: Creating a Primary Key constraint creates a new index

Fred Toussi-2
The fix will be committed within a couple of days. You can compile from SVN /trunk but there is no fixed date for the next release.
 
Fred
 
On Wed, Feb 8, 2012, at 12:24, Vilius Vaivada wrote:
Hi Fred,
 
That was quick, thanks for that! When do you expect to be able to release the fix?
 
The restart route is not particularly relevant to us, the DB is only alive that one time.
 
Thanks again,
VIlius

On Wed, Feb 8, 2012 at 12:02 PM, Fred Toussi <[hidden email]> wrote:
This index name will not persist after a SHUTDOWN and RESTART. The name will then be in the usual form SYS_IDX_MNOP..
 
But I will change the implementation to use the SYS_ name from start.
 
Fred
 
 
On Wed, Feb 8, 2012, at 11:33, Vilius Vaivada wrote:
Sorry for the previous message.
 
I think the key here is that the index is not named "sys_idx_whatever" (which is the case if I specify the column to be primary key when creating the database).
 


On Wed, Feb 8, 2012 at 11:31 AM, Vilius Vaivada <[hidden email]> wrote:
I think what I'm uncomfortable with is the fact that the index is not of the  


On Wed, Feb 8, 2012 at 11:20 AM, Vilius Vaivada <[hidden email]> wrote:
Hi all,
 
I'm using hsqldb 2.2.8. It seems like adding a primary key constraint via "alter table add constraint xxx primary key (column)" creates a new index on the table, "IDX". Is that expected behaviour? My understanding was it was only the case prior to 1.8 release? Is there any way around it? This makes my schema for HSQLDB look different to MySQL and Oracle ones...
 
Thanks a lot
------------------------------------------------------------------------------
Keep Your Developer Skills Current with LearnDevNow!
The most comprehensive online learning library for Microsoft developers
is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3,
Metro Style Apps, more. Free future releases when you subscribe now!
http://p.sf.net/sfu/learndevnow-d2d
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user

 

------------------------------------------------------------------------------
Keep Your Developer Skills Current with LearnDevNow!
The most comprehensive online learning library for Microsoft developers
is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3,
Metro Style Apps, more. Free future releases when you subscribe now!
http://p.sf.net/sfu/learndevnow-d2d
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
 
------------------------------------------------------------------------------
Keep Your Developer Skills Current with LearnDevNow!
The most comprehensive online learning library for Microsoft developers
is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3,
Metro Style Apps, more. Free future releases when you subscribe now!
http://p.sf.net/sfu/learndevnow-d2d
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user

 

------------------------------------------------------------------------------
Keep Your Developer Skills Current with LearnDevNow!
The most comprehensive online learning library for Microsoft developers
is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3,
Metro Style Apps, more. Free future releases when you subscribe now!
http://p.sf.net/sfu/learndevnow-d2d
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|

Re: Creating a Primary Key constraint creates a new index

Fred Toussi-2
In reply to this post by Vilius Vaivada
This syntax is no longer supported.
 
Use ALTER TABLE .. ADD CONSTRAINT or ALTER TABLE .. ADD PRIMARY KEY
 
To change the DEFAULT use the ALTER .. ALTER COLUMN ... ADD DEFAULT etc.
 
Please read the Guide.
 
Fred
 
On Wed, Feb 8, 2012, at 11:59, Vilius Vaivada wrote:
Follow-up: I tried adding a primary key constraint on an existing column via 
 
ALTER TABLE tableName ALTER COLUMN columnName VARCHAR(2) PRIMARY KEY

,but that just throws a parse exception for unexpected token (that seems to happen if I specify anything more than the datatype in my "alter column" statement (e.g. default, [not] null)).

On Wed, Feb 8, 2012 at 11:33 AM, Vilius Vaivada <[hidden email]> wrote:
Sorry for the previous message.
 
I think the key here is that the index is not named "sys_idx_whatever" (which is the case if I specify the column to be primary key when creating the database).
 


On Wed, Feb 8, 2012 at 11:31 AM, Vilius Vaivada <[hidden email]> wrote:
I think what I'm uncomfortable with is the fact that the index is not of the  


On Wed, Feb 8, 2012 at 11:20 AM, Vilius Vaivada <[hidden email]> wrote:
Hi all,
 
I'm using hsqldb 2.2.8. It seems like adding a primary key constraint via "alter table add constraint xxx primary key (column)" creates a new index on the table, "IDX". Is that expected behaviour? My understanding was it was only the case prior to 1.8 release? Is there any way around it? This makes my schema for HSQLDB look different to MySQL and Oracle ones...
 
Thanks a lot
------------------------------------------------------------------------------
Keep Your Developer Skills Current with LearnDevNow!
The most comprehensive online learning library for Microsoft developers
is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3,
Metro Style Apps, more. Free future releases when you subscribe now!
http://p.sf.net/sfu/learndevnow-d2d
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user

 

------------------------------------------------------------------------------
Keep Your Developer Skills Current with LearnDevNow!
The most comprehensive online learning library for Microsoft developers
is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3,
Metro Style Apps, more. Free future releases when you subscribe now!
http://p.sf.net/sfu/learndevnow-d2d
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user