Skip to content

Instantly share code, notes, and snippets.

@Mikou
Created March 24, 2014 17:09
Show Gist options
  • Save Mikou/9744610 to your computer and use it in GitHub Desktop.
Save Mikou/9744610 to your computer and use it in GitHub Desktop.
-- DDL
-- company
CREATE TABLE company (
id SERIAL PRIMARY KEY NOT NULL,
name VARCHAR(64) NOT NULL,
date_certif_start DATE NOT NULL,
date_certif_end DATE NOT NULL,
status SMALLINT NOT NULL,
about VARCHAR NULL
);
-- address
CREATE TABLE address (
id SERIAL PRIMARY KEY,
street VARCHAR(255) NOT NULL,
additional VARCHAR(255) NULL,
zip_code INT NOT NULL,
city VARCHAR(64) NOT NULL,
country_code INTEGER NOT NULL
);
CREATE TABLE company_address (
id_company INTEGER,
id_address INTEGER,
PRIMARY KEY (id_company, id_address),
CONSTRAINT fk_company_address__address FOREIGN KEY (id_address) REFERENCES address (id),
CONSTRAINT fk_company_address__company FOREIGN KEY (id_company) REFERENCES company (id)
);
-- rule
CREATE TABLE rule (
id SERIAL PRIMARY KEY,
type VARCHAR(32) NOT NULL,
description VARCHAR(255) NULL
);
CREATE TABLE company_rule (
id_company INTEGER,
id_rule INTEGER,
PRIMARY KEY (id_rule, id_company),
CONSTRAINT fk_company_rule__rule FOREIGN KEY (id_rule) REFERENCES rule (id),
CONSTRAINT fk_company_rule__company FOREIGN KEY (id_company) REFERENCES company (id)
);
-- insertion
WITH
company_store AS (
INSERT INTO "company"
(name, date_certif_start, date_certif_end, status, about)
VALUES
('test4', '2014-03-22', '2016-03-22', 0, 'some text...')
RETURNING id
),
address_store AS (
INSERT INTO "address"
(street, zip_code, city, country_code)
VALUES
('rue des petits anges', 75000, 'Paris', 33)
RETURNING id
)
INSERT INTO company_rule ( id_company, id_rule )
( SELECT id, '17'
FROM company_store )
INSERT INTO company_address (id_company, id_address)
( SELECT address_store.id, company_store.id
FROM address_store, company_store );
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment