Forum: Building VoltDB Applications

Post: How to create voltdb schema for more than one database table using 1 .sql file

How to create voltdb schema for more than one database table using 1 .sql file
kamalkishore
Jan 13, 2014
Hi All,

I have just started using Voltdb and trying to understand it fully.
I have a use case where I would be creating lot of sql tables and I am using command
Voltdb compile -o <schema-name.jar> <schema-name.sql> to create the tables but when I do this I am not able to see any previously created tables using voltdb UI ( http://localhost:8080 ).

If I want to create more than one tables do I need to include them in one single .sql file ???.

In my usecase, I need to create lot of tables and these tables are dependent on each other.
An example is given below

CREATE TABLE contract_type
(
id integer NOT NULL,
CONSTRAINT temp_contract_type PRIMARY KEY (id),
);


CREATE TABLE contract_sub_type
(
id integer NOT NULL,
contract_type_id integer NOT NULL,
CONSTRAINT temp_contract_sub_type PRIMARY KEY (id),
CONSTRAINT temp_contract_sub_type_contract_type_1 FOREIGN KEY (contract_type_id)
REFERENCES contract_type (id) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT
);

If I change the order in .sql file and put "contract_sub_type" before ""contract_type", I am getting an error saying
ERROR: [null:2]: DDL Error: "user lacks privilege or object not found: CONTRACT_TYPE in statement [CREATE TABLE contract_sub_type ( id integer NOT NULL, name character varying(256) NOT NULL, contract_type_id integer NOT NULL, active tinyint DEFAULT 0 NOT NULL, date_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, date_modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, revision_no integer DEFAULT 1 NOT NULL, last_modified_by integer DEFAULT 1 NOT NULL, CONSTRAINT pk_contract_sub_type PRIMARY KEY (id), /*CONSTRAINT fk_contract_sub_type_app_user_1 FOREIGN KEY (last_modified_by) REFERENCES app_user (id) MATCH SIMPLE ON UPDATE RESTRICT ON DELETE RESTRICT, */ CONSTRAINT fk_contract_sub_type_contract_type_1 FOREIGN KEY (contract_type_id) REFERENCES contract_type (id) MATCH SIMPLE ON UPDATE RESTRICT ON DELETE RESTRICT )]" in statement starting on lineno: 2
ERROR: Failed to compile XML
ERROR: Catalog compilation failed.
------------------------------------------
Project compilation failed. See log for errors.
------------------------------------------
ERROR: Command "/usr/lib/jvm/java-6-openjdk-amd64/bin/java ..." failed with return code 65280.

FATAL: Exiting.
""

As I have lot of circular dependencies between my sql tables, please let me know how to compile these tables.

Rgds,
Kamal
bballard
Jan 13, 2014
Hi Kamal,

You can declare multiple CREATE TABLE statements in one DDL .sql file. Typically a simple schema will be in one file, but you can use multiple files as in the examples here:
http://voltdb.com/docs/UsingVoltDB/clivoltdb.php

The issue you're running into is that VoltDB currently (as of 4.0) does not support Foreign Key declarations. VoltDB supports primary key constraints, and secondary unique indexes, which should include the partitioning key.

You can implement referential integrity enforcement manually in stored procedures. There is an example of this in the vote.java procedure in the voter example app:
https://github.com/VoltDB/voltdb/blob/master/examples/voter/src/voter/procedures/Vote.java#L64

-Ben