FirebirdSQL logo
 DOMAININDEX 

Who Can Create a Table

The CREATE TABLE statement can be executed by:

The user executing the CREATE TABLE statement becomes the owner of the table.

CREATE TABLE Examples

  1. Creating the COUNTRY table with the primary key specified as a column constraint.

    CREATE TABLE COUNTRY (
      COUNTRY COUNTRYNAME NOT NULL PRIMARY KEY,
      CURRENCY VARCHAR(10) NOT NULL
    );
  2. Creating the STOCK table with the named primary key specified at the column level and the named unique key specified at the table level.

    CREATE TABLE STOCK (
      MODEL     SMALLINT NOT NULL CONSTRAINT PK_STOCK PRIMARY KEY,
      MODELNAME CHAR(10) NOT NULL,
      ITEMID    INTEGER NOT NULL,
      CONSTRAINT MOD_UNIQUE UNIQUE (MODELNAME, ITEMID)
    );
  3. Creating the JOB table with a primary key constraint spanning two columns, a foreign key constraint for the COUNTRY table and a table-level CHECK constraint.The table also contains an array of 5 elements.

    CREATE TABLE JOB (
      JOB_CODE        JOBCODE NOT NULL,
      JOB_GRADE       JOBGRADE NOT NULL,
      JOB_COUNTRY     COUNTRYNAME,
      JOB_TITLE       VARCHAR(25) NOT NULL,
      MIN_SALARY      NUMERIC(18, 2) DEFAULT 0 NOT NULL,
      MAX_SALARY      NUMERIC(18, 2) NOT NULL,
      JOB_REQUIREMENT BLOB SUB_TYPE 1,
      LANGUAGE_REQ    VARCHAR(15) [1:5],
      PRIMARY KEY (JOB_CODE, JOB_GRADE),
      FOREIGN KEY (JOB_COUNTRY) REFERENCES COUNTRY (COUNTRY)
      ON UPDATE CASCADE
      ON DELETE SET NULL,
      CONSTRAINT CHK_SALARY CHECK (MIN_SALARY < MAX_SALARY)
    );
  4. Creating the PROJECT table with primary, foreign and unique key constraints with custom index names specified with the USING clause.

    CREATE TABLE PROJECT (
      PROJ_ID     PROJNO NOT NULL,
      PROJ_NAME   VARCHAR(20) NOT NULL UNIQUE USING DESC INDEX IDX_PROJNAME,
      PROJ_DESC   BLOB SUB_TYPE 1,
      TEAM_LEADER EMPNO,
      PRODUCT     PRODTYPE,
      CONSTRAINT PK_PROJECT PRIMARY KEY (PROJ_ID) USING INDEX IDX_PROJ_ID,
      FOREIGN KEY (TEAM_LEADER) REFERENCES EMPLOYEE (EMP_NO)
        USING INDEX IDX_LEADER
    );
  5. Creating a table with an identity column

    create table objects (
      id integer generated by default as identity primary key,
      name varchar(15)
    );
    
    insert into objects (name) values ('Table');
    insert into objects (id, name) values (10, 'Computer');
    insert into objects (name) values ('Book');
    
    select * from objects order by id;
    
              ID NAME
    ============ ===============
               1 Table
               2 Book
              10 Computer
  6. Creating the SALARY_HISTORY table with two computed fields.The first one is declared according to the SQL standard, while the second one is declared according to the traditional declaration of computed fields in Firebird.

    CREATE TABLE SALARY_HISTORY (
      EMP_NO         EMPNO NOT NULL,
      CHANGE_DATE    TIMESTAMP DEFAULT 'NOW' NOT NULL,
      UPDATER_ID     VARCHAR(20) NOT NULL,
      OLD_SALARY     SALARY NOT NULL,
      PERCENT_CHANGE DOUBLE PRECISION DEFAULT 0 NOT NULL,
      SALARY_CHANGE  GENERATED ALWAYS AS
        (OLD_SALARY * PERCENT_CHANGE / 100),
      NEW_SALARY     COMPUTED BY
        (OLD_SALARY + OLD_SALARY * PERCENT_CHANGE / 100)
    );
  7. With DEFINER set for table t, user US needs only the SELECT privilege on t.If it were set for INVOKER, the user would also need the EXECUTE privilege on function f.

    set term ^;
    create function f() returns int
    as
    begin
        return 3;
    end^
    set term ;^
    create table t (i integer, c computed by (i + f())) SQL SECURITY DEFINER;
    insert into t values (2);
    grant select on table t to user us;
    
    commit;
    
    connect 'localhost:/tmp/7.fdb' user us password 'pas';
    select * from t;
  8. With DEFINER set for table tr, user US needs only the INSERT privilege on tr.If it were set for INVOKER, either the user or the trigger would also need the INSERT privilege on table t.The result would be the same if SQL SECURITY DEFINER were specified for trigger tr_ins:

    create table tr (i integer) SQL SECURITY DEFINER;
    create table t (i integer);
    set term ^;
    create trigger tr_ins for tr after insert
    as
    begin
      insert into t values (NEW.i);
    end^
    set term ;^
    grant insert on table tr to user us;
    
    commit;
    
    connect 'localhost:/tmp/29.fdb' user us password 'pas';
    insert into tr values(2);