?

Log in

No account? Create an account
 
 
28 Октябрь 2009 @ 21:11
Странная проблема с foreign keys в реляционных СУБД  
Есть у нас такая странная проблема - периодически у кого-то из клиентов начинает валиться TrackStudio.  Присылают анонимный бекап - у базы нарушена ссылочная целостность.

Такое происходит из-за того, что при необходимости удалить что-то TrackStudio не пытается отслеживать связи между данными, а просто удаляет. Если удалилось - значит ссылок на объект нет и все нормально. Но почему-то иногда СУБД позволяет удалять данные, на которые ссылаются foreign keys. Когда-то давно видели эту проблему только на MS SQL Server 2000 без SP4, думали локальный глюк. Но за последние годы видели такое и на MySQL, и на DB2, и на SQL Server 2005.

Вопрос - что это может быть ? Как вообще может быть, что foreign key есть, а данные удаляются ? Может ли пользователь как-то случайно отключить проверку foreign keys ? Можно ли обойти проверку foreign keys ? Приветствуются любые идеи.
 
 
 
aensidheaensidhe on Октябрь, 29, 2009 04:36 (UTC)
А можно посмотреть скрипт создания foreign key?

Просто у нас активно юзается MSSQL, базы растут до 10 и более гиг, таких проблем нет.
Максим Крамаренкоmaximkr on Октябрь, 29, 2009 07:21 (UTC)
Скрипт большой, выложил сюда
http://www.trackstudio.com/tse-35/trackstudio-mssql.sql

Т.е. мы сначала создаем таблицы, потом данные (генерятся на основе файла экспорта), потом ключи. Вчера вот пользователь прислал базу, импортирую локально. Создание таблиц проходит, данных - проходит, а с ключами (HSQLDB) выводит такие ругательства

------------------FAILED------------------
ALTER TABLE gr_message ADD CONSTRAINT fmessage_4 FOREIGN KEY (message_submitter) REFERENCES gr_user (user_id)
(
------------------------------------------
Integrity constraint violation - no parent 8a8a818e1905c8a9011906549184046e, table: GR_MESSAGE in statement [ALTER TABLE gr_message ADD CONSTRAINT fmessage_4 FOREIGN KEY (message_submitter) REFERENCES gr_user (user_id) ]
------------------FAILED------------------
ALTER TABLE gr_task ADD CONSTRAINT ftask_1 FOREIGN KEY (task_submitter) REFERENCES gr_user (user_id)
(
------------------------------------------
Integrity constraint violation - no parent 8a8a818e1905c8a9011906549184046e, table: GR_TASK in statement [ALTER TABLE gr_task ADD CONSTRAINT ftask_1 FOREIGN KEY (task_submitter) REFERENCES gr_user (user_id) ]
------------------FAILED------------------
ALTER TABLE gr_task ADD CONSTRAINT ftask_2 FOREIGN KEY (task_handler) REFERENCES gr_usersource (usersource_id)
(
------------------------------------------
Integrity constraint violation - no parent 8a8a818e19069c2b01190b8172480418, table: GR_TASK in statement [ALTER TABLE gr_task ADD CONSTRAINT ftask_2 FOREIGN KEY (task_handler) REFERENCES gr_usersource (usersource_id) ]
------------------FAILED------------------
ALTER TABLE gr_attachment ADD CONSTRAINT fattachment_2 FOREIGN KEY (attachment_user) REFERENCES gr_user (user_id)
(
------------------------------------------
Integrity constraint violation - no parent 8a8a81a323ec16f2012405772cb908df, table: GR_ATTACHMENT in statement [ALTER TABLE gr_attachment ADD CONSTRAINT fattachment_2 FOREIGN KEY (attachment_user) REFERENCES gr_user (user_id) ]

================================

У юзера вот что:
Microsoft SQL Server Management Studio 9.00.1399.00
Microsoft Analysis Services Client Tools 2005.090.1399.00
Microsoft Data Access Components (MDAC) 6.0.6002.18005 (lh_sp2rtm.090410-1830)
Microsoft MSXML 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 7.0.6002.18005
Microsoft .NET Framework 2.0.50727.4016
Operating System 6.0.6002
dapofig on Октябрь, 29, 2009 08:34 (UTC)
а у пользователя эти констрейнты в БД точно есть?
Максим Крамаренкоmaximkr on Октябрь, 29, 2009 08:39 (UTC)
Сейчас попробую спросить.
dapofig on Октябрь, 29, 2009 09:16 (UTC)
можно спросить результат запроса:

select * from sys.all_objects where type = 'F' and parent_object_id in (select object_id from sys.all_objects where name = 'gr_message')
Максим Крамаренкоmaximkr on Ноябрь, 5, 2009 15:35 (UTC)
Запросил, юзер молчит...
dapofig on Ноябрь, 18, 2009 13:38 (UTC)
а можно узнать почему в качестве ключей
вы выбрали строки а не обычный int?
Максим Крамаренкоmaximkr on Ноябрь, 18, 2009 14:44 (UTC)
Это для удобства репликации. Если были бы int-ы, то при переносе объекта с id-ником 5 из одной базы в другую ему придется не только назначать другой id-шник (свободный), но и править все ссылки у других импортируемых объектов. А с GUID-ами можно переносить данные и не задумываться об этом.
Максим Крамаренкоmaximkr on Декабрь, 17, 2009 11:26 (UTC)
Пользователь наконец отписал
===
There are no constraints currently on any table. Current tables can not have constraints do to SQL not allowing constraints to be placed on columns that are not primary keyed.
===

Насколько я понял, это значит что у него и первичных ключей нет...
Как такое может быть - решительно не понятно, мы создаем PK вместе с таблицами и никогда после этого не удаляем
CREATE TABLE gr_attachment ( attachment_id varchar(32) NOT NULL, attachment_task varchar(32) NOT NULL, attachment_user varchar(32) , attachment_name nvarchar(200) NOT NULL, attachment_description nvarchar(2000) , CONSTRAINT xpkgr_attachment PRIMARY KEY (attachment_id)) ;

Если бы тут что-то не сработало - у него вообще таблицы этой быть не должно.

Может есть в MS SQL какой способ копирования/переноса данных из одной БД в другую, при котором переносятся только данные, без PK/FK ?
dapofig on Декабрь, 17, 2009 21:03 (UTC)
ну теперь то хоть ясно что чуда не было :)
возможно кто то залез в базу и убил все ключи или как более реальный вариант БД перекачивали с одного сервера на другой:
а) через SQL скрипты при генерации которых ключи выкинули - потому что чтобы перекачать с ними надо таблицы в нужном порядке качать
б) через встроенный Data Transformation Services что посути тот же вариант а) только ориентированный на чайников.
Максим Крамаренкоmaximkr on Декабрь, 17, 2009 23:15 (UTC)
Да, пользователь подтверждает, что базу могли скопировать
To be honest I can not remember, but we may have because there has been a lot of servers being switched around. So it is a possibility that we did copy this database.

Интересно ,а для остальных "проблемных" СУБД (особенно MySQL и DB2) легко данные без ключей скопировать ?

Второй вопрос - есть ли какой универсальный (для всех СУБД) способ проверки на наличие ключей? Как вариант - при каждом запуске пытаться убить root-а или поменять ему значение PK, если получилось - ругаемся и отказываемся работать, но может есть способ лучше ?