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

Откуда: Moscow
Сообщений: 907
Привет.

Имеется таблица, у которой столбцы ссылаются на 2 другие таблицы (которые очень похожи, но все же хранят разные поля и данные).
Можно ли настроить ограничение ?



MainTable - основная таблица
Id RecordTypeId LoginId Value
1 1 1Str1...
2 1 2 Str1...
3 2 3 Str1...
4 3 4 Str1...


Вспомогательная таблица для MainTable
RecordType
Id Value
1 Пользователь
2 Организация Типа А
3 Организация Типа Б


UserTable. Таблица Пользователей. Должна быть связана с таблицей MainTable.
Id MainTableRef Name
1 1 'User1'
2 2 'User2'


OrganizationTable. Таблица Пользователей. Должна быть связана с таблицей MainTable а также на вспомогательную OrganizationTypeTable
Id OrgTypeId MainTableRef Name
1 1 3 'Организация 1'
2 2 4 'Организация 2'


OrganizationTypeTable
Id Name
1 'Организация типа А'
2 'Организация типа Б'


Обратите внимание, что у таблицы UserTable нету поля OrgTypeId, как у таблицы OrganizationTable, т,к. в таблице может хранится только единственный тип (пользователи).

В то же время, в таблице OrganizationTable есть поле OrgTypeId, которое может хранить значения 1,2

Т.е. Поля OrgTypeId RecordTypeId несут похожий смысл, но все же различны.


Вопрос. Как можно настроить органичение внешних ключей, чтобы MainTable поддерживала ссылочную целостность на таблицы UserTable и OrganizationTable.
Т.е. логика ограничение словами такова:
- Записи, в таблице MainTable с RecordTypeId =1 была связана внешним ключом с таблицей UserTable.
- Записи, в таблице MainTable с RecordTypeId =2,3 была связана внешним ключом с таблицей OrganizationTable.


При этом крайне желательно обходится без создания дополнительных таблиц. Т.к. логика программы уже написана, и переделывать все для поддержания дополнительных таблиц - проблематично, считай новая итерация переделки программы.
Возможно ли обойти без создания доп таблиц ? Ну если же нет, то какие таблицы еще нужно создать ?

PS. Еще мне чуть чуть не нравится частичное пересечение таблиц OrganizationTypeTable и RecordType. Это нормально ?
24 дек 14, 15:35    [17046659]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать такое ограничение ?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
Проблемы-то в чем? MainTableRef ссылается на MainTable.id в обеих таблицах, OrganizationTypeTable.id на OrganizationTable.OrgTypeId, RecordType.ID на MainTable.RecordTypeId.

Нарушения в чем могут быть? Ваша архитектура допускает пересечение ID в UserTable и OrganizationTable. Исключить пересечения нумераций можно путем использования Sequence.
24 дек 14, 15:52    [17046811]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать такое ограничение ?  [new]
ProBiotek
Member

Откуда: Moscow
Сообщений: 907
Примечание. Необходимо чтобы была связь 1к1 таблицы UserTable-MainTable и такую-же для таблицы OrganizationTable-MainTable.
24 дек 14, 15:52    [17046817]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать такое ограничение ?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
ProBiotek, у Вас уже не 1 к 1 связь по условию задачи.
24 дек 14, 15:55    [17046845]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать такое ограничение ?  [new]
ProBiotek
Member

Откуда: Moscow
Сообщений: 907
Владислав Колосов
ProBiotek, у Вас уже не 1 к 1 связь по условию задачи.


Как это ? Вроде нету нарушения.
24 дек 14, 15:59    [17046891]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать такое ограничение ?  [new]
ProBiotek
Member

Откуда: Moscow
Сообщений: 907
Имеется связь 1к1 на уровне четко двух таблиц: UserTable-MainTable и OrganizationTable-MainTable
Т.е. одной записи в UserTable имеется записи, ссылающаяся на нее, в таблице MainTable. И также с OrganizationTable-MainTable.

Теперь, как раз, это и нужно оформить в виде Констреинтов в БД.

Я Вас не понимаю...
24 дек 14, 16:01    [17046919]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать такое ограничение ?  [new]
ProBiotek
Member

Откуда: Moscow
Сообщений: 907
ProBiotek
Имеется связь 1к1 на уровне четко двух таблиц: UserTable-MainTable и OrganizationTable-MainTable
Т.е. одной записи в UserTable имеется записи, ссылающаяся на нее, в таблице MainTable. И также с OrganizationTable-MainTable.

Теперь, как раз, это и нужно оформить в виде Констреинтов в БД.

Я Вас не понимаю...


Извиняюсь. Имелось ввиду наоборот. Одной записи в MainTable, имеется ссылка в UserTable. И также для OrganizationTable-MainTable.
24 дек 14, 16:02    [17046932]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать такое ограничение ?  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20607
ProBiotek
При этом крайне желательно обходится без создания дополнительных таблиц. Т.к. логика программы уже написана, и переделывать все для поддержания дополнительных таблиц - проблематично, считай новая итерация переделки программы.
Т.е. жизнь всё-таки решила объяснить, что кто-то зря пропустил анализ предметной области (или выполнил его ненадлежащим образом.

ProBiotek
Возможно ли обойти без создания доп таблиц ?

Вам следует написать функцию, которая проверяет соответствие этому замудрёному условию целостности, и использовать её в CONSTRAINT-е.
См. пример тут.
24 дек 14, 16:03    [17046938]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать такое ограничение ?  [new]
ProBiotek
Member

Откуда: Moscow
Сообщений: 907
Akina
Т.е. жизнь всё-таки решила объяснить, что кто-то зря пропустил анализ предметной области (или выполнил его ненадлежащим образом.


Я все понимаю, что были косяки с проектированием :) Можете конечно мысленно меня ругать за это. Я хорошо отношусь к критике и все понимаю.
Сейчас уже поздно жалеть и критиковать, т.к. назад уже не вернешь.

Так, что просьба без офтопа в стиле "автор, ну ты даешь. как ты намутил такую схему ?". Я просто опасаюсь, что тема замусорится офтопом и сложно будет вычитывать суть.
24 дек 14, 16:09    [17046988]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать такое ограничение ?  [new]
ProBiotek
Member

Откуда: Moscow
Сообщений: 907
Akina
Вам следует написать функцию, которая проверяет соответствие этому замудрёному условию целостности, и использовать её в CONSTRAINT-е.
См. пример тут.


Спасибо за предложение. Но смутило, что
"CHECK constraints are not validated during DELETE statements. Therefore, executing DELETE statements on tables with certain types of check constraints may produce unexpected results. For example, consider the following statements executed on table CheckTbl."

В целом, это довольно таки терпимо, т.к. схема работы предполагает лишь добавление записей, но не их удаление.


Есть еще идеи у кого ? Пока, пожалуй, сделаю как предлагает Akina.
24 дек 14, 16:15    [17047046]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать такое ограничение ?  [new]
мимокрокодил222
Guest
Akina
Вам следует написать функцию, которая проверяет соответствие этому замудрёному условию целостности, и использовать её в CONSTRAINT-е.
См. пример тут.


при read committed запросто можно нарушить это самое ограничение.
24 дек 14, 16:26    [17047128]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать такое ограничение ?  [new]
ProBiotek
Member

Откуда: Moscow
Сообщений: 907
мимокрокодил222
при read committed запросто можно нарушить это самое ограничение.


Объясните. Почему ?
24 дек 14, 16:28    [17047143]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать такое ограничение ?  [new]
ProBiotek
Member

Откуда: Moscow
Сообщений: 907
Слушайте. А может стоит это организовать на уровне тригера ?

Мне не нравится решение с функцией, т.к. она не получает ID вставляемых данных. Придется каждый раз селектить всю таблицу на предмет проверки...

Да. И вообще, если в таблице УЖЕ нарушено ограничение, то функция всегда будет возвращать False ??
Как сделать, чтобы функция проверяла Ограничение только для ново-вставляемых значений ?
24 дек 14, 16:32    [17047159]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать такое ограничение ?  [new]
мимокрокодил222
Guest
ProBiotek
мимокрокодил222
при read committed запросто можно нарушить это самое ограничение.


Объясните. Почему ?


тупанул, сори. с вычислимой колонкой попутал.
24 дек 14, 16:32    [17047162]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать такое ограничение ?  [new]
ProBiotek
Member

Откуда: Moscow
Сообщений: 907
мимокрокодил222,

Я не искал ошибки в вашем ответе. Вы МОЖЕТ быть правы, я просто хотел понять ПОЧЕМУ. Чисто для понимания.

Ну раз Вы говорите, что все ок, то ок.
24 дек 14, 16:37    [17047196]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать такое ограничение ?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
ProBiotek
Имеется связь 1к1 на уровне четко двух таблиц: UserTable-MainTable и OrganizationTable-MainTable
Т.е. одной записи в UserTable имеется записи, ссылающаяся на нее, в таблице MainTable. И также с OrganizationTable-MainTable.

Теперь, как раз, это и нужно оформить в виде Констреинтов в БД.

Я Вас не понимаю...


В моем понимании 1 к 1 - это когда одной записи в таблицу А соответствует строго одна запись в таблице Б. Т.е. не 0 или 1 , а всегда 1.

Создать два внешних ключа можно в Вашей ситуации и все будет полноценно работать, за исключением того, то ID в подчиненных таблицах могут пересекаться. Чтобы этого не было, как я писал, для получения ID используйте общий SEQENCE.
24 дек 14, 17:36    [17047651]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать такое ограничение ?  [new]
ProBiotek
Member

Откуда: Moscow
Сообщений: 907
Владислав Колосов,

Ну, в принципе, да. Можно сделать общий сиквенс. Это позволит настроить внешние ключи от таблицу Пользователей-Организаций.

А как делается этот сиквенс ? В тригере ?
24 дек 14, 17:55    [17047786]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать такое ограничение ?  [new]
Владислав Колосов
Member

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

хоть в триггере, хоть в процедуре. Можете и дефолт в поле указать.
24 дек 14, 18:01    [17047826]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать такое ограничение ?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
Если сервер 2012 и выше. CREATE SEQUENCE и т.п.
24 дек 14, 18:02    [17047835]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать такое ограничение ?  [new]
ProBiotek
Member

Откуда: Moscow
Сообщений: 907
Владислав Колосов,

Спасибо
24 дек 14, 18:21    [17047930]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить