Search code examples
sqldb2firebirdcreate-table

Firebird/DB2 - referencing column that is declared later


Let's assume we have trivial code like:

CREATE TABLE tab(
  id INT NOT NULL PRIMARY KEY,
  CONSTRAINT greater_than_10 CHECK (id > 10)
);

A table with single column and constraint was defined. Now if we switch the order:

CREATE TABLE tab(
  CONSTRAINT greater_than_10 CHECK (id > 10),
  id INT NOT NULL PRIMARY KEY
);

unsuccessful metadata update CREATE TABLE TAB failed Dynamic SQL Error SQL error code = -206 Column unknown ID

or:

[IBM][CLI Driver][DB2/LINUXX8664] SQL0205N Column, attribute, or period "ID" is not defined in "FIDDLE_QSNXRANMEEPHZPWEWQDV.TAB". SQLSTATE=42703 SQLCODE=-205

db<>fiddle demo - Firebird

db<>fiddle demo - DB2

This behaviour does not exists in any other major RDBMS:

db<>fiddle demo - Oracle - db<>fiddle demo - SQL Server - db<>fiddle demo - PostgreSQL

EDIT:

It is also applicable for foreign keys that reference the same table:

-- here I could define columns in any order, neat feature
CREATE TABLE comments(
  comment_id INT REFERENCES comments(id),
  id INT PRIMARY KEY
);

db<>fiddle demo - FK

The idea of refering objects that does not exist is present for instance: T-SQL stored procedures deferred name resolution or C/C++ forward declarations.

EDIT 2:

This behaviour is even applicalbe for calculated columns:

-- SQL Server
CREATE TABLE t(
   col AS (id + 1),
   id INT
);

-- PostgreSQL 12
CREATE TABLE t(
   col INT GENERATED ALWAYS AS (id + 1) STORED,
  id INT
);

-- Oracle/MySQL 8.0/MariaDB 10.4
CREATE TABLE t(
  col INT AS (id + 1),
  id INT
);

Searching for official documentation/ISO that states if specific order of defining entries at the same level is mandatory.


Solution

  • As far as I can tell, at a quick glance, the SQL standard (ISO/IEC 9075-2:2016) doesn't say anything about it. The wording in 11.3 <table definition> is not specific enough to say conclusively if it is allowed or not, or even required.