help creating schema sql for hsqldb

I am quite new to database programming and am trying to create a java program to access hsqldb(version2.2.5) using hibernate.I have 3 classes in my program which I mapped to 3 tables as given below.

I wanted to create a one-to-one mapping between SaleOrder and Bill. Also, to create a schema for the db, I tried sql create statements. The two tables SALEORDER and BILL have each other's id as a FK.To avoid dependency error between tables during create,I used alter table add constraint statements

Now,I want to use drop table statements at the beginning of script.I used the following

ALTER TABLE SALEORDER DROP CONSTRAINT FK_SO_BILL;
ALTER TABLE SALEORDER DROP CONSTRAINT FK_SO_BUYER;
ALTER TABLE BILL DROP CONSTRAINT FK_BILL_SO;
ALTER TABLE BILL DROP CONSTRAINT FK_BILL_BUYER;

DROP TABLE BUYER IF EXISTS;
DROP TABLE SALEORDER IF EXISTS;
DROP TABLE BILL IF EXISTS;

This however, causes problem when run for the first time(since the to be altered tables don't exist.).I could not find 'IF EXISTS' clause for ALTER TABLE in hsqldb ..So what is the solution?Should I run the create table scripts alone the first time,and add the alter table,drop table statements shown above thereafter?That doesn't sound a clean way.

will be grateful for your suggestions,

sincerely,

Jim

The main schema script is

CREATE TABLE BUYER(
   BUYER_ID BIGINT NOT NULL PRIMARY KEY IDENTITY,
   NAME VARCHAR(100)
);

CREATE TABLE SALEORDER(
   SALEORDER_ID开发者_开发知识库 BIGINT NOT NULL PRIMARY KEY IDENTITY,
   BUYER_ID BIGINT NOT NULL,
   BILL_ID BIGINT,

);

CREATE TABLE BILL(
  BILL_ID BIGINT NOT NULL PRIMARY KEY IDENTITY,
  BUYER_ID BIGINT NOT NULL,
  SALEORDER_ID BIGINT NOT NULL,

    );
ALTER TABLE SALEORDER ADD CONSTRAINT FK_SO_BILL FOREIGN KEY(BILL_ID) REFERENCES BILL(BILL_ID);
ALTER TABLE SALEORDER ADD CONSTRAINT FK_SO_BUYER FOREIGN KEY(BUYER_ID) REFERENCES BUYER(BUYER_ID);
ALTER TABLE BILL ADD CONSTRAINT FK_BILL_BUYER FOREIGN KEY(BUYER_ID) REFERENCES BUYER(BUYER_ID);
ALTER TABLE BILL ADD CONSTRAINT FK_BILL_SO FOREIGN KEY(SALEORDER_ID) REFERENCES SALEORDER(SALEORDER_ID);

Also,I am using an ant target to create schema

<target name="createschema" description="execute schema">
            <sql
                    driver="${db.driver}"
                    url="${db.url}"
                    userid="${db.username}"
                    password="${db.password}"
                    print="yes"
                    src="${dir.schema}/${file.schema}"
                    caching="false"
                    showheaders="true"
                    >
                <classpath>
                    <path location="${dir.lib}/hsqldb.jar"/>
                </classpath>
            </sql>
        </target>

---

You don't need the ALTER TABLE statements before dropping the tables.

Use the CASCADE keyword to force drop the foreign key constraints.

DROP TABLE BUYER IF EXISTS CASCADE
DROP TABLE SALEORDER IF EXISTS CASCADE
DROP TABLE BILL IF EXISTS CASCADE

Can also use

DROP SCHEMA PUBLIC CASCADE

Which drops all existing objects in the schema.