jhCiEez6

1DROP TABLE IF EXISTS responsible_category;
2
3CREATE TABLE responsible_categories(
4    category_id serial not null,
5    name character varying(100) not null,
6    primary key (category_id)
7);
8
9INSERT INTO responsible_categories (category_id, name) VALUES (1, 'Coordinador Financiero');
10INSERT INTO responsible_categories (category_id, name) VALUES (2, 'Coordinador Institucional');
11
12CREATE TABLE mecesup_projects(
13    mecesup_projects_id SERIAL NOT NULL,
14    responsible_categories_fk INTEGER NOT NULL,
15    project_id_fk INTEGER,
16    primary key (mecesup_projects_id)
17);
18
19CREATE TABLE 3ie_projects(
20	3ie_projects_id SERIAL NOT NULL,
21    project_id_fk INTEGER,
22    primary key (3ie_projects_id)
23);
24
25CREATE TABLE project_categories(
26	project_categories_id SERIAL NOT NULL,
27    name_project  CHARACTER VARYING(500),
28    primary key (category_project_id)
29);
30
31ALTER TABLE projects(
32	ADD COLUMN category_project_fk INTEGER,
33	ADD CONSTRAINT fk_category_project_reference_project FOREIGN KEY ("category_project_fk") 
34		REFERENCES "project_categories" ("project_categories_id") ON DELETE RESTRICT ON UPDATE RESTRICT;
35);
36
37ALTER TABLE responsible_categories(
38	ADD CONSTRAINT fk_responsible_categories_reference_responsible_categories FOREIGN KEY ("responsible_categories_fk") 
39		REFERENCES "responsible_categories" ("category_id") ON DELETE RESTRICT ON UPDATE RESTRICT;
40);