|
Introducing FOREIGN KEY constraint on table may cause cycles or multiple cascade paths
#37555592
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
Ссылка на профиль пользователя:
|
|
|
|
DDL
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.
create table Office
(
OfcId int not null constraint pk_Office primary key,
OfcName nvarchar(256) not null
)
create table Users
(
OfcId int not null,
UsrId int not null,
UsrName nvarchar(256) not null,
constraint pk_Users primary key (OfcId, UsrId),
constraint fk_Users_Office foreign key (OfcId) references Office on update cascade on delete cascade
)
create table Types
(
OfcId int not null,
TypId int not null,
TypName nvarchar(256) not null,
constraint pk_Types primary key (OfcId, TypId),
constraint fk_Types_Office foreign key (OfcId) references Office on update cascade on delete cascade
)
create table Objects
(
OfcId int not null,
UsrId int not null,
TypId int not null,
ObjId int not null,
ObjName nvarchar(256) not null,
constraint fk_Objects_Office foreign key (OfcId) references Office on update cascade on delete cascade,
constraint fk_Objects_Users foreign key (OfcId, UsrId) references Users on update cascade on delete cascade,
constraint fk_Objects_Types foreign key (OfcId, TypId) references Types on update cascade on delete cascade
)
Но, поскольку модификация PK смертный грех - переходим от естественных ключей к суррогатным:
DDL
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 Office
(
Id int not null constraint pk_Office primary key,
OfcId int not null,
constraint uk_Office unique (OfcId),
OfcName nvarchar(256) not null
)
create table Users
(
Id int not null constraint pk_Users primary key,
UsrId int not null,
UsrName nvarchar(256) not null,
OfcId int not null,
constraint uk_Users unique (OfcId, UsrId),
constraint fk_Users_Office foreign key (OfcId) references Office on delete cascade
)
create table Types
(
Id int not null constraint pk_Types primary key,
TypId int not null,
TypName nvarchar(256) not null,
OfcId int not null,
constraint uk_Types unique (OfcId, TypId),
constraint fk_Types_Office foreign key (OfcId) references Office on delete cascade
)
create table Objects
(
Id int not null constraint pk_Objects primary key,
ObjId int not null,
ObjName nvarchar(256) not null,
OfcId int not null, /* согласен - избыточен, но удаление проблему не решает */
UsrId int not null,
TypId int not null,
constraint fk_Objects_Office foreign key (OfcId) references Office on delete cascade, /* см. коммент выше */
constraint fk_Objects_Users foreign key (UsrId) references Users on delete cascade,
constraint fk_Objects_Types foreign key (TypId) references Types on delete cascade
)
M$ SQL ниасиливает .
Это структура кривая или M$ SQL?
_________________
"Helo, word!" - 17 errors 56 warnings |
|
|
