2.2.2. Primary Tables

Now let us proceed to the primary tables. The first will be the CUSTOMER table. We will create a sequence (a generator) for its primary key and a corresponding trigger for implementing it as an auto-incrementing column. We will do the same for each of the tables.

  1. CREATE GENERATOR GEN_CUSTOMER_ID;
  3. CREATE TABLE CUSTOMER (
  4.   CUSTOMER_ID INTEGER NOT NULL,
  5.   NAME VARCHAR(60) NOT NULL,
  6.   ADDRESS VARCHAR(250),
  7.   ZIPCODE D_ZIPCODE,
  8.   PHONE VARCHAR(14),
  9.   CONSTRAINT PK_CUSTOMER PRIMARY KEY (CUSTOMER_ID)
  10. );
  12. SET TERM ^ ;
  14. CREATE OR ALTER TRIGGER CUSTOMER_BI FOR CUSTOMER
  15. ACTIVE BEFORE INSERT POSITION 0
  16. AS
  17. BEGIN
  18.   IF (NEW.CUSTOMER_ID IS NULL) THEN
  19.     NEW.CUSTOMER_ID = NEXT VALUE FOR GEN_CUSTOMER_ID;
  20. END
  21. ^
  23. SET TERM ; ^
  25. COMMENT ON TABLE CUSTOMER IS
  26. 'Customers';
  28. COMMENT ON COLUMN CUSTOMER.CUSTOMER_ID IS
  29. 'Customer Id';
  31. COMMENT ON COLUMN CUSTOMER.NAME IS
  32. 'Name';
  34. COMMENT ON COLUMN CUSTOMER.ADDRESS IS
  35. 'Address';
  37. COMMENT ON COLUMN CUSTOMER.ZIPCODE IS
  38. 'Zip Code';
  40. COMMENT ON COLUMN CUSTOMER.PHONE IS
  41. 'Phone';

  • In Firebird 3.0, you can use IDENTITY columns as auto-incremental fields. The script for creating the table would then be as follows:

    1. CREATE TABLE CUSTOMER (
    2.   CUSTOMER_ID INTEGER GENERATED BY DEFAULT AS IDENTITY,
    3.   NAME VARCHAR(60) NOT NULL,
    4.   ADDRESS VARCHAR(250),
    5.   ZIPCODE D_ZIPCODE,
    6.   PHONE VARCHAR(14),
    7.   CONSTRAINT PK_CUSTOMER PRIMARY KEY (CUSTOMER_ID)
    8. );

  • In Firebird 3.0, you need the USAGE privilege to use a sequence (generator), so you will have to add the following line to the script:

    1. GRANT USAGE ON SEQUENCE GEN_CUSTOMER_ID TO TRIGGER CUSTOMER_BI;

Now we construct a script for creating the PRODUCT table:

  1. CREATE GENERATOR GEN_PRODUCT_ID;
  3. CREATE TABLE PRODUCT (
  4.   PRODUCT_ID INTEGER NOT NULL,
  5.   NAME VARCHAR(100) NOT NULL,
  6.   PRICE D_MONEY NOT NULL,
  7.   DESCRIPTION BLOB SUB_TYPE 1 SEGMENT SIZE 80,
  8.   CONSTRAINT PK_PRODUCT PRIMARY KEY (PRODUCT_ID)
  9. );
  11. SET TERM ^;
  13. CREATE OR ALTER TRIGGER PRODUCT_BI FOR PRODUCT
  14. ACTIVE BEFORE INSERT POSITION 0
  15. AS
  16. BEGIN
  17.   IF (NEW.PRODUCT_ID IS NULL) THEN
  18.     NEW.PRODUCT_ID = NEXT VALUE FOR GEN_PRODUCT_ID;
  19. END
  20. ^
  22. SET TERM ;^
  24. COMMENT ON TABLE PRODUCT IS
  25. 'Goods';
  27. COMMENT ON COLUMN PRODUCT.PRODUCT_ID IS
  28. 'Product Id';
  30. COMMENT ON COLUMN PRODUCT.NAME IS
  31. 'Name';
  33. COMMENT ON COLUMN PRODUCT.PRICE IS
  34. 'Price';
  36. COMMENT ON COLUMN PRODUCT.DESCRIPTION IS
  37. 'Description';

In Firebird 3.0, you need to add the command for granting the USAGE privilege for a sequence (generator) to the script:

  1. GRANT USAGE ON SEQUENCE GEN_PRODUCT_ID TO TRIGGER PRODUCT_BI;