dotnet-learning/data/postgres.sql at master · navferty/dotnet-learning · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
CREATE TABLE "Factory"(
    "Id" int NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    "Name" text NULL,
    "Description" text NULL,
    CONSTRAINT "PK_Factory" PRIMARY KEY ("Id")
);

CREATE TABLE "Unit"(
    "Id" int NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    "Name" text NULL,
    "FactoryId" int NULL,
    CONSTRAINT "PK_Unit" PRIMARY KEY ("Id")
);
ALTER TABLE "Unit" ADD CONSTRAINT "FK_Unit_Factory_FactoryId" FOREIGN KEY("FactoryId")
    REFERENCES "Factory" ("Id");

CREATE TABLE "Tank"(
    "Id" int NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    "Name" text NULL,
    "Volume" int NOT NULL,
    "MaxVolume" int NOT NULL,
    "UnitId" int NULL,
    CONSTRAINT "PK_Tank" PRIMARY KEY ("Id")
);
ALTER TABLE "Tank" ADD CONSTRAINT "FK_Tank_Unit_UnitId" FOREIGN KEY("UnitId")
    REFERENCES "Unit" ("Id");

INSERT INTO "Factory" ("Name", "Description") VALUES (N'МНПЗ', N'Московский нефтеперерабатывающий завод');
INSERT INTO "Factory" ("Name", "Description") VALUES (N'ОНПЗ', N'Омский нефтеперерабатывающий завод');

INSERT INTO "Unit" ("Name", "FactoryId") VALUES (N'ГФУ-1', 1);
INSERT INTO "Unit" ("Name", "FactoryId") VALUES (N'ГФУ-2', 1);
INSERT INTO "Unit" ("Name", "FactoryId") VALUES (N'Установка АВТ-6', 2);

INSERT INTO "Tank" ("Name", "Volume", "MaxVolume", "UnitId") VALUES (N'Резервуар 1', 1500, 2000, 1);
INSERT INTO "Tank" ("Name", "Volume", "MaxVolume", "UnitId") VALUES (N'Резервуар 2', 2500, 3000, 1);
INSERT INTO "Tank" ("Name", "Volume", "MaxVolume", "UnitId") VALUES (N'Доп. резервуар 24', 3000, 3000, 2);
INSERT INTO "Tank" ("Name", "Volume", "MaxVolume", "UnitId") VALUES (N'Резервуар 35', 3000, 3000, 2);
INSERT INTO "Tank" ("Name", "Volume", "MaxVolume", "UnitId") VALUES (N'Резервуар 47', 4000, 5000, 2);
INSERT INTO "Tank" ("Name", "Volume", "MaxVolume", "UnitId") VALUES (N'Резервуар 256', 500, 500, 3);