title: sql code for firebird doesn't compile
body: <p>I am getting strange errors while trying to create a simple database using isql tools from the Firebird package.</p>
  
  <p>The same code for creating a table works for other tables with other names.
  I've tried with and without quotes surrounding fields and table names, no success.</p>
  
  <p>It is Firebird 2.5 server version.</p>
  
  <p>The code I'm trying to execute:</p>
  
  <pre><code>SET SQL DIALECT 3;
  SET NAMES UTF8;
  CREATE DATABASE 'localhost:C:\fuzzdb.fdb' user 'SYSDBA' password 'masterkey'
  DEFAULT CHARACTER SET UTF8;
  
  CREATE TABLE RULES (
  RULE_ID INTEGER NOT NULL,
  IF_FUZZY SMALLINT,
  CONSTRAINT PK_RULE_ID
  PRIMARY KEY (RULE_ID),
  );
  
  CREATE TABLE VARS (
  VAR_ID INTEGER NOT NULL,
  VRULE_ID INTEGER,
  INPOUTP SMALLINT,
  RANGE_STRT INTEGER,
  RANGE_END INTEGER,
  VAR_NAME VARCHAR(40),
  FUZ_SET INTEGER,
  CONSTRAINT PK_VAR_ID
  PRIMARY KEY (VAR_ID)
  );
  
  CREATE TABLE FUZZSETS (
  FS_ID INTEGER NOT NULL,
  FS_NAME VARCHAR(40),
  INPOUTP SMALLINT,
  PAR1 FLOAT,
  PAR2 FLOAT,
  PAR3 FLOAT,
  PAR4 FLOAT,
  PAR5_HEDGE INTEGER,
  FUZ_SET INTEGER,
  CONSTRAINT PK_FS_ID
    PRIMARY KEY (FS_ID)
  );
  
  CREATE TABLE FRULES (
  FRULE_ID INTEGER NOT NULL,
  RULE_ID INTEGER,
  VAR_ID INTEGER,
  FS_ID INTEGER,
  INPOUTP SMALLINT,  
  CONSTRAINT PK_FRULE_ID
  PRIMARY KEY (FRULE_ID)
  );
  
  CREATE TABLE LINK_RV (
  LINK_RULES INTEGER,
  LINK_VARS INTEGER,
  CONSTRAINT FK_LINK_RV
  PRIMARY KEY (LINK_RULES, LINK_VARS)
  );
  
  CREATE TABLE LINK_VARFS (
  LINK_VRS INTEGER,
  LINK_FS INTEGER,
  CONSTRAINT FK_LINK_VARFS
  PRIMARY KEY (LINK_VRS, LINK_FS)
  );
  
  CREATE TABLE LINK_RLVR (
  LINK_RULE INTEGER NOT NULL,
  LINK_VR INTEGER NOT NULL,
  CONSTRAINT FK_LINK_RLVR
  PRIMARY KEY (LINK_RULE, LINK_VR)
  );
  
  CREATE TABLE LINK_FRL_RL (      
  LINK_FRULE INTEGER NOT NULL,
  LINK_RULE INTEGER NOT NULL,
  CONSTRAINT FK_LINK_FRL_RL
  PRIMARY KEY (LINK_FRULE, LINK_RULE)
  );
  
  CREATE TABLE LINK_FRL_VAR (
  LINK_FRULE INTEGER NOT NULL,
  LINK_VAR INTEGER NOT NULL,
  CONSTRAINT FK_LINK_FRL_VAR
  PRIMARY KEY (LINK_FRULE, LINK_VAR)
  );
  
  CREATE TABLE LINK_FRL_FS (
  LINK_FSRULE INTEGER NOT NULL,
  LINK_FS INTEGER NOT NULL,
  CONSTRAINT FK_LINK_FRL_FS
  PRIMARY KEY (LINK_FRULE, LINK_FS)
  );
  
  ALTER TABLE LINK_FRL_FS
  ADD CONSTRAINT FK_LINK_FSRULE
  FOREIGN KEY(LINK_FSRULE)
  REFERENCES FRULES(FRULE_ID);
  
  ALTER TABLE LINK_FRL_FS
  ADD CONSTRAINT FK_LINK_FS
  FOREIGN KEY(LINK_FS)
  REFERENCES FUZZSETS(FS_ID);
  
  ALTER TABLE LINK_FRL_VAR
  ADD CONSTRAINT FK_LINK_FRULE
  FOREIGN KEY(LINK_FRULE)
  REFERENCES FRULES(FRULE_ID);
  
  ALTER TABLE LINK_FRL_VAR
  ADD CONSTRAINT FK_LINK_VAR
  FOREIGN KEY(LINK_VAR)
  REFERENCES FUZZSETS(VAR_ID);
  
  ALTER TABLE LINK_FRL_RL
  ADD CONSTRAINT FK_LINK_FRULE
  FOREIGN KEY(LINK_FRULE)
  REFERENCES FRULES(FRULE_ID);
  
  ALTER TABLE LINK_FRL_RL
  ADD CONSTRAINT FK_LINK_RULE
  FOREIGN KEY(LINK_RULE)
  REFERENCES RULES(RULE_ID);
  
  CREATE GENERATOR GEN_RULE_ID;
  CREATE GENERATOR GEN_VAR_ID;
  CREATE GENERATOR GEN_FS_ID;
  CREATE GENERATOR GEN_FRULE_ID;
  
  SET TERM ^ ;
  
  CREATE TRIGGER BI_RULES FOR RULES
  ACTIVE BEFORE INSERT POSITION 0
  AS
  BEGIN
  IF (NEW.RULE_ID IS NULL) THEN
    NEW.RULE_ID = GEN_ID(GEN_RULE_ID, 1);
  END^
  
  CREATE TRIGGER BI_VARS FOR VARS
  ACTIVE BEFORE INSERT POSITION 0
  AS
  BEGIN
  IF (NEW.VAR_ID IS NULL) THEN
    NEW.VAR_ID = GEN_ID(GEN_VAR_ID, 1);
  END^
  
  CREATE TRIGGER BI_FUZZSETS FOR FUZZSETS
  ACTIVE BEFORE INSERT POSITION 0
  AS
  BEGIN
  IF (NEW.FS_ID IS NULL) THEN
    NEW.FS_ID = GEN_ID(GEN_FS_ID, 1);
  END^
  
  CREATE TRIGGER BI_FRULES FOR FRULES
  ACTIVE BEFORE INSERT POSITION 0
  AS
  BEGIN
  IF (NEW.FRULE_ID IS NULL) THEN
    NEW.FRULE_ID = GEN_ID(GEN_FRULE_ID, 1);
  END^
  
  SET TERM ; ^
  COMMIT;
  </code></pre>
  
  <hr>
  
  <p>The output from the isql commmand:</p>
  
  <pre><code>Use CONNECT or CREATE DATABASE to specify a database
  Statement failed, SQLSTATE = 42000
  Dynamic SQL Error
  -SQL error code = -104
  -Token unknown - line 6, column 3
  -)
  At line 10 in file c:\fdb.sql
  Statement failed, SQLSTATE = 42000
  unsuccessful metadata update
  -Unknown columns in index FK_LINK_FRL_FS
  After line 82 in file c:\fdb.sql
  Statement failed, SQLSTATE = 42S02
  Dynamic SQL Error
  -SQL error code = -204
  -Table unknown
  -LINK_FRL_FS
  -At line 1, column 13.
  After line 89 in file c:\fdb.sql
  Statement failed, SQLSTATE = 42S02
  Dynamic SQL Error
  -SQL error code = -204
  -Table unknown
  -LINK_FRL_FS
  -At line 1, column 13.
  After line 94 in file c:\fdb.sql
  Statement failed, SQLSTATE = 42000
  unsuccessful metadata update
  -could not find UNIQUE or PRIMARY KEY constraint in table FUZZSETS with specifie
  d columns
  After line 104 in file c:\fdb.sql
  Statement failed, SQLSTATE = 42S11
  unsuccessful metadata update
  -Index FK_LINK_FRULE already exists
  After line 109 in file c:\fdb.sql
  Statement failed, SQLSTATE = 42000
  unsuccessful metadata update
  -Table RULES not found
  After line 114 in file c:\fdb.sql
  Statement failed, SQLSTATE = 42S02
  Dynamic SQL Error
  -SQL error code = -204
  -Table unknown
  -RULES
  -At line 1, column 29
  At line 130 in file c:\fdb.sql
  </code></pre>
  
  <hr>
  
  <p>I don't get why it's impossible to create the first table "RULES" although the commands are similar to other tables.
  Even without all the triggers and foreign keys (alter table..) I am getting at leaast the last error.
  it says "Unknown columns in index FK_LINK_FRL_FS" but no mention of other similar indexing tables.</p>
  
  <p>I am just starting working with databases and it could be that I missed or mixed something,
  but I tried to compile with too many changes and still getting errors.
  I've found more or less similar code here</p>
  
  <p><a href="http://sergworks.wordpress.com/category/firebird/" rel="nofollow">http://sergworks.wordpress.com/category/firebird/</a></p>
  
  <p>and I was able to compile it without problems.
  Could somebody point me in the right direction or show how it is done in another way?</p>