Running database creation script with constraints

December 6, 2021

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 :

First create the tables without foreign key constraints and then use ALTER to alter it for foreign keys that would be a workaround

 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),
);

  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)
    
);

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


   Alter table store
   ADD Constraint fk
   FOREIGN KEY (store_product) REFERENCES 
   product (id);
   Alter table inventory
   ADD Constraint fk1
   FOREIGN KEY (product_id) REFERENCES 
   product (id);
   Alter table product
   ADD Constraint fk2
    FOREIGN KEY (store_id) REFERENCES store (id),
   FOREIGN KEY (inventory_id) REFERENCES 
    inventory (id);