• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

"Syntax error in CONSTRAINT clause" within Java code

 
Ranch Hand
Posts: 33
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic