[SOLVED] Running database creation script with constraints

Issue

I created a table schema but I don’t know how I should run the script in this case, because I have constraints on each table that need the creation of the others, is there any method to add the constraint after creation or some other method to leave the correct table schema equal in the script?

I’m using PostgreSQL as a database.

CREATE TABLE IF NOT EXISTS store (
    id INTEGER NOT NULL,
    nome VARCHAR(255) NOT NULL,
    document VARCHAR(80) NOT NULL,
    store_product INTEGER NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (store_product) REFERENCES product (id)
);

CREATE TABLE IF NOT EXISTS 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),
    FOREIGN KEY (store_id) REFERENCES store (id),
    FOREIGN KEY (inventory_id) REFERENCES inventory (id)
);

CREATE TABLE IF NOT EXISTS inventory (
    id INTEGER NOT NULL PRIMARY KEY,
    amount INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    FOREIGN KEY (product_id) REFERENCES product (id)
);

Solution

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 storefk1 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.

Answered By – The Impaler

Answer Checked By – Terry (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published. Required fields are marked *