Quantcast

Create table and index in one script

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Create table and index in one script

Keith Squier
I am having difficulty creating tables and indexes all in one script. A simple example is below.

CREATE TABLE A (
   id UUID NOT NULL,
   datetime TIMESTAMP NOT NULL,
   PRIMARY KEY (id));

CREATE INDEX a_datetime_idx ON A (datetime);

When I run both statements in a single transaction, I get errors that the table is not found. If I run them in separate transactions, everything works. Is it not possible to create tables and their indexes all in one transaction?

------------------------------------------------------------------------------
Developer Access Program for Intel Xeon Phi Processors
Access to Intel Xeon Phi processor-based developer platforms.
With one year of Intel Parallel Studio XE.
Training and support from Colfax.
Order your platform today. http://sdm.link/xeonphi
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Create table and index in one script

Fred Toussi-2
The statements are compiled before they are executed. The CREATE INDEX statements is compiled before the table is created therefore compilation fails due to the non-existent table.
 
It is possible to create a whole schema in one transaction, including all the constraints and indexes. See the Guide, Schemas and Database Objects chapter. In the example below, note the lack of semicolon at the end of inner statements and the single semicolon at the end:
 
 CREATE SCHEMA ACCOUNTS AUTHORIZATION DBA
   CREATE TABLE AB(A INTEGER)
   CREATE TABLE CD(C CHAR(10))
   CREATE INDEX IDX ON AB(A);
 
Fred Toussi
 
On Thu, Jan 12, 2017, at 23:35, Keith Squier wrote:
I am having difficulty creating tables and indexes all in one script. A simple example is below.
 
CREATE TABLE A (
   id UUID NOT NULL,
   datetime TIMESTAMP NOT NULL,
   PRIMARY KEY (id));
 
CREATE INDEX a_datetime_idx ON A (datetime);
 
When I run both statements in a single transaction, I get errors that the table is not found. If I run them in separate transactions, everything works. Is it not possible to create tables and their indexes all in one transaction?
------------------------------------------------------------------------------
Developer Access Program for Intel Xeon Phi Processors
Access to Intel Xeon Phi processor-based developer platforms.
With one year of Intel Parallel Studio XE.
Training and support from Colfax.
Order your platform today. http://sdm.link/xeonphi
_______________________________________________
Hsqldb-user mailing list

------------------------------------------------------------------------------
Developer Access Program for Intel Xeon Phi Processors
Access to Intel Xeon Phi processor-based developer platforms.
With one year of Intel Parallel Studio XE.
Training and support from Colfax.
Order your platform today. http://sdm.link/xeonphi
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Loading...