DbSchema | Firebird - How to Create a Table?

Firebird: How to Create a Table in isql and DbSchema

Introduction

Firebird is an open-source SQL relational database management system that runs on Linux, Windows, and several Unix platforms. It offers excellent concurrency, high performance, and powerful language support for stored procedures and triggers. Firebird also provides a command-line tool called isql and graphical interface options like DbSchema.

This article aims to provide a comprehensive guide on creating a table in Firebird using both isql and DbSchema.

Prerequisites

Before diving into the topic, ensure you have the following:

Firebird installed on your local system.
Familiarity with SQL basics and command line.
DbSchema installed for GUI interaction with Firebird.

For installation and establishing connection you can read our article Firebird-How to create a database?

Firebird Data Types

Here is a list of commonly used Firebird data types, alongside their definitions:

Data Type	Description
INTEGER	32-bit signed integer.
BIGINT	64-bit signed integer.
FLOAT	Approximate numeric with 32-bit precision.
DOUBLE	Approximate numeric with 64-bit precision.
DATE	Date with the format YYYY-MM-DD.
TIME	Time with the format HH:MM:SS.
TIMESTAMP	Date and time.
CHAR(n)	Fixed-length string with n characters.
VARCHAR(n)	Variable-length string with a maximum of n characters.

Creating a Table in isql

isql is the command-line interface that allows you to perform various database operations, such as creating tables, executing queries, and more. It’s a powerful tool especially useful when working with servers or environments without a graphical user interface.

Follow the steps below to create a table using isql:

Step 1: Open the Command-Line Interface or Terminal

This can vary based on your operating system. If you’re on Windows, you can use Command Prompt or PowerShell. If you’re on Linux or macOS, you can use Terminal.

Step 2: Connect to Your Firebird Database

Type in the following command to connect to your Firebird database:

isql localhost:employee.fdb -u SYSDBA -p masterkey

In this command:

localhost refers to your server name (replace this with your server’s IP address or hostname if it’s not on your local machine).
employee.fdb is your database name.
SYSDBA is your username.
masterkey is your password.

Press Enter to execute the command.

Step 3: Creating a New Table

Once you’re connected to the database, you can create a new table using the CREATE TABLE SQL statement. Let’s create a table named Students with the following fields: ID, Name, Age, and Grade.

Type in the following command:

CREATE TABLE Students
(
    ID INTEGER,
    Name VARCHAR(50),
    Age INTEGER,
    Grade INTEGER
);

In this command:

Students is the name of the new table.
ID, Name, Age, and Grade are the columns in the table.
INTEGER and VARCHAR(50) are the data types of the columns. INTEGER is used for numerical values and VARCHAR(50) is used for string values of maximum length 50.

After typing the command, press Enter to execute it.

Step 4: Confirm the Table Creation

You can confirm whether your table was created successfully by querying it. Use the SELECT SQL statement to display all records in the Students table:

SELECT * FROM Students;

Since the table is newly created, it won’t have any records yet. But if this command runs without errors, it means your table was created successfully.

Creating a Table in DbSchema

DbSchema is an intuitive GUI tool for designing and managing databases. Here’s how to create a table in Firebird using DbSchema:

Open DbSchema and click on Connect to database.
In the pop-up dialog, select Firebird as your database type, and enter your connection details.
Once connected, click on the Create new table button in the toolbar or right-click on the canvas and select Create Table.
In the new table dialog, enter the table name, e.g., Students.
Add columns by clicking the + button. For each column, you need to specify the name, type, and other attributes. For example, you can add ID, Name, Age, and Grade as column names.
Click OK to create the table. You can view the newly created table in the DbSchema layout.

Create Tables and Visually Manage Firebird using DbSchema

DbSchema is a Firebird client and visual designer. DbSchema has a free Community Edition, which can be downloaded here.

Create Table >

Create Table

Start the application and connect to the Firebird database. Right-click the table folder to create a table.

Add Columns >

Add Table Columns

Add the columns to the table.

Conclusion

Creating a table in Firebird is simple, whether you prefer a command-line tool like isql or a graphical interface like DbSchema. Understanding these steps is vital for managing and manipulating your database effectively. Remember to refer to Firebird’s documentation for a detailed explanation of its data types and functionalities.