Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Check constraint  [new]
topic starter
Member

Откуда:
Сообщений: 245
Бизнес-правило такое: есть фигуры (круги и эллипсы). У них по 2 одинаковых параметра - центр и радиус, плюс у эллипса еще второй радиус.
Круг не может иметь второго радиуса никогда. Эллипс иметь второй радиус обязан всегда.

Круг под влиянием каких-то событий может превратиться в эллипс, и наоборот. Соответственно, при этом меняются/дополняются его параметры.

Это бизнес-правило было реализовано при помощи вот таких декларативных ограничений:
CREATE TABLE [Фигура]
(
	[Ид]	INT NOT NULL PRIMARY KEY,
	[Тип]	BIT NOT NULL,	-- 0 = круг, 1 = эллипс
-- ограничение уникальности нужно для ссылок из дочерней таблицы
CONSTRAINT [ТипФигуры] UNIQUE ([Ид], [Тип])
);

CREATE TABLE [Параметры]
(
	[Ид]			INT NOT NULL PRIMARY KEY,
	[ИдФигуры]	INT NOT NULL,
	[ТипФигуры]	BIT NOT NULL,
	[X]			DECIMAL NOT NULL,
	[Y]			DECIMAL NOT NULL,
	[Радиус]	DECIMAL NOT NULL,
	[Радиус2]	DECIMAL NULL, -- второй радиус - для эллипса

-- составной внешний ключ нужен только для проверки типа фигуры
CONSTRAINT [fk_Фигура] FOREIGN KEY ( [ИдФигуры], [ТипФигуры] )
	REFERENCES [Фигура] ( [Ид], [Тип] )	ON UPDATE CASCADE,
CONSTRAINT [ch_ТолькоДляЭллипса] 
	CHECK 
	(
		[ТипФигуры] = 0 AND [Радиус2] IS NULL
		OR
		[ТипФигуры] = 1 AND [Радиус2] IS NOT NULL
	)
);

INSERT INTO [Фигура] ( [Ид], [Тип] ) VALUES (1, 1);

INSERT INTO [Параметры] 
( [Ид], [ИдФигуры], [ТипФигуры], [X], [Y], [Радиус], [Радиус2] )
VALUES ( 1, 1, 1, 0.0, 0.0, 2.0, 3.0 );

Теперь пробуем сменить тип фигуры с эллипса на круг. Разумеется, при попытке выполнить запрос
UPDATE [Фигура] SET [Тип] = 0 WHERE [Ид] = 1
получаем ошибку
The UPDATE statement conflicted with the CHECK constraint "ch_ТолькоДляЭллипса".

Может быть, здесь допущена ошибка проектирования? Если да, то хочется знать, как ее исправить. Как изменять тип фигуры, сохраняя по возможности все ограничения, наложенные бизнес-правилом?

PS: MS SQL Server 2005
18 май 11, 23:28    [10674787]     Ответить | Цитировать Сообщить модератору
 Re: Check constraint  [new]
iljy
Member

Откуда:
Сообщений: 8711
topic starter,

ошибка?? Да тут целый зоопарк багов! Только что сходу в глаза бросается:
1. CONSTRAINT [ТипФигуры] UNIQUE ([Ид], [Тип]). Какой в нем смысл, если Ид - первичный ключ и заведомо уникально?
2. Зачем создавать искуственные трудности? Эллипс и окружность прекрасно задаются описаным прямоугольником, совершенно одинаково (для окружности это просто будет квадрат).
3. В принципе эллипс может иметь полуоси, непараллельные осям координат - вы осознанно отсекаете этот случай?
4. Нафига тут вообще 2 таблицы? У вас в первой вся запись является потенциальным ключом (что кстати неверное, см. п1), который используется в качестве внешнего - нафига? Какую инфу можно извлечь из первой таблицы, которой нет во второй?
5. Тип фигуры естественно надо менять с соблюдением ограничений. Т.е. одновременно со сменой типа задавать либо убирать второй радиус.

В общем сотрите это все и напишите заново.
18 май 11, 23:46    [10674888]     Ответить | Цитировать Сообщить модератору
 Re: Check constraint  [new]
topic starter
Member

Откуда:
Сообщений: 245
iljy, спасибо что быстро отреагировали. Постараюсь ответить на все вопросы.
Сразу же хочу предупредить, что вышеописанная схема - упрощенное отражение реальной бизнес-схемы, где объекты гораздо более сложны.

1. Уникальный констрейнт [ТипФигуры] задумывался только и исключительно для того, чтобы в дочерних таблицах можно было строить составные внешние ключи на первую табличку, с целью проверки типа фигуры (при помощи check-констрейнтов). То, что [Ид] - первичный ключ и заведомо уникально, я прекрасно знаю. Предложите, пожалуйста, другой механизм поддержания ссылочной целостности в дочерних таблицах по нескольким полям родительской таблицы без введения на ней UNIQUE CONSTRAINT-а по этим полям.

2. Искуственные трудности я не создаю. Давайте условимся так, что эти "гипотетические-фигуры-в-вакууме" описываются именно и только так - центром и радиусом. На самом деле в реальных бизнес-объектах эти атрибуты называются по-другому, не будем ими заморачиваться.

3. См. п.2.

4. Реальные таблицы содержат десятки полей, поэтому в первой табличке вся запись никак не может являться потенциальным первичным ключом. В базе все ПК - суррогатные и однотипные.

5. "Тип фигуры естественно надо менять с соблюдением ограничений" - это совершенно верно. Вот и хочется знать, как это можно сделать. Если я начну изменение с дочерней таблицы (т.е. во второй табличке присвою [Радиус2] = NULL и [ТипФигуры] = 0), то наткнусь на ошибку по форейн констрейнту, так как у родителя значение [ТипФигуры] все еще равно 1.
19 май 11, 00:08    [10674990]     Ответить | Цитировать Сообщить модератору
 Re: Check constraint  [new]
topic starter
Member

Откуда:
Сообщений: 245
iljy
В общем сотрите это все и напишите заново.
Я немножко перепишу первую табличку. И прошу прощения за то, что из желания упростить задачу слишком перемудрил.

CREATE TABLE [Фигура]
(
	[Ид]	INT NOT NULL PRIMARY KEY,
	[Тип]	BIT NOT NULL,	-- 0 = круг, 1 = эллипс

	-- ... и еще много-много-много других полей ...

-- ограничение уникальности нужно для ссылок из дочерней таблицы
CONSTRAINT [ТипФигуры] UNIQUE ([Ид], [Тип])
);
19 май 11, 00:12    [10675015]     Ответить | Цитировать Сообщить модератору
 Re: Check constraint  [new]
iljy
Member

Откуда:
Сообщений: 8711
topic starter,

объясните, зачем поддерживать ссылочную целостность по составному ключу когда есть первичный? Что вам это дает? Вы нарушаете 3ю нормальную форму, зачем? Если уж никак невозможно объединить эти таблицы (хотя пока вы ни одного аргумента за это не привели, по описанию у вас не может несколько записей из второй таблицы ссылаться на одну из первой) - так
вынесите поля, зависимые от ИдФигуры, в первую таблицу.
А вообще разговор беспредметный. Да и тема явно не этого форума, вам в проектирование.
19 май 11, 00:17    [10675039]     Ответить | Цитировать Сообщить модератору
 Re: Check constraint  [new]
topic starter
Member

Откуда:
Сообщений: 245
iljy,
если мне в проектирование - пусть модераторы перенесут тему.

Попытаюсь еще раз объяснить. Ссылочную целостность по составному ключу я пытаюсь поддерживать для того, чтобы в дочерней таблице какие-то поля могли иметь только определенные значения, в зависимости от значения поля родительской таблицы.

Сейчас я вижу, что действительно схему неграмотно нарисовал. Эх... Попробую описать поближе к предметной области.

Пусть родительская таблица называется [ВидыОтправлений] = {бандероль, письмо, посылка} и имеет поле [ТипОтправления] = {простое | заказное}. Различные виды отправлений (бандероль или письмо) могут быть как простыми, так и заказными.

Тогда дочерняя таблица - это [Отправления], со ссылками на поля родительской таблицы: вид и тип. Эта табличка, помимо прочих, содержит некоторые поля, которые надо заполять только если отправляется заказное. Если простое, то эти поля должны быть NULL.

В процессе создания отправки, может быть принято решение сменить тип отправления с заказного на простое. Если все констрейнты будут аналогичны вышеприведенному примеру, то как это корректно сделать?
19 май 11, 00:35    [10675126]     Ответить | Цитировать Сообщить модератору
 Re: Check constraint  [new]
iljy
Member

Откуда:
Сообщений: 8711
topic starter,

в таблице ВидыОтправлений первичным ключем должна быть пара ВидОтправления (бандероль-письмо-етс) + ТипОтправления. Тогда вторая таблица действительно ссылается по этим двум полям на первую, но при этом поле ТипОтправления можно спокойно сменить, со всеми проверками корректности параметров, при этом целостность по внешнему ключу не нарушится, а просто запись станет ссылаться на другую родительскую. Соответственно если сочетание ВидОтправления-ТипОтправления недопустимо, то возникнет ошибка ВК.
19 май 11, 00:41    [10675159]     Ответить | Цитировать Сообщить модератору
 Re: Check constraint  [new]
SIMPLicity_
Member

Откуда: (((@)))
Сообщений: 8879
Корче, коллега - яп сделал две таблицы - ТипОтправлений и ВидОтправлений.
А в одну фигачить и то и другое - вместо трёх строк будет шесть (ПисьмоПростое, ПисьмоЗаказное, БандерольПростая, БандерольЗаказная, .... ). А если по-другому, тогда в тип можно будет внести вид и наоборот.
Ну, в общем: блондинко - за две таблицы.
PS Хотя, если указанными парами значений покрывается всё - то пусть будет одна таблица; но характеристика будет вот такая как в примере (ПисьмоПростое, ПисьмоЗаказное, etc...).
19 май 11, 00:54    [10675204]     Ответить | Цитировать Сообщить модератору
 Re: Check constraint  [new]
invm
Member

Откуда: Москва
Сообщений: 9844
Резюмируя, должно получиться примерно следущее:
create table dbo.[ТипОтправления]
(
  [id_ТипаОтправления] int not null,
  [НаименованиеТипаОтправления] varchar(30) not null,
  constraint [PK_ТипОтправления] primary key ([id_ТипаОтправления])
)
go
create table dbo.[ВидОтправления]
(
  [id_ВидаОтправления] int not null,
  [НаименованиеВидаОтправления] varchar(30) not null,
  constraint [PK_ВидОтправления] primary key ([id_ВидаОтправления])
)
go
create table dbo.[ДопустимыеОтправления]
(
  [id_ТипаОтправления] int not null,
  [id_ВидаОтправления] int not null,
  constraint [PK_ДопустимыеОтправления] primary key ([id_ТипаОтправления], [id_ВидаОтправления]),
  constraint [FK_ДопустимыеОтправления_ТипОтправления] foreign key ([id_ТипаОтправления]) references dbo.[ТипОтправления] ([id_ТипаОтправления]),
  constraint [FK_ДопустимыеОтправления_ВидОтправления] foreign key ([id_ВидаОтправления]) references dbo.[ВидОтправления] ([id_ВидаОтправления]) 
)
go
create table dbo.[Отправления]
(
  [id_Отправления] int not null,
  [id_ТипаОтправления] int not null,
  [id_ВидаОтправления] int not null,
  ...
  constraint [PK_Отправления] primary key ([id_Отправления]),
  constraint [FK_Отправления_ДопустимыеОтправления] foreign key ([id_ТипаОтправления], [id_ВидаОтправления]) references dbo.[ДопустимыеОтправления] ([id_ТипаОтправления], [id_ВидаОтправления])
)
go
19 май 11, 01:13    [10675242]     Ответить | Цитировать Сообщить модератору
 Re: Check constraint  [new]
topic starter
Member

Откуда:
Сообщений: 245
iljy, SIMPLicity_,

да, похоже что придется вводить составной первичный ключ на родительской табличке. А так хотелось этого избежать! В других таблах ПК все простые. Хотелось, ткскть, натянуть фрачную пару на осьминога и подмять предметную область под свои идеализированные представления о проектировании БД )))

Попробую переделать схему данных. О результате отпишусь.
19 май 11, 01:16    [10675247]     Ответить | Цитировать Сообщить модератору
 Re: Check constraint  [new]
topic starter
Member

Откуда:
Сообщений: 245
invm,

спасибо за скриптик, наглядно получилось. И не лень вам возиться с нами, чайниками? )))
На самом деле реальная структура данных запутаннее, это уж как водится. Но все равно попробую применить эту схему. Главное, что в мозгах вроде прояснилось.
19 май 11, 01:21    [10675261]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить