Who Can Create a Table
The CREATE TABLE
statement can be executed by:
-
Users with the
CREATE TABLE
privilege
The user executing the CREATE TABLE
statement becomes the owner of the table.
The CREATE TABLE
statement can be executed by:
Users with the CREATE TABLE
privilege
The user executing the CREATE TABLE
statement becomes the owner of the table.
CREATE TABLE
ExamplesCreating 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
);
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)
);
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)
);
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
);
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
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)
);
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;
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);