Search code examples
sqlinformix

SQL Informix - adding foreign key

I am struggling while creating new database tables with foreign keys in Informix DB.

I need to migrate from one database to another. I am using DBeaver as DB browsing and editing tool. I am trying to run script like this (I just changed names). The commands come from DBeaver DDL export itself, so this is how it should be.

DROP TABLE IF EXISTS eapp:test_table1;
CREATE TABLE eapp:test_table1 (
    column1 smallint NOT NULL,
    PRIMARY KEY (column1) CONSTRAINT table1_pk
);

DROP TABLE IF EXISTS eapp:test_table2;
CREATE TABLE eapp:test_table2 (
    column1 smallint NOT NULL,
    column2 varchar(100) NOT NULL,
    column3 smallint NOT NULL,
    column4 smallint,
    PRIMARY KEY (column1) CONSTRAINT table2_pk,
    FOREIGN KEY (column4) REFERENCES eapp:test_table1(column1) ON DELETE RESTRICT ON UPDATE RESTRICT CONSTRAINT table2_fk
);

However, execution keeps crashing with a weird trimmed error message:

SQL execution error message

The language is Czech and it says something like "Syntax error. (Important notice: Database servers, that". But the rest is trimmed even in detail, so I have no idea what is going on.

Full stack:

org.jkiss.dbeaver.model.sql.DBSQLException: SQL Error [42000]: Chyba syntaxe. (Důležitá poznámka: Databázové servery, které
    at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:134)
    at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeStatement(SQLQueryJob.java:473)
    at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.lambda$0(SQLQueryJob.java:412)
    at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:148)
    at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeSingleQuery(SQLQueryJob.java:405)
    at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.extractData(SQLQueryJob.java:865)
    at org.jkiss.dbeaver.ui.editors.sql.SQLEditor$QueryResultsContainer.readData(SQLEditor.java:2833)
    at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.lambda$0(ResultSetJobDataRead.java:98)
    at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:148)
    at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:96)
    at org.jkiss.dbeaver.ui.controls.resultset.ResultSetViewer$17.run(ResultSetViewer.java:3356)
    at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:103)
    at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)
Caused by: java.sql.SQLException: Chyba syntaxe. (Důležitá poznámka: Databázové servery, které
    at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:413)
    at com.informix.jdbc.IfxSqli.a(IfxSqli.java:3537)
    at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3853)
    at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2653)
    at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2569)
    at com.informix.jdbc.IfxSqli.executeExecute(IfxSqli.java:2446)
    at com.informix.jdbc.IfxSqli.executeExecute(IfxSqli.java:2421)
    at com.informix.jdbc.IfxResultSet.b(IfxResultSet.java:393)
    at com.informix.jdbc.IfxStatement.a(IfxStatement.java:1326)
    at com.informix.jdbc.IfxStatement.executeImpl(IfxStatement.java:1296)
    at com.informix.jdbc.IfxStatement.c(IfxStatement.java:1007)
    at com.informix.jdbc.IfxStatement.execute(IfxStatement.java:893)
    at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.execute(JDBCStatementImpl.java:338)
    at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:131)
    ... 12 more
Caused by: java.sql.SQLException
    at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:413)
    at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3858)
    ... 23 more

I was unable to Google message like that, so I am trying to ask here, if anyone has some experience with Informix DB and could possibly give me some hints.

I already tried to remove FK declaration from table creation and add it afterwards via ALTER TABLE, but it yields the same...

Solution

  • DBeaver is not generating valid Informix DDL. Informix does not understand ON DELETE RESTRICT ON UPDATE RESTRICT syntax.

    From the IBM online manual (Version 12.10):

    REFERENCES Clause

|--REFERENCES--table--+------------------+---------------------->
                      |    .-,------.    |   
                      |    V        |    |   
                      '-(----column-+--)-'   

>--+-------------------------+----------------------------------|
   |  (1)                    |   
   '-------ON DELETE CASCADE-'