tF3qJVUa

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_fk INTEGER,
16    primary key (mecesup_projects_id)
17);
18
19CREATE TABLE 3ie_projects(
20	3ie_projects_id SERIAL NOT NULL,
21    project_fk INTEGER,
22    category_usm CHARACTER VARYING(500),
23    primary key (3ie_projects_id)
24);
25
26CREATE TABLE project_categories(
27	project_categories_id SERIAL NOT NULL,
28    name_project  CHARACTER VARYING(500),
29    primary key (category_project_id)
30);
31
32ALTER TABLE projects(
33	ADD COLUMN category_project_fk INTEGER,
34	ADD CONSTRAINT fk_category_project_reference_project FOREIGN KEY ("category_project_fk") 
35		REFERENCES "project_categories" ("project_categories_id") ON DELETE RESTRICT ON UPDATE RESTRICT;
36);
37
38ALTER TABLE responsible_categories(
39	ADD CONSTRAINT fk_responsible_categories_reference_responsible_categories FOREIGN KEY ("responsible_categories_fk") 
40		REFERENCES "responsible_categories" ("category_id") ON DELETE RESTRICT ON UPDATE RESTRICT;
41);