Is There Any Other Way to Create Constraints During SQL Table Creation

Is there any other way to create constraints during SQL table creation?

You can do this with a create schema statement:

create schema authorization [schema name]
create table department 
(   code      varchar2(3) primary key,
    name      varchar2(40)  not null,
    chair     varchar2(11),
    constraint departmentChairFk foreign key(chair) references facultyStaff(staffId) on delete set null
)
create table facultyStaff 
(   staffID     varchar2(5) primary key,
    dob     date,
    firstName   varchar2(20),
    lastName    varchar2(20),
    rank        varchar2(10),
    deptCode    varchar2(3),
    constraint rankValue check (rank in ('Assistant', 'Associate', 'Full', 'Emeritus')),
    constraint facultyDeptFk foreign key (deptCode) references department (code)
)
create table student 
(   studentId   varchar2(5) primary key,
    dob     date ,
    firstName   varchar2(20),
    lastName    varchar2(20),
    status      varchar(10),
    major       varchar(3),
constraint statusValue check (status in ('Freshman', 'Sophomore', 'Junior', 'Senior',  'Graduate')),
    constraint studentMajorFk foreign key (major) references department (code)
);

Running database creation script with constraints

There are two issues with the foreign key constraints:

1. Adding the Constraints

When there are FKs that link subsets of tables in cycles you can create the tables first, and then add the constraints later.

For example:

CREATE TABLE store (
    id INTEGER NOT NULL,
    nome VARCHAR(255) NOT NULL,
    document VARCHAR(80) NOT NULL,
    store_product INTEGER NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE product (
    id INTEGER NOT NULL,
    nome VARCHAR(255) NOT NULL,
    price NUMERIC(15,2) NOT NULL,
    store_id INTEGER NOT NULL,
    inventory_id INTEGER NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE inventory (
    id INTEGER NOT NULL PRIMARY KEY,
    amount INTEGER NOT NULL,
    product_id INTEGER NOT NULL
);

And then:

alter table store add constraint fk1 
FOREIGN KEY (store_product) REFERENCES product (id) 
deferrable initially deferred;

alter table product add constraint fk2 
FOREIGN KEY (store_id) REFERENCES store (id);

alter table product add constraint fk3 
FOREIGN KEY (inventory_id) REFERENCES inventory (id);

alter table inventory add constraint fk4 
FOREIGN KEY (product_id) REFERENCES product (id);

2. Inserting Data

When inserting data that depends on each other you'll need to decide which row in which table you want to insert first. That's why the example above includes DEFERRABLE INITIALLY DEFERRED in the first constraint.

This way you can insert in sequence:

1. Begin the transaction.
2. Insert into store -- fk1 is not validated yet.
3. Insert into inventory. Validates fk4.
4. Insert into product. Validates fk2 and fk3.
5. Commit the transaction. At this point fk1 will be finally validated.

Constraints in Data Dictionary table creation

You can use domains for this purpose. Table field => Data Element => Domain.
In the domain you can define possible values for that field. If your example with the weekdays is really what you need, than check out domain WEEKDAY in SE11.

---

Related Topics