[MySQL] ON UPDATE CASCADE ON DELETE CASCADE Problem

[SwingTänzer]

Moin,
möchte gerne diesen SQL Befehl in meine MYSQL Datenbank per MySQLConnector (Java) schicken. Leider klappt das nicht so recht. Verbindung usw. steht. Es geht wirklich nur um das Statement. Kann mir da jemand vielleicht weiter helfen?

CREATE TABLE ApplicationServer
(
  Name              VARCHAR(125) NOT NULL,
  Host              VARCHAR(256) NOT NULL,
  AppServerStatus   INTEGER NOT NULL REFERENCES
  AppServerStatus ON UPDATE CASCADE ON DELETE CASCADE,
  PRIMARY KEY (Name)
);

Habe das bislang so umgemodelt....

public boolean createTableApplicationServer() {
        String sql = "CREATE TABLE applicationserver("
                + "Name VARCHAR(125) NOT NULL," + "Host VARCHAR(255) NOT NULL,"
                + "appserverstatus INTEGER NOT NULL REFERENCES,"
                //+ "appserverstatus CASCADE ON DELETE,"
                //+ "appserverstatus CASCADE ON UPDATE,"
                + "PRIMARY KEY (Name))";
        return createTable("applicationserver", sql);
    }

Sobald ich die auskommentierten Zeilen reinmache, kommt dies hier:

Syntax error or access violation,  message from server: "You have an error in yo
ur SQL syntax.  Check the manual that corresponds to your MySQL server version f
or the right syntax to use near 'appserverstatus CASCADE ON DELETE,appserverstat
us CASCADE ON UP"

Weis vielleicht jemand Rat?

Greetz SwingTänzer

 

[Thomas Darimont]

Hallo!

Sieht die Syntax nicht ein wenig anders aus?

[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name, ...)
    [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
    [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

Beispiel:

CREATE TABLE product (category INT NOT NULL, id INT NOT NULL,
                      price DECIMAL,
                      PRIMARY KEY(category, id)) TYPE=INNODB;
CREATE TABLE customer (id INT NOT NULL,
                      PRIMARY KEY (id)) TYPE=INNODB;
CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT,
                      product_category INT NOT NULL,
                      product_id INT NOT NULL,
                      customer_id INT NOT NULL,
                      PRIMARY KEY(no),
                      INDEX (product_category, product_id),
                      FOREIGN KEY (product_category, product_id)
                        REFERENCES product(category, id)
                        ON UPDATE CASCADE ON DELETE RESTRICT,
                      INDEX (customer_id),
                      FOREIGN KEY (customer_id)
                        REFERENCES customer(id)) TYPE=INNODB;

Gruß Tom

 

[SwingTänzer]

jo, danke, keine Ahnung was meine Quelle da für ne Syntax benutzt...

egal nu klappts, nachdem ich mir dein Beispiel angeguckt habe.:

String sql = "CREATE TABLE applicationserver("
                + "Name VARCHAR(125) NOT NULL," 
                + "Host VARCHAR(255) NOT NULL,"
                + "AppServerStatus INTEGER NOT NULL,"
                + "PRIMARY KEY(Name),"
                + "INDEX (AppServerStatus)," 
                + "FOREIGN KEY (AppServerStatus_ID) "
                + "REFERENCES appserverstatus(ID) ON UPDATE CASCADE ON DELETE CASCADE"
                + ")";

Danke!