"Syntax error in CONSTRAINT clause" within Java code

[Amisha Shah]

Hello all,

I am doing simple java program that creates table in an existing MS Access database. Eventhough my DDL sql is perfectly correct, I am getting runtime error called "Syntax error in CONSTAINT clause" when I add ON UPDATE CASCADE, ON DELETE CASCADE. And also with the CHECK statements. My code is here below.

import java.sql.*;


class CreateTable {

Connection connection;
Statement statement;

public void loadDriver() throws ClassNotFoundException{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}

public void makeConnection() throws SQLException {
connection=
DriverManager.getConnection("jdbcdbc:StockTracker");
}

public void program() throws SQLException
{
statement = connection.createStatement();
String cmd = "CREATE TABLE StockTrades1( "+
"symbol VARCHAR(8) NOT NULL,"+
"userID VARCHAR(20) NOT NULL,"+
"transDateTime DATETIME NOT NULL,"+
"transType VARCHAR(6) NOT NULL,"+
"pricePerShare MONEY NOT NULL," +
"numShares INTEGER NOT NULL ,"+
"totalPrice MONEY NOT NULL"+
")";
statement.executeUpdate(cmd);
connection.commit();

cmd = "ALTER TABLE StockTrades1 "+
"ADD CONSTRAINT ST_CK CHECK (numShares > 0)";
statement.executeUpdate(cmd);
connection.commit();

cmd = "ALTER TABLE StockTrades1 "+
"ADD CONSTRAINT ST_PRI PRIMARY KEy(transDateTime,transType,userID,symbol)";
statement.executeUpdate(cmd);
connection.commit();

cmd = "ALTER TABLE StockTrades1 ADD CONSTRAINT ST_FK FOREIGN KEY(userID,symbol)"+
"REFERENCES UserStocks(userID,symbol)";
statement.executeUpdate(cmd);
connection.commit();
}
public void close() throws SQLException
{
connection.close();
}

public static void main(String args[])
{

CreateTable obj = new CreateTable();

try{
obj.loadDriver();
obj.makeConnection();
obj.program();
obj.close();
}
catch(Exception e)
{
e.printStackTrace();
}

}

}

I like to know that whether I can define foreign key with ON DELETE CASCADE, ON UPDATE CASCADE or not. I also like to know whether I can write CONSTRAINT CKECK clause or not. In addition to that I want to set DEFAULT values for all colums but that is also not workig.

Thanks to all in advance.
Amisha.