Results 1 to 6 of 6
  1. #1
    computerbum is offline Member
    Join Date
    Feb 2010
    Posts
    26
    Rep Power
    0

    Default help with database using java

    I am trying to alter my tables that I created in java using mysql in order to add foreign key constraints to my tables. I have tried many commands such as excecute, addbatch, excecuteupdate but nothing seems to be adding my foreign keys. I have googled the crap out this stuff and cannot find no help.

    This is what I have so far:

    Java Code:
      //Create tables//
    
                stmt.execute(
                        "Create table computerwarehouse.products ( " +
                        "product_num integer not null primary key auto_increment," +
                        "product_brand varchar(30) not null,"+
                        "prod_model varchar(30) not null,"+
                        "model_num integer not null,"+
                        "product_id varchar(20) not null);");
    
    
                stmt.execute(
                        "CREATE TABLE computerwarehouse.computer_models ("+
                        "model_num integer not null primary key auto_increment,"+
                        "type varchar(20) not null);"
                        );
    
    
    
                stmt.execute(
                        "CREATE TABLE computerwarehouse.product_details ("+
                        "product_id varchar(20) not null primary key,"+
                        "product_cost decimal,"+
                        "product_list decimal,"+
                        "product_qty integer);"
                        );
    
    // add foreign key constraints//
    
    
                stmt.addBatch(
                        "ALTER TABLE computerwarehouse.products"+
                        "ADD FOREIGN KEY (model_num) REFERENCES computer_models(model_num),"+
                        "ADD FOREIGN KEY (product_id) REFERENCES product_details(product_id);"
                        );

  2. #2
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    8

    Default

    Are you actually committing anything? Or just executing them?

    Edit: Also, per default, MySQL tables use the MyISAM engine, which doesn't have foreign keys. See the MySQL manual to find out how to designate the InnoDB engine, which does.

  3. #3
    computerbum is offline Member
    Join Date
    Feb 2010
    Posts
    26
    Rep Power
    0

    Default

    I want to commit this to my database.

    I am using mysql server 5.1
    I created this script file for by database using mysql and this works but when I program these very same statements in java everything works but the alter table statements.


    Java Code:
    /* delete tables in this order if they exist */
    DROP TABLE supply_details;
    DROP TABLE  order_details;
    DROP TABLE  products;
    DROP TABLE  customer_orders;
    DROP TABLE  supply_order;
    DROP TABLE  computer_models;
    DROP TABLE  employees;
    DROP TABLE  product_details;
    DROP TABLE  clients;
    DROP TABLE  suppliers;
    DROP TABLE  contact_info;
    DROP TABLE  location;
    
    /* Add all tables to the Computer Warehouse database */
    /*Create Products Table*/
    Create table products (
    product_num integer not null primary key auto_increment,
    product_brand varchar(30) not null,
    prod_model varchar(30) not null,
    model_num integer not null,
    product_id varchar(20) not null);
                                                                                                          
    /* Create the order_details table */
    CREATE TABLE order_details (
    Order_id varchar(20) unique not null,
    Order_item_num  integer unique not null auto_increment,
    Product_num integer not null,
    employee_num integer not null,
    Primary key (order_item_num, order_id));
    
    /* Create Computer_Models table */
    CREATE TABLE computer_models (
    model_num integer not null primary key auto_increment,
    type varchar(20) not null);
                  
    /* Create Product Details table */
    CREATE TABLE product_details (
    product_id varchar(20) not null primary key,
    product_cost decimal,
    product_list decimal,
    product_qty integer);
    
    
    /* Create Supply Details table */
    CREATE TABLE supply_details (
    details_id varchar(20) not null,
    supply_item_num integer  not null auto_increment,
    product_num integer not null,
    employee_num integer not null,
    primary key(supply_item_num, details_id));
    
    /* Create Supply Order table */
    CREATE TABLE supply_order (
    details_id varchar(20) not null primary key,
    invoice_date datetime not null,
    supplier_id varchar(20) not null);
    
    /* Create table Suppliers*/
    CREATE TABLE suppliers (
    supplier_id varchar(20) not null primary key,
    supplier_name varchar(30) not null,
    contact_num integer not null);
    
    /* Create table Contact information */
    CREATE TABLE contact_info(
    contact_num integer not null auto_increment primary key,
    email varchar(40) not null,
    phone varchar(12) not null,
    street_add varchar(50) not null,
    zipcode integer);
    
    /* Create table Contact location */
    CREATE TABLE location (
    zipcode integer not null primary key,
    city varchar(40) not null,
    state varchar(2) not null );
    
    /* Create table Clients */
    CREATE TABLE clients (
    client_id varchar(20) not null primary key,
    client_firstName varchar(30) not null,
    client_MI varchar(1),
    client_lastName varchar(30) not null,
    contact_num integer not null); 
    
    /* Create table Employees */
    CREATE TABLE employees (
    employee_num  integer not null auto_increment primary key,
    client_id varchar(20) not null);
    
    /* Create table Customer Orders */
    CREATE TABLE customer_orders (
    order_id varchar(20) not null primary key,
    customer_order_date datetime not null,
    client_id varchar(20) not null);
    
    /* Update all tables with their foreign key constraints 
         Set the foreign key constraints for the  order details table
         dependent upon the Products table and Employees table
    */
    ALTER TABLE order_details
    ADD FOREIGN KEY (product_num) REFERENCES products(product_num),
    ADD FOREIGN KEY (employee_num) REFERENCES employees(employee_num);
    
    /*Set the foreign key constraints for the  products table
        dependent upon the Computer Models table and Product details table
    */
    ALTER TABLE products
    ADD FOREIGN KEY (model_num) REFERENCES computer_models(model_num),
    ADD FOREIGN KEY (product_id) REFERENCES product_details(product_id);
    
    /*Set the foreign key constraints for the  supply details table
        dependent upon the Products  and Employees tables
    */
    ALTER TABLE supply_details
    ADD FOREIGN KEY (product_num) REFERENCES products(product_num),
    ADD FOREIGN KEY (employee_num) REFERENCES employees(employee_num);
    
    /*Set the foreign key constraints for the  Customer Order table
        dependent upon the Clients table
    */
    ALTER TABLE customer_orders
    ADD FOREIGN KEY (client_id) REFERENCES clients(client_id);
    
    /*Set the foreign key constraints for the  Supply Order table
        dependent upon the Suppliers table
    */
    ALTER TABLE supply_order
    ADD FOREIGN KEY (supplier_id) REFERENCES suppliers(supplier_id);
    
    /*Set the foreign key constraints for the  Employees table
        dependent upon the Clients table
    */
    ALTER TABLE employees
    ADD FOREIGN KEY (client_id) REFERENCES clients(client_id);
    
    /*Set the foreign key constraints for the Clients table
        dependent upon the Contact_info table
    */
    ALTER TABLE clients
    ADD FOREIGN KEY (contact_num) REFERENCES contact_info(contact_num);
    
    /*Set the foreign key constraints for the Contact Info table
        dependent upon the Location table
    */
    ALTER TABLE contact_info
    ADD FOREIGN KEY (zipcode) REFERENCES location(zipcode);
    
    /*Set the foreign key constraints for the Suppliers table
        dependent upon the Contact Info table
    */
    ALTER TABLE suppliers
    ADD FOREIGN KEY (contact_num) REFERENCES contact_info(contact_num);

  4. #4
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    8

    Default

    Well, like I said, are you commiting anything? Or just executing it? Check that your DB is configured to use InnoDB as the default engine, otherwise you will have to change those create table statements, and make sure to call commit on the connection after the commands are finished. And, describe, in detail, what "doesn't work" means. Are you getting exceptions, warnings, etc, etc, etc.

  5. #5
    computerbum is offline Member
    Join Date
    Feb 2010
    Posts
    26
    Rep Power
    0

    Default

    Yes I am getting an error:

    com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorEx ception: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FOREIGN KEY (product_num) REFERENCES products(product_num),ADD FOREIGN KEY (empl' at line 1
    I suppose its like you are saying about the innobdb. How do I check it to see if I am using the innobdb, I am very new to mysql?

  6. #6
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    8

    Default

    Don't remember, haven't used it in a long while. Ask at MySQL :: MySQL Forums, or, as I've already said, read the manual.

Similar Threads

  1. Java connecting to sql database
    By markious in forum New To Java
    Replies: 3
    Last Post: 04-14-2010, 09:58 PM
  2. Java Database Connections
    By javahelp00 in forum New To Java
    Replies: 6
    Last Post: 02-28-2009, 07:41 PM
  3. Java and database value printing
    By stefanybaez in forum Advanced Java
    Replies: 3
    Last Post: 11-29-2008, 04:35 AM
  4. MySQL Database and Java
    By shaggymac in forum Advanced Java
    Replies: 1
    Last Post: 05-01-2008, 09:01 PM
  5. PDA, Database, and Java
    By percivalwcy in forum CLDC and MIDP
    Replies: 0
    Last Post: 08-08-2007, 03:12 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •