powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Форумы / Проектирование БД [игнор отключен] [закрыт для гостей] / Внешний ключ: обойтись без дополнительного уникального индекса?
22 сообщений из 22, страница 1 из 1
Внешний ключ: обойтись без дополнительного уникального индекса?
    #36709431
topic starter
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Добрый день. Нужен совет по схеме БД.

Есть следующие таблицы: Цвета, Круги, Квадраты, НаборыФигур. Круги и квадраты могут быть любого цвета. Набор фигур состоит из одного круга и одного квадрата, одинакового цвета. Консистентность данных нужно обеспечивать декларативно.

Нарисовал вот такую схемку:
Код: plaintext
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.
create table Цвета (	
  КодЦвета char( 1 ) primary key
, Название varchar( 10 )
)

create table Круги (
    КодКруга char( 2 ) primary key
  , КодЦвета char( 1 ) references Цвета(КодЦвета) unique (КодЦвета, КодКруга)
  , X int, Y int
  , Радиус int check (Радиус >  0 )
)

create table Треугольники (
  КодТреугольника char( 2 ) primary key
, КодЦвета char( 1 ) references Цвета(КодЦвета) unique (КодЦвета, КодТреугольника)
, A_X int, A_Y int, B_X int, B_Y int, C_X int, C_Y int
)

create table НаборыФигур (
  КодНабора int identity( 1 , 1 )
, КодЦвета char( 1 ) references Цвета(КодЦвета)
, КодКруга char( 2 )
, КодТреугольника char( 2 )
)
alter table НаборыФигур add constraint FK_Набор_Цвет_Круга
  foreign key (КодЦвета, КодКруга) references Круги(КодЦвета, КодКруга)
alter table НаборыФигур add constraint FK_Набор_Цвет_Треугольника
  foreign key (КодЦвета, КодТреугольника) references Треугольники(КодЦвета, КодТреугольника)
В таблицах Круги и Треугольники ограничения уникальных столбцов введены только для того, чтобы для таблицы НаборыФигур можно было создать внешние ключи по паре столбцов (код фигуры и цвет фигуры), обеспечив тем самым одинаковый цвет фигур в наборе. Эта схема нормально работает, не разрешая вставку никаких наборов, кроме двух указанных ниже (что хорошо и правильно).

Скрипт заполнения таблиц тестовыми данными:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
insert Цвета(КодЦвета, Название) values('К', 'Красный')
insert Цвета(КодЦвета, Название) values('С', 'Синий')
insert Цвета(КодЦвета, Название) values('З', 'Зеленый')

insert Круги (КодКруга, КодЦвета, X, Y, Радиус)
values ('к1', 'К',  20 ,  10 ,  5 )
insert Круги (КодКруга, КодЦвета, X, Y, Радиус)
values ('к2', 'С',  0 , - 3 ,  1 )
insert Круги (КодКруга, КодЦвета, X, Y, Радиус)
values ('к3', 'З', - 8 ,  0 ,  4 )

insert Треугольники (КодТреугольника, КодЦвета, A_X, A_Y, B_X, B_Y, C_X, C_Y)
values ('т6', 'К',  8 , - 4 , - 2 ,  0 , - 3 , - 1 )
insert Треугольники (КодТреугольника, КодЦвета, A_X, A_Y, B_X, B_Y, C_X, C_Y)
values ('т7', 'С',  5 ,  7 , - 3 ,  4 , - 1 ,  0 )

insert into НаборыФигур(КодЦвета, КодКруга, КодТреугольника)
values ('К','к1','т6')
insert into НаборыФигур(КодЦвета, КодКруга, КодТреугольника)
values ('С','к2','т7')

Смущает только одно: введение [вроде бы] ненужных уникальных индексов в таблицах: unique (КодКруга, КодЦвета) и unique (КодТреугольника, КодЦвета). Ведь эти пары значений и так заведомо уникальны, поскольку в каждую пару входит первичный ключ. Вот это-то и не нравится: накладываем уникальность поверх уникальности. Хочется понять, так ли это необходимо в данном случае, или можно что-то поизящнее изобразить.

Можно было бы, конечно, сделать первичные ключи в таблицах Круги и Треугольники составными (код фигуры + код цвета), тогда и FK по ним построится и без привлечения дополнительного unique constraint. Но хочется избежать составных ЕК, так как первичные ключи всех остальных таблиц базы - суррогатные и простые.

Версия сервера 2005. Всем прочитавшим заранее спасибо.
...
Рейтинг: 0 / 0
Внешний ключ: обойтись без дополнительного уникального индекса?
    #36709435
topic starter
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Упс, спутал квадраты с треугольниками :) Исправляюсь. Первый абзац следует читать так:

Есть следующие таблицы: Цвета, Круги, Треугольники, НаборыФигур. Круги и треугольники могут быть любого цвета. Набор фигур состоит из одного круга и одного треугольника, одинакового цвета. Консистентность данных нужно обеспечивать декларативно.
...
Рейтинг: 0 / 0
Внешний ключ: обойтись без дополнительного уникального индекса?
    #36709442
topic starter
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Перечитал сам себя и увидел, что структура приведенных таблиц и тестовые примеры данных противоречат мною же заявленному утверждению: "первичные ключи всех таблиц базы - суррогатные и простые". Поясню, пожалуй, чтобы не было путаницы. На самом деле, в примере приведены только прототипы реальных таблиц, поэтому для наглядности значения ключей сделаны "говорящими": 'К', 'С', 'З' и т.д., как бы на вид естественными, что ли. А в реальных таблицах там везде id int identity(1,1). Ну, не хотелось в примере загромождать все кучей цифр. Прошу прощения, если кого-то сбил с толку.

Модератор: Тема перенесена из форума "Microsoft SQL Server".
...
Рейтинг: 0 / 0
Внешний ключ: обойтись без дополнительного уникального индекса?
    #36709670
Senya_L
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
topic starter,

Интересно, как будет выглядеть таблица [НаборыФигур], если к треугольникам и кругам прибавится еще с пяток типов фигур? Я, конечно, понимаю, что примитивов не так уж и много, но все ж таки...
...
Рейтинг: 0 / 0
Внешний ключ: обойтись без дополнительного уникального индекса?
    #36709687
topic starter
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Senya_L,

понимаю вас. При увеличении количества типов фигур, наверно изящнее было бы проблему решить через промежуточную таблицу, так? Но набор всегда состоит только из двух фигур. Это, напоминаю, прототипы реальных таблиц, и у экземпляра сущности, названной здесь условно "Набор фигур", в действительности множество других атрибутов. Но эти два - неизменные (в том смысле, что всегда присутствуют, и всегда ровно два, и всегда должны быть одного "цвета").
...
Рейтинг: 0 / 0
Внешний ключ: обойтись без дополнительного уникального индекса?
    #36709707
topic starter
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Подискутирую-ка сам с собой :)

Похоже, таблица НаборыФигур у меня не нормализована, поскольку там наблюдается некая зависимость неключевых атрибутов от другого неключевого же атрибута (цвет набора). Вопросы:
1. Действительно ли нарушена нормализация?
2. Что лучше делать? Оставить все как есть ("работает и ладно") или попытаться как-то оптимизировать? Например, вынести атрибуты, зависящие от цвета, в отдельную таблицу (правда, пока довольно смутно представляю, что мне это даст; нужно еще рисовать и смотреть...)
...
Рейтинг: 0 / 0
Внешний ключ: обойтись без дополнительного уникального индекса?
    #36709709
Senya_L
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
topic starterСмущает только одно: введение [вроде бы] ненужных уникальных индексов в таблицах: unique (КодКруга, КодЦвета) и unique (КодТреугольника, КодЦвета). Ведь эти пары значений и так заведомо уникальны, поскольку в каждую пару входит первичный ключ. Вот это-то и не нравится: накладываем уникальность поверх уникальности. Хочется понять, так ли это необходимо в данном случае, или можно что-то поизящнее изобразить. Ограничения UNIQUE Вам ничего не дадут. ИМХО, у Вас направление связи неверное. То бишь это таблицы фигур должны ссылаться на [НаборыФигур]. Тогда можно будет декларативно ограничить и количество фигур в наборе и их цвет. Как и хотелось изначально. Да и поле [КодЦвета] можно перенести в [НаборыФигур].
...
Рейтинг: 0 / 0
Внешний ключ: обойтись без дополнительного уникального индекса?
    #36709711
Senya_L
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
topic starterПодискутирую-ка сам с собой :)Ну что ж вы хотите? Жара, духота, все на водоемах или в теньке газеткой помахивают. А Вы хотите, чтобы сразу ответили...
...
Рейтинг: 0 / 0
Внешний ключ: обойтись без дополнительного уникального индекса?
    #36709713
SERG1257
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Сформулируйте вопрос.
Например
1
Таблицы Круги и квадраты очень большие с частой вставкой - поддержка дополнительных индексов накладна. Что делать?
Ответ: забить на свое правило что первичный ключ не может составным.

2 Таблицы круги и квадраты статичные, наличие индекса напрягает только мое эстетическое чувство
Ответ: я не вижу способа заставить SQLserver объявить ограничение внешнего ключа без использования ограничения уникальности, стало быть надо гасить свое эстетическое чувство.

авторПри увеличении количества типов фигур, наверно изящнее было бы проблему решить через промежуточную таблицу, так?Не так. Вы как архитектор должны проработать вариант, а что если (по независящим от вас причинам) добавится еще одна фигура, что будет стоить переделать приложение. Если добавление дополнительной фигуры явление редкое (такое что под это дело будет патч на приложение и базу данных) тогда добавление дополнительного столбца (индексов, ограничений) тестирование наката патча и т.д. дело оправданное. Если же введение дополнительной фигуры - часть бизнеса, тогда лучше ввести дополнительную таблицу.
...
Рейтинг: 0 / 0
Внешний ключ: обойтись без дополнительного уникального индекса?
    #36709714
topic starter
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Senya_L,

да, вы правы, ограничения UNIQUE позволили мне только лишь ограничения внешнего ключа создать (fk-констрэйнты). Другой вопрос, нужны ли они.
...
Рейтинг: 0 / 0
Внешний ключ: обойтись без дополнительного уникального индекса?
    #36709721
topic starter
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Senya_Ltopic starterПодискутирую-ка сам с собой :)Ну что ж вы хотите? Жара, духота, все на водоемах или в теньке газеткой помахивают. А Вы хотите, чтобы сразу ответили...Сугубо уважаю право форумчан на субботний отдых в теньке у водоема! Фраза "подискутирую..." относилась исключительно к безудержному полету моей личной творческой фантазии :)
...
Рейтинг: 0 / 0
Внешний ключ: обойтись без дополнительного уникального индекса?
    #36709732
topic starter
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
SERG1257,

благодарю за развернутый ответ. Вот так и надо с нами, новичками - с пылесосом по мозгам пройтись.

Из предложенных вами вариантов верен второй:SERG12572 Таблицы круги и квадраты статичные, наличие индекса напрягает только мое эстетическое чувствоДа, эти таблицы представляют собой род справочников, одна из них заполняется первый раз при старте системы и практически не меняется потом (редко, раз в год-два). Другая обнуляется и заполняется ежегодно.
SERG1257Вы как архитектор должны проработать вариант, а что если (по независящим от вас причинам) добавится еще одна фигура, что будет стоить переделать приложение. Если добавление дополнительной фигуры явление редкое (такое что под это дело будет патч на приложение и базу данных) тогда добавление дополнительного столбца (индексов, ограничений) тестирование наката патча и т.д. дело оправданное. Если же введение дополнительной фигуры - часть бизнеса, тогда лучше ввести дополнительную таблицу.Окей. Добавление фигуры - не часть бизнеса. Это явление (по уверениям составителей ТЗ) чрезвычайно маловероятное. Ну, как атрибут банковского документа, что ли. Таких атрибутов ведь известное количество: расчетный счет, БИК, ИНН, бла-бла-бла, и новый атрибут вот так вот из воздуха появиться не может

Получается, что представленная схема выглядит оптимальной? И, поскольку "способа заставить SQLserver объявить ограничение внешнего ключа без использования ограничения уникальности нет", то на эстетическое чувство придется наступить грязным ботинком.
...
Рейтинг: 0 / 0
Внешний ключ: обойтись без дополнительного уникального индекса?
    #36709736
Senya_L
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
topic starterSenya_L,

да, вы правы, ограничения UNIQUE позволили мне только лишь ограничения внешнего ключа создать (fk-констрэйнты). Другой вопрос, нужны ли они.Та не нужны они.
Код: plaintext
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.
create table Цвета (	
  КодЦвета char( 1 ) primary key
, Название varchar( 10 )
)

create table Круги (
    КодКруга char( 2 ) primary key
  , X int, Y int
  , Радиус int check (Радиус >  0 )
)

create table Треугольники (
  КодТреугольника char( 2 ) primary key
, A_X int, A_Y int, B_X int, B_Y int, C_X int, C_Y int
)

create table НаборыФигур (
  КодНабора int identity( 1 , 1 )
, КодЦвета char( 1 ) references Цвета(КодЦвета)
, КодКруга char( 2 )
, КодТреугольника char( 2 )
)
alter table НаборыФигур add constraint FK_Набор_Цвет_Круга
  foreign key (КодЦвета, КодКруга) references Круги(КодЦвета, КодКруга)
alter table НаборыФигур add constraint FK_Набор_Цвет_Треугольника
  foreign key (КодЦвета, КодТреугольника) references Треугольники(КодЦвета, КодТреугольника)
Заметьте, просто удалил две строчки исходного скрипта :)
...
Рейтинг: 0 / 0
Внешний ключ: обойтись без дополнительного уникального индекса?
    #36709739
topic starter
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Senya_LЗаметьте, просто удалил две строчки исходного скрипта :)
Сейчас-сейчас [потирает руки], испробую ваш вариант.
...
Рейтинг: 0 / 0
Внешний ключ: обойтись без дополнительного уникального индекса?
    #36709744
Senya_L
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
topic starterSenya_LЗаметьте, просто удалил две строчки исходного скрипта :)
Сейчас-сейчас [потирает руки], испробую ваш вариант.Ну тады попробуйте и еще такой рабоче-крестьянский вариант:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
create table Цвета (	
  КодЦвета char( 1 ) primary key
, Название varchar( 10 )
)

create table НаборыФигур (
  КодНабора int identity( 1 , 1 ) primary key
, КодЦвета char( 1 ) references Цвета(КодЦвета)
, КодКруга char( 2 )
, КодТреугольника char( 2 )
)


create table Фигура(
    КодФигуры char( 2 ) primary key,
	КодНабора int not null references НаборыФигур(КодНабора),
	ТипФигуры char( 1 ) check(ТипФигуры in('t', 'k'))
	-- круг
  , X int null, Y int null, Радиус int null check (Радиус >  0  or Радиус is null)
	-- треугольник
	, A_X int, A_Y int null, B_X int null, B_Y int null, C_X int null, C_Y int null
	constraint uq_figure unique(КодНабора, ТипФигуры)
)

...
Рейтинг: 0 / 0
Внешний ключ: обойтись без дополнительного уникального индекса?
    #36709745
topic starter
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Senya_L,

посмотрел внимательней. Вы зачем атрибут "Цвет" из таблиц "Круги" и "Квадраты" исключили? Они у меня все раскрашенные, не могут они прозрачными быть :)
...
Рейтинг: 0 / 0
Внешний ключ: обойтись без дополнительного уникального индекса?
    #36709747
Senya_L
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
topic starterSenya_L,

посмотрел внимательней. Вы зачем атрибут "Цвет" из таблиц "Круги" и "Квадраты" исключили? Они у меня все раскрашенные, не могут они прозрачными быть :)авторНабор фигур состоит из одного круга и одного квадрата, одинакового цвета.Зачем поле цвет в таблицах фигур, если он уже определен в таблице НаборыФигур?
...
Рейтинг: 0 / 0
Внешний ключ: обойтись без дополнительного уникального индекса?
    #36709750
topic starter
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Senya_LНу тады попробуйте и еще такой рабоче-крестьянский вариант
Да, вариант и правда какой-то... серпасто-молоткастый. Вынесение атрибутов в отдельную таблицу атрибутов имело бы смысл, если бы ожидалось регулярное изменение как числа самих атрибутов, так и их количества в наборе. А у меня по ТЗ - атрибутов, зависящих от цвета, вообще всегда только два. И добавляться они не будут. И в наборе и два и ровно два всегда. Смысл иметь отдельную таблу?
...
Рейтинг: 0 / 0
Внешний ключ: обойтись без дополнительного уникального индекса?
    #36709755
topic starter
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Senya_LЗачем поле цвет в таблицах фигур, если он уже определен в таблице НаборыФигур?
Затем, что таблицей НаборыФигур эта БД не ограничивается. Красные круги и синие треугольники - экземпляры справочников. Они существуют (как явления) и сами по себе, без наборов. В наборы они только входят.
...
Рейтинг: 0 / 0
Внешний ключ: обойтись без дополнительного уникального индекса?
    #36709759
Senya_L
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
авторДа, вариант и правда какой-то... серпасто-молоткастый.Это одобрение или порицание? :)
topic starterSenya_LЗачем поле цвет в таблицах фигур, если он уже определен в таблице НаборыФигур?
Затем, что таблицей НаборыФигур эта БД не ограничивается. Красные круги и синие треугольники - экземпляры справочников. Они существуют (как явления) и сами по себе, без наборов. В наборы они только входят.Тогда не понимаю. Вы сформулировали свою задачу абстрактно на примере фигур. Так вот судя по Вашему примеру поле КодЦвета в таблицах фигур не нужно. Что Вы видите исходя по реальной постановке - простите, отсюда не видно.
...
Рейтинг: 0 / 0
Внешний ключ: обойтись без дополнительного уникального индекса?
    #36709766
topic starter
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Senya_LЭто одобрение или порицание? :)Изумление :)
topic starterВы сформулировали свою задачу абстрактно на примере фигур. Так вот судя по Вашему примеру поле КодЦвета в таблицах фигур не нужно. Что Вы видите исходя по реальной постановке - простите, отсюда не видно.Да, признаю, дал маху, перестарался с абстракцией. Хотел, чтобы было наглядно и понятно, чтобы не тащить сюда нашу производственную БД и утомлять описаниями, что от чего там должно зависеть. Приношу извинения.

Описывая часть БД схематично, я предполагал, что сущности Цвета, Круги, Треугольники а) уже существуют и б) связаны между собой именно таким образом, как описано в примере (то есть изменению эта часть схемы не подлежит). Интересовала только таблица НаборыФигур, а точнее - реализация декларативного ограничения ее целостности наиболее элегантным способом.
...
Рейтинг: 0 / 0
Внешний ключ: обойтись без дополнительного уникального индекса?
    #36709783
topic starter
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Вопрос решен. Оставляю структуру таблиц как есть.
Благодарю Senya_L за отзывчивость и терпение, а SERG1257 - за промывку мозгов и напоминание о том, что правильно поставленный вопрос содержит в себе половину ответа :)
...
Рейтинг: 0 / 0
22 сообщений из 22, страница 1 из 1
Форумы / Проектирование БД [игнор отключен] [закрыт для гостей] / Внешний ключ: обойтись без дополнительного уникального индекса?
Найденые пользователи ...
Разблокировать пользователей ...
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]