FireBird Database Constraints

• Foreign Keys
• Check Clauses
• Problem

Foreign Keys

This page explains how to address database constraints in FireBird.

Database constraints are constraints that span more than one table. The most common kind of constraint is the foreign key constraint. By using this constraint, we declare that certain values in a given row must exist in a row in another table.

The simplest way to use these constraints is with a single value. The following sample SQL script can be used with FireBird's isql program ("%programfiles%\Firebird\Firebird_1_5\bin\isql.exe") to exemplify this.

CREATE DATABASE 'c:/db-constraints.gdb'
USER 'SYSDBA' PASSWORD 'masterkey';

CREATE TABLE CUSTOMERS (
  CUSTOMER_ID INTEGER NOT NULL PRIMARY KEY,
  CUSTOMER_NAME VARCHAR(128) DEFAULT '(UNKNOWN)' NOT NULL
);

CREATE TABLE ORDERS (
  ORDER_ID INTEGER NOT NULL PRIMARY KEY,
  CUSTOMER_ID INTEGER NOT NULL REFERENCES CUSTOMERS,
  DELIVERED CHAR(1) DEFAULT 'T' NOT NULL CHECK (DELIVERED = 'T' OR DELIVERED = 'F')
);

We can try our model out now and see what happens.

INSERT INTO CUSTOMERS (CUSTOMER_ID, CUSTOMER_NAME) VALUES (1, 'JOHN');
INSERT INTO CUSTOMERS (CUSTOMER_ID, CUSTOMER_NAME) VALUES (2, 'MARY');

/* Customer ID = 1 exists in CUSTOMERS */
INSERT INTO ORDERS (ORDER_ID, CUSTOMER_ID, DELIVERED) VALUES (1, 1, 'T');

/* Customer ID = 3 does not exist in CUSTOMERS */
INSERT INTO ORDERS (ORDER_ID, CUSTOMER_ID, DELIVERED) VALUES (2, 3, 'T');
Statement failed, SQLCODE = -530

violation of FOREIGN KEY constraint "INTEG_7" on table "ORDERS"

As you can see, the database server prevents us from inserting an order without a valid customer - all is well in business-land.

There is another syntax, more verbose, that can be used when a given list of values must match all together in a row in a given table. Note that in this new example, we will name our constraint, to make it easier to understand what we're doing wrong if we get a constraint violation. The syntax is quite similar to the syntax used for a primary key constraint referring to multiple columns.

/* This table has a row with total sales for a given product
 * in a given date. */
CREATE TABLE DAILY_PRODUCT_SALES (
  DATE_OF_SALE DATE NOT NULL,
  PRODUCT_ID INTEGER NOT NULL,
  UNITS_SOLD INTEGER NOT NULL CHECK (UNITS_SOLD >= 0),
  CONSTRAINT UNIQUE_DAILY_SALES
    PRIMARY KEY (DATE_OF_SALE, PRODUCT_ID)
);

/* This table has a row indicating which customer bought a certain
 * product on a given date. */
CREATE TABLE DAILY_PRODUCT_SALE_DETAILS (
  DATE_OF_SALE DATE NOT NULL,
  PRODUCT_ID INTEGER NOT NULL,
  CUSTOMER_ID INTEGER NOT NULL REFERENCES CUSTOMERS,
  CONSTRAINT UNIQUE_DAILY_SALE_DETAIL 
    PRIMARY KEY (PRODUCT_ID, DATE_OF_SALE, CUSTOMER_ID),
  CONSTRAINT DAILY_SALE_DETAIL_AGGREGATED
    FOREIGN KEY (DATE_OF_SALE, PRODUCT_ID)
    REFERENCES DAILY_PRODUCT_SALES(DATE_OF_SALE, PRODUCT_ID)
    ON DELETE CASCADE ON UPDATE CASCADE
);

/* Insert some products. Note that DATE_OF_SALE and PRODUCT_ID
 * need to be unique *in combination*. */
INSERT INTO DAILY_PRODUCT_SALES VALUES ('2004-09-10', 1, 10);
INSERT INTO DAILY_PRODUCT_SALES VALUES ('2004-09-11', 1, 12);
INSERT INTO DAILY_PRODUCT_SALES VALUES ('2004-09-12', 1, 14);

INSERT INTO DAILY_PRODUCT_SALES VALUES ('2004-09-10', 2, 30);
INSERT INTO DAILY_PRODUCT_SALES VALUES ('2004-09-11', 2, 40);

INSERT INTO DAILY_PRODUCT_SALES VALUES ('2004-09-10', 3, 100);

/* Now, see how the table with the reference constraint works. */
INSERT INTO DAILY_PRODUCT_SALE_DETAILS VALUES ('2004-09-10', 1, 1);
INSERT INTO DAILY_PRODUCT_SALE_DETAILS VALUES ('2004-09-10', 1, 2);

/* Let's try a pair of values that exist by themselves, but
 * not in combination. */
INSERT INTO DAILY_PRODUCT_SALE_DETAILS VALUES ('2004-09-12', 3, 2);

Statement failed, SQLCODE = -530

violation of FOREIGN KEY constraint "DAILY_SALE_DETAIL_AGGREGATED"
on table "DAILY_PRODUCT_SALE_DETAILS"

Just for kicks, I also threw in the ON DELETE CASCADE and ON UPDATE CASCADE clauses. They work by doing pretty much what you would expect.

SELECT * 
FROM DAILY_PRODUCT_SALE_DETAILS
WHERE PRODUCT_ID = 1 AND DATE_OF_SALE = '2004-09-10';

DATE_OF_SALE   PRODUCT_ID  CUSTOMER_ID
============ ============ ============

2004-09-10              1            1
2004-09-10              1            2

DELETE 
FROM DAILY_PRODUCT_SALES
WHERE PRODUCT_ID = 1 AND DATE_OF_SALE = '2004-09-10';

SELECT * 
FROM DAILY_PRODUCT_SALE_DETAILS
WHERE PRODUCT_ID = 1 AND DATE_OF_SALE = '2004-09-10';

[nothing]

Check Clauses

Another options we have to declare database-wide constraints is to use the CHECK clause on tables and columns. The following two examples illustrate how these can be used.

/* We create a table of preferred customers. These customers
 * must have bought from us three or more times. */
CREATE TABLE PREFERRED_CUSTOMERS (
  CUSTOMER_ID INTEGER NOT NULL
    CONSTRAINT PREFERRED_CUSTOMER_EXISTS REFERENCES CUSTOMERS,
    CONSTRAINT CUSTOMER_PLACED_MANY_ORDERS
      CHECK (3 <= (
        SELECT COUNT(*)
        FROM DAILY_PRODUCT_SALE_DETAILS D
        WHERE D.CUSTOMER_ID = CUSTOMER_ID)),
  PREFERRED_STATUS_FROM DATE DEFAULT 'NOW' NOT NULL
);

INSERT INTO PREFERRED_CUSTOMERS (CUSTOMER_ID)
VALUES (1);
Statement failed, SQLCODE = -297

Operation violates CHECK constraint CUSTOMER_PLACED_MANY_ORDERS on view or table
 PREFERRED_CUSTOMERS
 
/* Reinsert the information we deleted and add more. */
INSERT INTO DAILY_PRODUCT_SALES VALUES ('2004-10-01', 1, 100);
INSERT INTO DAILY_PRODUCT_SALES VALUES ('2004-10-02', 1, 100);
INSERT INTO DAILY_PRODUCT_SALES VALUES ('2004-10-03', 1, 100);
INSERT INTO DAILY_PRODUCT_SALE_DETAILS VALUES ('2004-10-01', 1, 1);
INSERT INTO DAILY_PRODUCT_SALE_DETAILS VALUES ('2004-10-02', 1, 1);
INSERT INTO DAILY_PRODUCT_SALE_DETAILS VALUES ('2004-10-03', 1, 1);

INSERT INTO PREFERRED_CUSTOMERS (CUSTOMER_ID)
VALUES (1);

SELECT * FROM PREFERRED_CUSTOMERS;

 CUSTOMER_ID PREFERRED_STATUS_FROM
============ =====================

           1 2004-09-08

However, note that FireBird, like other database engines, may not be too smart about these checks...

/* Delete something that will invalidate the check. */
DELETE FROM DAILY_PRODUCT_SALE_DETAILS
WHERE PRODUCT_ID = 1 AND DATE_OF_SALE = '2004-10-01';

SELECT PC.CUSTOMER_ID, COUNT(*)
FROM PREFERRED_CUSTOMERS PC, DAILY_PRODUCT_SALE_DETAILS DPSD
WHERE PC.CUSTOMER_ID = DPSD.CUSTOMER_ID
GROUP BY PC.CUSTOMER_ID;

 CUSTOMER_ID        COUNT
============ ============

           1            2

/* Whoops! The customer is still a preferred customer, violating the check. */

Unfortunately, these row constraints are not enforced at every possibility of table violation. Sometimes these problems can be addressed by using more structured checks from the client side, or offline processing techniques if required.

Problem

Now, let's tackle a problem that cannot be solved with checks at all. Let's suppose we have the following classic ORDER_ITEMS table.

CREATE TABLE ORDER_ITEMS (
  ORDER_ID INTEGER NOT NULL,
  PART_ID INTEGER NOT NULL,
  QUANTITY INTEGER NOT NULL CHECK (QUANTITY > 0),
  CONSTRAINT ORDER_ITEM_IN_ORDER
    FOREIGN KEY(ORDER_ID) REFERENCES ORDERS(ORDER_ID),
  CONSTRAINT UNIQUE_ORDER_ITEM PRIMARY KEY(ORDER_ID, PART_ID)
);

We can try our model out now and see what happens.

INSERT INTO ORDERS (ORDER_ID, DELIVERED) VALUES (1, 'T');
INSERT INTO ORDER_ITEMS (
  ORDER_ID, PART_ID, QUANTITY
) VALUES (
  1, 100, 3
);
INSERT INTO ORDER_ITEMS (
  ORDER_ID, PART_ID, QUANTITY
) VALUES (
  1, 101, 1
);

SELECT O.ORDER_ID, COUNT(OI.PART_ID) AS ITEM_COUNT
FROM ORDERS O, ORDER_ITEMS OI
WHERE O.ORDER_ID = OI.ORDER_ID
GROUP BY O.ORDER_ID;

    ORDER_ID   ITEM_COUNT
============ ============

           1            2

Now, let's suppose that we want to specify that orders must have order items to be valid. Fairly reasonable. We could declare a foreign key from ORDERS(ORDER_ID) to ORDER_ITEMS(ORDER_ID). However, the index on ORDER_ITEMS is not unique, so we get the following error.

/* Commit because there is an active transaction referring to the
 * old schema. */
COMMIT;

/* Add the constraint to the table. */
ALTER TABLE ORDERS
ADD CONSTRAINT ORDER_HAS_ITEMS 
FOREIGN KEY(ORDER_ID) REFERENCES ORDER_ITEMS(ORDER_ID);

Statement failed, SQLCODE = -607

unsuccessful metadata update
-could not find UNIQUE INDEX with specified columns

In any case, the problem is that the DBMS would try to enforce the constraint as soon as a new record is inserted into either table, and of course this would fail because there is no record inserted yet in the other table.

In an ideal world, the DBMS would just wait until we tried to commit the transaction, and then verify all the constraints in the most efficient manner possible (perhaps deferring failed constraints somehow). In any case, we're still not there yet, but we can implement some of these things on our own. Oracle8 introduces this capability for the Oracle line of DBMSs.

So, the first problem is how to track what constraints need to be enforced. A simple way to do this is to rely on one foreign key constraint in the database (which means that we still need to do inserts in a specific order), and then add a trigger to log that we need to verify the reverse constraint.

/* We accept duplicates in this table, but for all purposes
 * they are merged into a single value. */
CREATE TABLE ORDERS_TO_CHECK (
  ORDER_ID INTEGER NOT NULL
);

CREATE EXCEPTION ORDERS_REQUIRE_ITEMS
  'Orders require order items to be stored in the database.';

SET TERM !!;

CREATE PROCEDURE CHECK_ORDERS
AS
  DECLARE VARIABLE ORDER_ID INTEGER;
  DECLARE VARIABLE ORDER_COUNT INTEGER;
  DECLARE VARIABLE ORDER_ITEMS_COUNT INTEGER;
BEGIN
  FOR
    SELECT DISTINCT ORDER_ID
    FROM ORDERS_TO_CHECK
    INTO :ORDER_ID
  DO BEGIN
    SELECT COUNT(*)
    FROM ORDER_ITEMS
    WHERE ORDER_ID = :ORDER_ID
    INTO :ORDER_ITEMS_COUNT;

    IF (ORDER_ITEMS_COUNT = 0) THEN BEGIN
      /* May have been deleted. */
      SELECT COUNT(*)
      FROM ORDERS
      WHERE ORDER_ID = :ORDER_ID
      INTO :ORDER_COUNT;
      IF (ORDER_COUNT <> 0) THEN BEGIN
        EXCEPTION ORDERS_REQUIRE_ITEMS;
      END
    END ELSE BEGIN
      DELETE
      FROM ORDERS_TO_CHECK
      WHERE ORDER_ID = :ORDER_ID;
    END
  END
END!!

CREATE TRIGGER CHECK_NEW_ORDER_ITEMS FOR ORDERS
AFTER INSERT
AS BEGIN
  /* When an order is added, we need to eventually
   * check that it has items. */
  INSERT INTO ORDERS_TO_CHECK (
    ORDER_ID
  ) VALUES (
    NEW.ORDER_ID
  );
END!!

CREATE TRIGGER CHECK_UPDATED_ORDER_ITEMS FOR ORDERS
AFTER UPDATE
AS BEGIN
  /* When an order is updated, we need to eventually
   * check that it has items with its new ID. */
  IF (NEW.ORDER_ID <> OLD.ORDER_ID) THEN BEGIN
    INSERT INTO ORDERS_TO_CHECK (
      ORDER_ID
    ) VALUES (
      NEW.ORDER_ID
    );
  END
END!!

CREATE TRIGGER CHECK_ORDER_UPDATED_ITEMS FOR ORDER_ITEMS
AFTER UPDATE
AS BEGIN
  /* When an order item is assigned to another order,
   * we need to check that the old order is not left
   * without any items. */
  IF (NEW.ORDER_ID <> OLD.ORDER_ID) THEN BEGIN
    INSERT INTO ORDERS_TO_CHECK (
      ORDER_ID
    ) VALUES (
      OLD.ORDER_ID
    );
  END
END!!

CREATE TRIGGER CHECK_ORDER_DELETED_ITEMS ORDER_ITEMS
AFTER DELETE
AS BEGIN
  /* When an order item is deleted, we need to check that
   * the order is not left without any items. */
  INSERT INTO ORDERS_TO_CHECK (
    ORDER_ID
  ) VALUES (
    OLD.ORDER_ID
  );
END!!

SET TERM ;!!

OK, we can try these out now. If we add a new order but don't add any items, an exception should be thrown when we invoke the stored procedure to verify these things.

INSERT INTO ORDERS (ORDER_ID, DELIVERED) VALUES (2, 'T');
SELECT * FROM ORDERS_TO_CHECK;

    ORDER_ID
============

           2

EXECUTE PROCEDURE CHECK_ORDERS;

Statement failed, SQLCODE = -836

exception 1
-Orders require order items to be stored in the database.

Now that we can track what validation we need to perform and we have the logic to do so, the second problem is when do we run the procedure that checks things.

The obvious place is right before a transaction commits. This would then abort the transaction with the raised exception if there were any problems.

Firebird does not have triggers for a 'before-commit' event, so we could do this from the client side or from the server side if we clearly define entry points to server procedures. This is usually workable for controlled environments; you may want to have a single CHECK_BEFORE_COMMIT procedure to centrally handle any separate constraints you want to enforce.

However, this is not always an acceptable solution, for performance or security reasons, and so there is another variation that is interesting. Sometimes you can have a daemon running that will run the checks at scheduled intervals. In this case, you can use the exception-throwing approach, or you can modify the interface of the procedure to actually report the problems, or you could get creative and write a compensating transaction. Read more about FireBird Offline Processing here.