FirebirdSQL logo

FOREIGN KEY

A foreign key ensures that the participating column(s) can contain only values that also exist in the referenced column(s) in the master table.These referenced columns are often called target columns.They must be the primary key or a unique key in the target table.They need not have a NOT NULL constraint defined on them although, if they are the primary key, they will, of course, have that constraint.

The foreign key columns in the referencing table itself do not require a NOT NULL constraint.

A single-column foreign key can be defined in the column declaration, using the keyword REFERENCES:

... ,
  ARTIFACT_ID INTEGER REFERENCES COLLECTION (ARTIFACT_ID),

The column ARTIFACT_ID in the example references a column of the same name in the table COLLECTIONS.

Both single-column and multi-column foreign keys can be defined at the table level.For a multi-column foreign key, the table-level declaration is the only option.

...
  CONSTRAINT FK_ARTSOURCE FOREIGN KEY(DEALER_ID, COUNTRY)
    REFERENCES DEALER (DEALER_ID, COUNTRY),

Notice that the column names in the referenced (“master”) table may differ from those in the foreign key.

Note

If no target columns are specified, the foreign key automatically references the target table’s primary key.

Foreign Key Actions

With the sub-clauses ON UPDATE and ON DELETE it is possible to specify an action to be taken on the affected foreign key column(s) when referenced values in the master table are changed:

NO ACTION

(the default) — Nothing is done

CASCADE

The change in the master table is propagated to the corresponding row(s) in the child table.If a key value changes, the corresponding key in the child records changes to the new value;if the master row is deleted, the child records are deleted.

SET DEFAULT

The foreign key columns in the affected rows will be set to their default values as they were when the foreign key constraint was defined.

SET NULL

The foreign key columns in the affected rows will be set to NULL.

The specified action, or the default NO ACTION, could cause a foreign key column to become invalid.For example, it could get a value that is not present in the master table.Such condition will cause the operation on the master table to fail with an error message.

Example
...
  CONSTRAINT FK_ORDERS_CUST
    FOREIGN KEY (CUSTOMER) REFERENCES CUSTOMERS (ID)
      ON UPDATE CASCADE ON DELETE SET NULL

CHECK Constraint

The CHECK constraint defines the condition the values inserted in this column or row must satisfy.A condition is a logical expression (also called a predicate) that can return the TRUE, FALSE and UNKNOWN values.A condition is considered satisfied if the predicate returns TRUE or value UNKNOWN (equivalent to NULL).If the predicate returns FALSE, the value will not be accepted.This condition is used for inserting a new row into the table (the INSERT statement) and for updating the existing value of the table column (the UPDATE statement) and also for statements where one of these actions may take place (UPDATE OR INSERT, MERGE).

Important

A CHECK constraint on a domain-based column does not replace an existing CHECK condition on the domain, but becomes an addition to it.The Firebird engine has no way, during definition, to verify that the extra CHECK does not conflict with the existing one.

CHECK constraints — whether defined at table level or column level — refer to table columns by their names.The use of the keyword VALUE as a placeholder — as in domain CHECK constraints — is not valid in the context of defining constraints in a table.

Example

with two column-level constraints and one at table-level:

CREATE TABLE PLACES (
  ...
  LAT DECIMAL(9, 6) CHECK (ABS(LAT) <=  90),
  LON DECIMAL(9, 6) CHECK (ABS(LON) <= 180),
  ...
  CONSTRAINT CHK_POLES CHECK (ABS(LAT) < 90 OR LON = 0)
);

NOT NULL Constraint

In Firebird, columns are nullable by default.The NOT NULL constraint specifies that the column cannot take NULL in place of a value.

A NOT NULL constraint can only be defined as a column constraint, not as a table constraint.