Forums Register Login

"Syntax error in CONSTRAINT clause" within Java code

+Pie Number of slices to send: Send
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("jdbc:odbc: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.
WHAT is your favorite color? Blue, no yellow, ahhhhhhh! Tiny ad:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com


reply
reply
This thread has been viewed 1991 times.
Similar Threads
How to retrieve detached @ManyToOne entities with relationship
Syntax error for Access Database
Hypersonic DB question
one to one unidirectional mapping in jpa
Updaing image field in Sql server
More...

All times above are in ranch (not your local) time.
The current ranch time is
Mar 28, 2024 11:52:26.