Firebird CREATE TABLE Guide in isql and DbSchema

A new Firebird database has only its system tables, so there is nowhere to put a row yet. CREATE TABLE makes one in a single statement: the table's name, each column with a data type, and a primary key so that every row can be told apart. This is a complete first table:

CREATE TABLE STUDENTS (
  ID INTEGER NOT NULL,
  NAME VARCHAR(50) NOT NULL,
  BIRTH_DATE DATE,
  GRADE SMALLINT,
  CONSTRAINT PK_STUDENTS PRIMARY KEY (ID)
);

You need a running Firebird server and a database to connect to. If the database is still missing, start with how to create a Firebird database. The statements follow the Firebird 5.0 Language Reference, and the output below was printed by a Firebird 4.0 server, which accepts the same statements.

Create the table in isql, step by step

isql is the command-line client that comes with Firebird.

1. Open a terminal and go to the directory that holds isql. On Windows that is the Firebird installation directory; on Linux it is usually /opt/firebird/bin.

2. Start isql with the database, the user and the password. On Linux, type ./isql instead of isql, because the shell doesn't look for commands in the current directory:

   isql localhost:/var/lib/firebird/data/school.fdb -u SYSDBA -p masterkey
   

   • localhost is the server. For a database on another machine, put its host name or IP address there.
   • /var/lib/firebird/data/school.fdb is the database file on that server. On Windows it looks like C:\data\school.fdb.
   • SYSDBA is the user, and masterkey stands for the password that your installation set.

   isql answers with its SQL> prompt.

3. Type the CREATE TABLE statement from the top of this page. If you press Enter before the closing semicolon, isql shows CON> and waits for the rest; the semicolon runs the statement.

4. Check that the table is there. SHOW TABLES lists the tables in the database, and SHOW TABLE with a table name lists its columns and constraints:

   SQL> SHOW TABLES;
   STUDENTS
   
   SQL> SHOW TABLE STUDENTS;
   ID                              INTEGER Not Null
   NAME                            VARCHAR(50) Not Null
   BIRTH_DATE                      DATE Nullable
   GRADE                           SMALLINT Nullable
   CONSTRAINT PK_STUDENTS:
     Primary key (ID)
   

   SELECT * FROM STUDENTS; prints nothing yet, because the table has no rows. That it runs without an error is proof enough that the table exists.

You never typed COMMIT for the table. isql starts with SET AUTODDL on, so it commits every CREATE, ALTER and DROP you type as soon as the statement succeeds. The rows you add later aren't committed that way, as the section on rows shows.

What each part of the statement decides

A name written without quotes is stored in uppercase, so CREATE TABLE Students makes a table called STUDENTS, and a query can spell it students or Students. Double quotes keep the case exactly, and from then on every query has to repeat them:

CREATE TABLE "Rooms" (ID INTEGER);
SELECT * FROM Rooms;

Statement failed, SQLSTATE = 42S02
Dynamic SQL Error
-SQL error code = -204
-Table unknown
-ROOMS
-At line 1, column 15

The unquoted Rooms became ROOMS, which isn't the table's name. Write names without quotes and this never comes up. A name can be up to 63 characters long.

A table name is unique in the database. A second CREATE TABLE STUDENTS fails and leaves the first table as it was:

Statement failed, SQLSTATE = 42S01
unsuccessful metadata update
-CREATE TABLE STUDENTS failed
-Table STUDENTS already exists

Firebird 5.0 has no CREATE TABLE IF NOT EXISTS. RECREATE TABLE, with the same column list, drops the existing table and creates it again, so its rows are gone. To leave an existing table alone, check the RDB$RELATIONS system table first and run the CREATE TABLE through EXECUTE STATEMENT, because a PSQL block can't run DDL directly. In isql, SET TERM changes the terminator for the length of the block:

COMMIT;
SET TERM ^ ;
EXECUTE BLOCK AS
BEGIN
  IF (NOT EXISTS (SELECT 1 FROM RDB$RELATIONS WHERE RDB$RELATION_NAME = 'STUDENTS')) THEN
    EXECUTE STATEMENT 'CREATE TABLE STUDENTS (ID INTEGER NOT NULL PRIMARY KEY)';
END^
SET TERM ; ^
COMMIT;

Both commits matter. Without the first, the check misses a table created earlier in the same isql session, and the CREATE TABLE fails with a duplicate key in RDB$RELATIONS. The second is there because a table created through EXECUTE STATEMENT waits for a commit, as a row does.

Every column accepts NULL unless it says NOT NULL. BIRTH_DATE and GRADE say nothing, so a student can be stored without them, while ID and NAME must always have a value.

The last line of the statement names the primary key PK_STUDENTS, and Firebird builds an index for it without being asked. Leave the name out and Firebird makes one up in the form INTEG_n:

CREATE TABLE TEACHERS (ID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR(50));
SHOW TABLE TEACHERS;

ID                              INTEGER Not Null
NAME                            VARCHAR(50) Nullable
CONSTRAINT INTEG_4:
  Primary key (ID)

Error messages quote that name when a row breaks the key, so PK_STUDENTS tells you the table at a glance and INTEG_4 doesn't.

Add rows and see what the table refuses

Insert three students:

INSERT INTO STUDENTS (ID, NAME, BIRTH_DATE, GRADE) VALUES (1, 'Ada', '2009-04-11', 8);
INSERT INTO STUDENTS (ID, NAME, BIRTH_DATE, GRADE) VALUES (2, 'Grace', '2008-12-02', 9);
INSERT INTO STUDENTS (ID, NAME) VALUES (3, 'Linus');
COMMIT;

The third row leaves out BIRTH_DATE and GRADE, which the table allows. The rows need the COMMIT that the table didn't. isql keeps inserts, updates and deletes in an open transaction until you commit them; EXIT commits as it leaves, and QUIT rolls the work back.

An uncommitted row also holds on to its table. Before the COMMIT, a new table with a foreign key to STUDENTS fails with object TABLE "STUDENTS" is in use.

SELECT ID, NAME, BIRTH_DATE, GRADE FROM STUDENTS ORDER BY ID;

isql prints <null> where Linus has no value:

The primary key and NOT NULL now do their work:

INSERT INTO STUDENTS (ID, NAME) VALUES (3, 'Margaret');
INSERT INTO STUDENTS (ID) VALUES (4);

Firebird refuses both, a second student with ID 3 and a student with no name, and each message names the constraint or the column that refused it:

Statement failed, SQLSTATE = 23000
violation of PRIMARY or UNIQUE KEY constraint "PK_STUDENTS" on table "STUDENTS"
-Problematic key value is ("ID" = 3)
Statement failed, SQLSTATE = 23000
validation error for column "STUDENTS"."NAME", value "*** null ***"

Firebird has no CREATE TABLE ... AS SELECT, and stops at the AS with "Token unknown". To copy rows into a new table, create it first and fill it with INSERT ... SELECT:

CREATE TABLE GRADUATES (
  ID INTEGER NOT NULL,
  NAME VARCHAR(50) NOT NULL,
  CONSTRAINT PK_GRADUATES PRIMARY KEY (ID)
);
INSERT INTO GRADUATES (ID, NAME) SELECT ID, NAME FROM STUDENTS WHERE GRADE = 9;

Let Firebird number the rows

Typing each ID yourself works for three students, and not for an application that adds them all day. Since Firebird 3.0, a column declared GENERATED BY DEFAULT AS IDENTITY takes the next number whenever an insert leaves it out:

CREATE TABLE COURSES (
  ID INTEGER GENERATED BY DEFAULT AS IDENTITY,
  TITLE VARCHAR(40) NOT NULL,
  CONSTRAINT PK_COURSES PRIMARY KEY (ID)
);
INSERT INTO COURSES (TITLE) VALUES ('Algebra');
INSERT INTO COURSES (TITLE) VALUES ('Chemistry');
SELECT ID, TITLE FROM COURSES;

Firebird numbered the rows from 1:

BY DEFAULT lets an insert supply its own value, and the identity column doesn't check it. The Language Reference says so plainly: "Uniqueness is not enforced automatically." The primary key is what stops a value that's already taken:

INSERT INTO COURSES (ID, TITLE) VALUES (1, 'Biology');

Statement failed, SQLSTATE = 23000
violation of PRIMARY or UNIQUE KEY constraint "PK_COURSES" on table "COURSES"
-Problematic key value is ("ID" = 1)

Firebird 4.0 added GENERATED ALWAYS AS IDENTITY, which refuses a supplied value unless the insert adds OVERRIDING SYSTEM VALUE. Take BY DEFAULT when you will load rows that already have IDs, such as a copy of another database, and ALWAYS when only Firebird should ever pick them. A schema older than Firebird 3.0 does the same job with a generator and a BEFORE INSERT trigger.

Firebird data types for the columns

The types below cover most columns you will declare. The full list is in the data types chapter of the Firebird 5.0 Language Reference.

Three details of these types are easy to miss. BIGINT exists in dialect 3 only, which is what CREATE DATABASE gives you unless you ask for dialect 1. Firebird has no unsigned integer type at all. The n in CHAR and VARCHAR counts characters while the limit is counted in bytes, so a multi-byte character set reaches the ceiling with fewer characters.

Creating the same table in DbSchema

DbSchema builds the same STUDENTS table on a diagram and writes the CREATE TABLE for you:

1. Start DbSchema and click Connect to Database. Pick Firebird in Choose Your Database. In the Connection Dialog's Server Location, choose This computer, default port for a server on your machine, or Remote computer or custom port to enter the Server Host and the Port (3050 unless it was changed). Then enter the Database User and Password, and the path /var/lib/firebird/data/school.fdb in Database. Click Test Connection, then Connect. DbSchema downloads the Firebird JDBC driver and reverse-engineers the database into a diagram.
2. Right-click the diagram canvas, choose New Table, type STUDENTS and press Enter. The table appears on the canvas and in the tree panel.
3. Double-click the table header to open the Table Dialog. In the Columns tab, add ID as INTEGER, NAME as VARCHAR(50), BIRTH_DATE as DATE and GRADE as SMALLINT, and mark ID and NAME NOT NULL.
4. In the Indexes tab, flag ID as the primary key.
5. Click OK to close the Table Dialog.

Double-click a column to open the column dialog, where it gets its data type, NOT NULL and a default value:

On the diagram, a key icon marks the primary key column, and Diagram → Show Column Types adds each column's data type:

Whether the table also exists in Firebird depends on whether DbSchema is connected, as the synchronization documentation explains. Connected, DbSchema runs each change against the database as you make it and lists the statement in the SQL History pane. Disconnected, the same steps change only the design model, and Firebird is untouched until Schema → Create or Upgrade Schema in Database shows the generated DDL and you click Execute.

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download

Whether you type the statement in isql or draw the table in DbSchema, Firebird ends up with the same table. Once tables reference each other, the DbSchema diagram draws a line for each foreign key, so a new table's place in the schema is in front of you as you create it. Download DbSchema at https://dbschema.com/download.html, connect to your Firebird database and draw your next table on the diagram. Connecting, reverse-engineering, creating tables on the diagram and the SQL Editor are in the free Community Edition; designing disconnected, saving the design to a .dbs file and the schema synchronization that deploys it are in Pro.