Creating Tables in Firebird - Firebird

It is assumed that, having reached the point where you are ready to create tables, you have already prepared your data analysis and model, and you have a very clear blueprint for the structures of your main tables and their relationships. In preparation for creating these tables, you need to have performed these steps:

  • You have created a database to accommodate them. For instructions, refer to the previous chapter.
  • You have connected to the database.
  • If you plan to use domains for the data type definitions of your tables’ columns, you have already created the domains.

Table Ownership and Privileges

When a table is created, Firebird automatically applies the default SQL security scheme to it. The person who creates the table (the owner) is assigned all SQL privileges for it, including the right to grant privileges to other users, triggers, and stored procedures. No other user, except the SYSDBA, will have any access to the table until explicitly granted privileges.

This security is as good (or bad) as the security of access to your server. Anyone who can log into your server can create a database. Anyone who can attach to a database can create tables in it. Firebird 1.5 improves slightly on this unfortunate situation by allowing you to limit the locations where databases can be created and accessed. See the DatabaseAccess parameter in firebird.conf.

CREATE TABLE Statement

The DDL for creating a table is the CREATE TABLE statement. The syntax is

The first essential argument to CREATE TABLE is the table identifier. It is required and must be unique among all table, view, and procedure names in the database, otherwise you will be unable to create the table. You must also supply at least one column definition.

Defining Columns

When you create a table in the database, your main task is to define the various attributes and constraints for each of the columns in the table.

This is the syntax for defining a column:

The next sections list the required and optional attributes that you can define for a column.

Required Attributes

These are the required attributes:

  • A column identifier (name), unique among the columns in the table
  • One of the following:
  1. An SQL data type (datatype)
  2. An expression (expr) for a computed column
  3. A domain definition (domai ) for a domain-based column

Columns are separated by commas, for example:

The column FULL_NAME is a computed column calculated by concatenating two other columns in the definition, FIRST_NAME and LAST_NAMES. We will come back to computed columns a little later. A NOT NULL constraint is applied to PERSON_ID because we want to make it a primary key (details to come later).

For the PHONE_NUMBER column, we use the domain:

Columns Based on Domains

If a column definition is based on a domain, it can include a new default value, additional CHECK constraints, or a COLLATE clause that overrides one already defined in the domain definition. It can also include additional attributes or column constraints. For example, you can add a NOT NULL constraint to the column if the domain does not already define one.

For example, the following statement creates a table, COUNTRY, referencing a domain called COUNTRYNAME, which doesn’t have a NOT NULL constraint:

We add the NOT NULL constraint to the column definition of COUNTRYNAME because we know it is going to be needed as the primary key of the COUNTRY table.

Optional Attributes

The following sections describe optional attributes for columns.

DEFAULT Value

Defining a default value can save data entry time and prevent data entry errors when new rows are inserted into a table. If the row is inserted without including the column in the column list, a default value—if defined—can be automatically written into the column. In a column based on a domain, the column can include a default value that locally overrides any default defined for the domain.

For example, a possible default for a TIMESTAMP column could be the context variable CURRENT_TIMESTAMP (server date and time). In a (True/False) Boolean- style character column, the default could be set to 'F' to ensure that a valid, non-null state was written on all new rows.

A default value must be compatible with the data type of the column and consistent with any other constraints on the column or its underlying domain. A default, as appropriat to data type, can be

  • A constant (e.g., some string, numeric, or date value).
  • A context variable (e.g., CURRENT_TIMESTAMP, CURRENT_USER, CURRENT_CONNECTION, etc.).
  • Apredefined date literal such as 'NOW','TOMORROW', etc.
  • NULL can be set as the default for any nullable column.

Nullable columns default to NULL automatically, but you may wish to override an unwanted domain-level default. Don’t define this default on a column that has a NOT NULL constraint.

The following example defines a column, CREATED_BY, that defaults to the context variable CURRENT_USER:

A new row is inserted by user JILLIBEE, omitting CREATED_BY from the column list:

The table is queried:

Table

CHARACTER SET

A CHARACTER SET can be specified for an individual character or text BLOB column when you define the column. If you do not specify a character set, the column assumes the character set of the domain, if applicable; otherwise, it takes the default character set of the database. For example:

The COLLATE Clause

A COLLATE clause can be added to a CHAR or VARCHAR column to override a collation sequence otherwise defined for the column’s character set by the underlying domain, if applicable. Collation sequence is not applicable to BLOB types.

The following extends the previous example to include a COLLATE clause:

COMPUTED Columns

A computed column is one whose value is calculated each time the column is accessed at runtime. It can be a convenient way to access redundant data without the negative effects of actually storing it. Not surprisingly, such columns cannot perform like hard data—refer to the restrictions listed later in this section.

This is the syntax:

<col_name> COMPUTED [BY] (<expr>);

There is no need to specify the data type—Firebird calculates an appropriate one. Expr is any scalar expression that is valid for the data types of the columns involved in the calculation. External functions are fine to use, as long as you are sure that the libraries used by the functions will be available on all platforms where the database might be installed.

Other restrictions exist for computed columns:

  • Any columns that the expression refers to must have been defined before the computed column is defined, so it is a useful practice to place computed columns last.
  • A computed column cannot be defined as an ARRAY type or return an array.
  • You can define a computed BLOB column by using a SELECT statement on a BLOB in another table, but it is strongly recommended that you don’t do this.
  • Computed columns cannot be indexed.
  • Constraints placed on computed columns will be ignored.
  • Computed columns are output-only and read-only. Including them in INSERT or UPDATE statements will cause exceptions.

Examples of COMPUTED Columns

The following statement creates a computed column, FULL_NAME, by concatenating the LAST_NAMES and FIRST_NAME columns.

The next statement computes two columns using context variables. This can be useful for logging the particulars of row creation:

The next example creates a table with a calculated column (NEW_PRICE) using the previously created OLD_PRICE and PERCENT_CHANGE definitions:


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Firebird Topics