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.
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.