Running database creation script with constraints-CodePudding

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

CodePudding user response：

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:

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

CodePudding user response：

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