Различия

Показаны различия между двумя версиями страницы.

--- lssexampl-02-datastru [2024/07/08 13:02] – внешнее изменение 127.0.0.1
+++ lssexampl-02-datastru [2026/04/28 12:45] (текущий) – [Создание структуры таблиц в базе SQL сервера] madmin
@@ Строка 19: / Строка 19: @@
 -- роль доступа
 create table "role" (
-	"id" serial primary key,
+	"id" bigserial primary key,
 	"code" varchar(64) not null default '',
 	"name" varchar(255) not null default ''
 );
-alter table "role" add unique("code") deferrable initially deferred;
+create unique index "uidx_role_code" on "role" ("code") where "id">0;
-alter table "role" add unique("name") deferrable initially deferred;
+create unique index "uidx_role_name" on "role" ("name") where "id">0;
 -- пользователь системы
 create table "user" (
-	"id" serial primary key,
+	"id" bigserial primary key,
 	"login" varchar(64) not null default '',
 	"password" varchar(255) not null default '',
 	"fio" varchar(255) not null default '',
-	"roleid" integer references "role" deferrable initially deferred
+	"roleid" bigint references "role" deferrable initially deferred
 );
-alter table "user" add unique("login") deferrable initially deferred;
+create index "idx_user_roleid" on "user" ("roleid");
+create unique index "uidx_user_login" on "user" ("login") where "id">0;
 -- цех
 create table "department" (
-	"id" serial primary key,
+	"id" bigserial primary key,
 	"name" varchar(255) not null default ''
 );
-alter table "department" add unique("name") deferrable initially deferred;
+create unique index "uidx_department_name" on "department" ("name") where "id">0;
 -- участок
 create table "sector" (
-	"id" serial primary key,
+	"id" bigserial primary key,
-	"departmentid" integer references "department" on delete cascade deferrable initially deferred,
+	"departmentid" bigint references "department" on delete cascade deferrable initially deferred,
 	"name" varchar(255) not null default ''
 );
 create index "idx_sector_departmentid" on "sector" ("departmentid");
-alter table "sector" add unique("departmentid", "name") deferrable initially deferred;
+create unique index "uidx_sector_unique" on "sector" ("departmentid", "name") where "id">0;
 -- тип оборудования
 create table "machinetype" (
-	"id" serial primary key,
+	"id" bigserial primary key,
 	"name" varchar(255) not null default ''
 );
-alter table "machinetype" add unique("name") deferrable initially deferred;
+create unique index "uidx_machinetype_name" on "machinetype" ("name") where "id">0;
 -- состояние оборудования
 create table "machinestatus" (
-	"id" serial primary key,
+	"id" bigserial primary key,
 	"name" varchar(255) not null default ''
 );
-alter table "machinestatus" add unique("name") deferrable initially deferred;
+create unique index "uidx_machinestatus_name" on "machinestatus" ("name") where "id">0;
 -- вид ремонта
 create table "repair" (
-	"id" serial primary key,
+	"id" bigserial primary key,
 	"name" varchar(255) not null default '',
 	"npp" int not null default 0
 );
-alter table "repair" add unique("name") deferrable initially deferred;
+create unique index "uidx_repair_name" on "repair" ("name") where "id">0;
 -- паспорт оборудования
 create table "machine" (
-	"id" serial primary key,
+	"id" bigserial primary key,
 	"dfrom" date,
 	"invnum" varchar(64) not null default '',
-	"machinetypeid" integer references "machinetype" deferrable initially deferred,
+	"machinetypeid" bigint references "machinetype" deferrable initially deferred,
 	"model" varchar(64) not null default '',
-	"departmentid" integer references "department" deferrable initially deferred,
+	"departmentid" bigint references "department" deferrable initially deferred,
-	"sectorid" integer references "sector" deferrable initially deferred,
+	"sectorid" bigint references "sector" deferrable initially deferred,
 	"dstatus" date,
-	"machinestatusid" integer references "machinestatus" deferrable initially deferred
+	"machinestatusid" bigint references "machinestatus" deferrable initially deferred
 );
 create index "idx_machine_machinetypeid" on "machine" ("machinetypeid");
@@ Строка 91: / Строка 91: @@
 create index "idx_machine_sectorid" on "machine" ("sectorid");
 create index "idx_machine_machinestatusid" on "machine" ("machinestatusid");
-alter table "machine" add unique("invnum") deferrable initially deferred;
+create unique index "uidx_machine_invnum" on "machine" ("invnum") where "id">0;
 -- ремонт оборудования
 create table "machinerepair" (
-	"id" serial primary key,
+	"id" bigserial primary key,
-	"machineid" integer references "repair" on delete cascade deferrable initially deferred,
+	"machineid" bigint references "machine" on delete cascade deferrable initially deferred,
-	"repairid" integer references "repair" deferrable initially deferred,
+	"repairid" bigint references "repair" deferrable initially deferred,
 	"dplan" date,
 	"dfact" date
@@ Строка 106: / Строка 106: @@
 -- нормы ремонтов
 create table "repairnorm" (
-	"id" serial primary key,
+	"id" bigserial primary key,
-	"repairid" integer references "repair" on delete cascade deferrable initially deferred,
+	"repairid" bigint references "repair" on delete cascade deferrable initially deferred,
-	"machinetypeid" integer references "machinetype" on delete cascade deferrable initially deferred,
+	"machinetypeid" bigint references "machinetype" on delete cascade deferrable initially deferred,
-	"period" integer not null default 0
+	"period" bigint not null default 0
 );
-alter table "repairnorm" add unique("machinetypeid","repairid") deferrable initially deferred;
 create index "idx_repairnorm_repairid" on "repairnorm" ("repairid");
 create index "idx_repairnorm_machinetypeid" on "repairnorm" ("machinetypeid");
+create unique index "uidx_repairnorm_unique" on "repairnorm" ("machinetypeid","repairid") where "id">0;
 </code>
 Этот скрипт написан вручную, содержит описание таблиц, полей, ссылочных целостностей, индексов. Его необходимо выполнить при развертывании этапа проекта на площадке.