Невозможно удалить объект так как на него ссылается ограничение foreign key

SQL-Ex blog

В ранних версиях SQL Server (до 2017) при удалении несуществующей таблицы (DROP TABLE t1;) вы получали сообщение об ошибке:
Не удалось удалить таблицу «t1″, так как она не существует или отсутствуют разрешения.»

В рамках интерактивного (чистого) SQL избежать возникновения ошибки в этом случае не получалось. Однако проблема легко решается процедурно (T-SQL):

IF object_id('t1') IS NOT NULL 
DROP TABLE t1;

Встроенная функция object_id возвращает идентификатор объекта, заданного именем, или же NULL, если такого имени не существует.
И вот в SQL Server 2017 появился оператор DROP TABLE IF EXISTS, который не вызывает ошибки, если удаляемой таблицы не существует, например:

DROP TABLE IF EXISTS t1;

Теперь предположим, что нам нужно удалить связанные таблицы. Рассмотрим следующий пример:

CREATE TABLE t1(id INT IDENTITY PRIMARY KEY); 
CREATE TABLE t2(id INT IDENTITY PRIMARY KEY, 
t1_id INT REFERENCES t1); 
GO 
INSERT INTO T1 DEFAULT VALUES; 
GO 3 -- выполним предыдущий пакет (т.е. вставку) 3 раза

SELECT * FROM t1;

INSERT INTO t2(t1_id) VALUES(1),(2); 
SELECT * FROM t2;

id	t1_id
1	1
2	2

Стандартное каскадное удаление

DROP TABLE t1 CASCADE;

работать не будет, т.к. оно еще не реализовано в SQL Server (но работает, например, в PostgreSQL), тогда как оператор

DROP TABLE t1;

вызовет следующую ошибку:
Невозможно удалить объект «t1», так как на него ссылается ограничение FOREIGN KEY.
Мы можем удалить связанные таблицы одним оператором, перечислив их через запятую (сначала подчиненную таблицу, а затем — главную):

DROP TABLE t2,t1;

Если указать таблицы в неправильном порядке, т.е. t1, t2, то при удалении t1 будет получена вышеприведенная ошибка, а потом будет удалена t2.
Если среди перечисленных таблиц (не обязательно две) могут быть отсутствующие, то тогда поможет новый оператор:

DROP TABLE IF EXISTS t2,t1;

Заметим, что оператор DROP . IF EXISTS может применяться и к другим объектам базы данных, например, представлениям и хранимым процедурам.
Возможно, что в следующих версиях SQL Server может появиться и оператор типа:

CREATE TABLE IF NOT EXISTS.

который будет создавать таблицу только в том случае, если такой не существует.

Обратные ссылки

Нет обратных ссылок

Комментарии

Показывать комментарии Как список | Древовидной структурой

Автор не разрешил комментировать эту запись

3. Создание таблиц и индексов

Целостность данных может быть обеспечена двумя способами.

1. Декларативный способ. Критерии, которым должны удовлетворять данные, задаются при определении объекта и являются частью определения базы данных.

Преимущества: контроль целостности выполняется автоматически MS SQL Server, такой способ полностью совместим со стандартом ISO SQL:2008.

1. Процедурный способ. Критерии описываются в пакетах операторов, выполнение которых и определяет целостность данных.

Преимущества: реализуется как на клиенте, так и на сервере с помощью различных программных средств.

Декларативная целостность

Реализуется при помощи ограничений, которые являются наиболее рекомендуемыми для обеспечения целостности данных. Каждый тип целостности обеспечивается соответствующим ограничением:

• PRIMARY KEY. Определение поля или группы полей в качестве первичного ключа позволяет уникально идентифицировать каждую запись таблицы, т.к. в этом случае недопустимы повторяющиеся и неопределенные (NULL) значения.
• FOREIGN KEY. Позволяет устанавливать связь между полями, содержащими идентичные данные. Данные в этом поле могут принимать значения, определенные в соответствующем первичном ключе, либо значения NULL.
• UNIQUE. Определяет уникальность данных в некотором поле таблицы, причем данное ограничение допускает значения NULL. В одной таблице разрешено применение несколько таких ограничений, поля с таким ограничением могут быть использованы в качестве внешних ключей.
• DEFAULT. Указывает значение поля по умолчанию, если значение явно не указано при вставке данных. Для поля можно применить только одно такое ограничение.
• CHECK. Накладывает ограничение на значение поля в виде логического выражения, что позволяет определить диапазон допустимых значений поля. Причем данное ограничение позволяет ссылаться на значения других полей.

Ссылочная целостность

Внешние ключи представляют собой столбцы или наборы столбцов, предназначенные для связывания каждой из строк дочерней таблицы, содержащей этот внешний ключ, со строкой родительской таблицы, содержащей соответствующее значение потенциального ключа. Стандарт SQL предусматривает механизм определения внешних ключей с помощью предложения FOREIGN KEY, а фраза REFERENCES определяет имя родительской таблицы, т.е. таблицы, где находится соответствующий потенциальный ключ. При использовании этого предложения система отклонит выполнение любых операторов INSERT или UPDATE, с помощью которых будет предпринята попытка создать в дочерней таблице значение внешнего ключа, не соответствующее одному из уже существующих значений потенциального ключа родительской таблицы. Когда действия системы выполняются при поступлении операторов UPDATE и DELETE, содержащих попытку обновить или удалить значение потенциального ключа в родительской таблице, которому соответствует одна или более строк дочерней таблицы, то они зависят от правил поддержки ссылочной целостности, указанных во фразах ON UPDATE и ON DELETE предложения FOREIGN KEY. Если пользователь предпринимает попытку удалить из родительской таблицы строку, на которую ссылается одна или более строк дочерней таблицы, язык SQL предоставляет следующие возможности:

CASCADE — выполняется удаление строки из родительской таблицы, сопровождающееся автоматическим удалением всех ссылающихся на нее строк дочерней таблицы;

SET NULL — выполняется удаление строки из родительской таблицы, а во внешние ключи всех ссылающихся на нее строк дочерней таблицы записывается значение NULL;

SET DEFAULT — выполняется удаление строки из родительской таблицы, а во внешние ключи всех ссылающихся на нее строк дочерней таблицы заносится значение, принимаемое по умолчанию;

NO ACTION — операция удаления строки из родительской таблицы отменяется. Именно это значение используется по умолчанию в тех случаях, когда в описании внешнего ключа фраза ON DELETE опущена.

Те же самые правила применяются в языке SQL и тогда, когда значение потенциального ключа родительской таблицы обновляется.

Все эти ограничения могут находиться в одном операторе CREATE TABLE. Синтаксис этих ограничений таков:

PRIMARY KEY (col_name [, col_name2 [. col_name16]])

UNIQUE (col_name [, col_name2 [. col_name16]])

hibernate.hbm2ddl.auto doesn’t work if value equals create-drop

In my configuration — it helps me. But hibernate try drop all tables in random order. And I see following log:

INFO ClassPathXmlApplicationContext - Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@432a0f6c: startup date [Fri Sep 20 15:16:06 MSK 2013]; root of context hierarchy INFO XmlBeanDefinitionReader - Loading XML bean definitions from class path resource [test/BeanConfig.xml] INFO XmlBeanDefinitionReader - Loading XML bean definitions from class path resource [test/data.xml] INFO DefaultListableBeanFactory - Pre-instantiating singletons in org.springframework.beans.factory.support.DefaultListableBeanFactory@75be16f5: defining beans [org.springframework.context.annotation.internalConfigurationAnnotationProcessor,org.springframework.context.annotation.internalAutowiredAnnotationProcessor,org.springframework.context.annotation.internalRequiredAnnotationProcessor,org.springframework.context.annotation.internalCommonAnnotationProcessor,org.springframework.context.annotation.internalPersistenceAnnotationProcessor,candidateDao,eventDaoImpl,eventStatusDao,eventTypeDao,noteDao,skillDao,userDao,vacancyDao,org.springframework.aop.config.internalAutoProxyCreator,org.springframework.transaction.annotation.AnnotationTransactionAttributeSource#0,org.springframework.transaction.interceptor.TransactionInterceptor#0,org.springframework.transaction.config.internalTransactionAdvisor,transactionManager,dataSource,sessionFactory,org.springframework.context.annotation.ConfigurationClassPostProcessor.importAwareProcessor]; root of factory hierarchy INFO DriverManagerDataSource - Loaded JDBC driver: com.microsoft.sqlserver.jdbc.SQLServerDriver INFO Version - HCANN000001: Hibernate Commons Annotations INFO Version - HHH000412: Hibernate Core INFO Environment - HHH000206: hibernate.properties not found INFO Environment - HHH000021: Bytecode provider name : javassist INFO Configuration - HHH000044: Configuring from URL: file:/C:/Users/Nikolay_Tkachev/workspace/HHSystem/Core/target/classes/test/hibernate.cfg.xml INFO Configuration - HHH000041: Configured SessionFactory: null INFO Dialect - HHH000400: Using dialect: org.hibernate.dialect.SQLServerDialect INFO TransactionFactoryInitiator - HHH000399: Using default transaction strategy (direct JDBC transactions) INFO ASTQueryTranslatorFactory - HHH000397: Using ASTQueryTranslatorFactory INFO Version - HV000001: Hibernate Validator 4.3.1.Final INFO SchemaExport - HHH000227: Running hbm2ddl schema export Hibernate: alter table candidate drop constraint FK_0a095d3c05344292b630736d577 ERROR SchemaExport - HHH000389: Unsuccessful: alter table candidate drop constraint FK_0a095d3c05344292b630736d577 ERROR SchemaExport - FK_0a095d3c05344292b630736d577 не является ограничением. Hibernate: alter table candidate_skill drop constraint FK_f487e8a130cb44478cf45d2bed6 ERROR SchemaExport - HHH000389: Unsuccessful: alter table candidate_skill drop constraint FK_f487e8a130cb44478cf45d2bed6 ERROR SchemaExport - FK_f487e8a130cb44478cf45d2bed6 не является ограничением. Hibernate: alter table candidate_skill drop constraint FK_d5e271d145df45948a4928ef92f ERROR SchemaExport - HHH000389: Unsuccessful: alter table candidate_skill drop constraint FK_d5e271d145df45948a4928ef92f ERROR SchemaExport - FK_d5e271d145df45948a4928ef92f не является ограничением. Hibernate: alter table candidate_status drop constraint FK_26a9279492b14ebd9068df09af9 ERROR SchemaExport - HHH000389: Unsuccessful: alter table candidate_status drop constraint FK_26a9279492b14ebd9068df09af9 ERROR SchemaExport - FK_26a9279492b14ebd9068df09af9 не является ограничением. Hibernate: alter table candidate_status drop constraint FK_d6fda5bc80c140bb8053a15595b ERROR SchemaExport - HHH000389: Unsuccessful: alter table candidate_status drop constraint FK_d6fda5bc80c140bb8053a15595b ERROR SchemaExport - FK_d6fda5bc80c140bb8053a15595b не является ограничением. Hibernate: alter table candidate_status drop constraint FK_04345df148d644c2a458c48aa7a ERROR SchemaExport - HHH000389: Unsuccessful: alter table candidate_status drop constraint FK_04345df148d644c2a458c48aa7a ERROR SchemaExport - FK_04345df148d644c2a458c48aa7a не является ограничением. Hibernate: alter table event drop constraint FK_3221e14f1a5546aea264084277b ERROR SchemaExport - HHH000389: Unsuccessful: alter table event drop constraint FK_3221e14f1a5546aea264084277b ERROR SchemaExport - FK_3221e14f1a5546aea264084277b не является ограничением. Hibernate: alter table event drop constraint FK_1e065236f2aa41a886818431a74 ERROR SchemaExport - HHH000389: Unsuccessful: alter table event drop constraint FK_1e065236f2aa41a886818431a74 ERROR SchemaExport - FK_1e065236f2aa41a886818431a74 не является ограничением. Hibernate: alter table event drop constraint FK_8cd8c0859ba8443e9f6ec783473 ERROR SchemaExport - HHH000389: Unsuccessful: alter table event drop constraint FK_8cd8c0859ba8443e9f6ec783473 ERROR SchemaExport - FK_8cd8c0859ba8443e9f6ec783473 не является ограничением. Hibernate: alter table event_candidate drop constraint FK_04a181b9bf6842ce808631f5b26 ERROR SchemaExport - HHH000389: Unsuccessful: alter table event_candidate drop constraint FK_04a181b9bf6842ce808631f5b26 ERROR SchemaExport - FK_04a181b9bf6842ce808631f5b26 не является ограничением. Hibernate: alter table event_candidate drop constraint FK_60493ff24dc343bf9edd017b5f1 ERROR SchemaExport - HHH000389: Unsuccessful: alter table event_candidate drop constraint FK_60493ff24dc343bf9edd017b5f1 ERROR SchemaExport - FK_60493ff24dc343bf9edd017b5f1 не является ограничением. Hibernate: alter table event_vacancy drop constraint FK_87e5ee2c1ed24cabb2a0e8472e8 ERROR SchemaExport - HHH000389: Unsuccessful: alter table event_vacancy drop constraint FK_87e5ee2c1ed24cabb2a0e8472e8 ERROR SchemaExport - FK_87e5ee2c1ed24cabb2a0e8472e8 не является ограничением. Hibernate: alter table event_vacancy drop constraint FK_99ca9c03c1a34a0d8c4c0e62a5b ERROR SchemaExport - HHH000389: Unsuccessful: alter table event_vacancy drop constraint FK_99ca9c03c1a34a0d8c4c0e62a5b ERROR SchemaExport - FK_99ca9c03c1a34a0d8c4c0e62a5b не является ограничением. Hibernate: alter table note drop constraint FK_a30769362d244864aea0d90fa53 ERROR SchemaExport - HHH000389: Unsuccessful: alter table note drop constraint FK_a30769362d244864aea0d90fa53 ERROR SchemaExport - FK_a30769362d244864aea0d90fa53 не является ограничением. Hibernate: alter table note drop constraint FK_9ea4fdfb6a4249cd8fc4365b08e ERROR SchemaExport - HHH000389: Unsuccessful: alter table note drop constraint FK_9ea4fdfb6a4249cd8fc4365b08e ERROR SchemaExport - FK_9ea4fdfb6a4249cd8fc4365b08e не является ограничением. Hibernate: alter table note drop constraint FK_ef76fe39f36e41439347bbe0d50 ERROR SchemaExport - HHH000389: Unsuccessful: alter table note drop constraint FK_ef76fe39f36e41439347bbe0d50 ERROR SchemaExport - FK_ef76fe39f36e41439347bbe0d50 не является ограничением. Hibernate: alter table vacancy drop constraint FK_78a4a7e3d0484999aedab350f6c ERROR SchemaExport - HHH000389: Unsuccessful: alter table vacancy drop constraint FK_78a4a7e3d0484999aedab350f6c ERROR SchemaExport - FK_78a4a7e3d0484999aedab350f6c не является ограничением. Hibernate: alter table vacancy_candidate drop constraint FK_70b128e5e6a24a78b7b500feeb0 ERROR SchemaExport - HHH000389: Unsuccessful: alter table vacancy_candidate drop constraint FK_70b128e5e6a24a78b7b500feeb0 ERROR SchemaExport - FK_70b128e5e6a24a78b7b500feeb0 не является ограничением. Hibernate: alter table vacancy_candidate drop constraint FK_c9145cfab1c4417ba935cea8ca5 ERROR SchemaExport - HHH000389: Unsuccessful: alter table vacancy_candidate drop constraint FK_c9145cfab1c4417ba935cea8ca5 ERROR SchemaExport - FK_c9145cfab1c4417ba935cea8ca5 не является ограничением. Hibernate: alter table vacancy_skill drop constraint FK_66c9f60532da40249fe0286747d ERROR SchemaExport - HHH000389: Unsuccessful: alter table vacancy_skill drop constraint FK_66c9f60532da40249fe0286747d ERROR SchemaExport - FK_66c9f60532da40249fe0286747d не является ограничением. Hibernate: alter table vacancy_skill drop constraint FK_3e3cb30e74ca45f786bf86ad790 ERROR SchemaExport - HHH000389: Unsuccessful: alter table vacancy_skill drop constraint FK_3e3cb30e74ca45f786bf86ad790 ERROR SchemaExport - FK_3e3cb30e74ca45f786bf86ad790 не является ограничением. Hibernate: alter table vacancy_status drop constraint FK_f867af57b4374eb892c86b76076 ERROR SchemaExport - HHH000389: Unsuccessful: alter table vacancy_status drop constraint FK_f867af57b4374eb892c86b76076 ERROR SchemaExport - FK_f867af57b4374eb892c86b76076 не является ограничением. Hibernate: alter table vacancy_status drop constraint FK_da55313052974e3bafeb61dc55a ERROR SchemaExport - HHH000389: Unsuccessful: alter table vacancy_status drop constraint FK_da55313052974e3bafeb61dc55a ERROR SchemaExport - FK_da55313052974e3bafeb61dc55a не является ограничением. Hibernate: alter table vacancy_status drop constraint FK_958985ec3e7d43fcb8b01b12b0d ERROR SchemaExport - HHH000389: Unsuccessful: alter table vacancy_status drop constraint FK_958985ec3e7d43fcb8b01b12b0d ERROR SchemaExport - FK_958985ec3e7d43fcb8b01b12b0d не является ограничением. Hibernate: drop table author ERROR SchemaExport - HHH000389: Unsuccessful: drop table author ERROR SchemaExport - Невозможно удалить объект "author", так как на него ссылается ограничение FOREIGN KEY. Hibernate: drop table candidate ERROR SchemaExport - HHH000389: Unsuccessful: drop table candidate ERROR SchemaExport - Невозможно удалить объект "candidate", так как на него ссылается ограничение FOREIGN KEY. Hibernate: drop table candidate_skill Hibernate: drop table candidate_status Hibernate: drop table event ERROR SchemaExport - HHH000389: Unsuccessful: drop table event ERROR SchemaExport - Невозможно удалить объект "event", так как на него ссылается ограничение FOREIGN KEY. Hibernate: drop table event_candidate Hibernate: drop table event_status ERROR SchemaExport - HHH000389: Unsuccessful: drop table event_status ERROR SchemaExport - Невозможно удалить объект "event_status", так как на него ссылается ограничение FOREIGN KEY. Hibernate: drop table event_type ERROR SchemaExport - HHH000389: Unsuccessful: drop table event_type ERROR SchemaExport - Невозможно удалить объект "event_type", так как на него ссылается ограничение FOREIGN KEY. Hibernate: drop table event_vacancy Hibernate: drop table note Hibernate: drop table skill ERROR SchemaExport - HHH000389: Unsuccessful: drop table skill ERROR SchemaExport - Невозможно удалить объект "skill", так как на него ссылается ограничение FOREIGN KEY. Hibernate: drop table status_for_candidate Hibernate: drop table status_for_vacancy ERROR SchemaExport - HHH000389: Unsuccessful: drop table status_for_vacancy ERROR SchemaExport - Невозможно удалить объект "status_for_vacancy", так как на него ссылается ограничение FOREIGN KEY. Hibernate: drop table vacancy ERROR SchemaExport - HHH000389: Unsuccessful: drop table vacancy ERROR SchemaExport - Невозможно удалить объект "vacancy", так как на него ссылается ограничение FOREIGN KEY. Hibernate: drop table vacancy_candidate Hibernate: drop table vacancy_skill Hibernate: drop table vacancy_status Hibernate: create table author (id int identity not null, name varchar(255), surname varchar(255), login varchar(255) not null, password varchar(255), primary key (id)) ERROR SchemaExport - HHH000389: Unsuccessful: create table author (id int identity not null, name varchar(255), surname varchar(255), login varchar(255) not null, password varchar(255), primary key (id)) ERROR SchemaExport - В базе данных уже существует объект с именем "author". Hibernate: create table candidate (id int identity not null, name varchar(255), surname varchar(255), creation_date datetime, phone varchar(12), cv_url varchar(100), author_id int, primary key (id)) ERROR SchemaExport - HHH000389: Unsuccessful: create table candidate (id int identity not null, name varchar(255), surname varchar(255), creation_date datetime, phone varchar(12), cv_url varchar(100), author_id int, primary key (id)) ERROR SchemaExport - В базе данных уже существует объект с именем "candidate". Hibernate: create table candidate_skill (candidate_id int not null, skills_id int not null, primary key (candidate_id, skills_id)) Hibernate: create table candidate_status (id int identity not null, creation_date datetime, author_id int, candidate_id int, status_for_candidate_id int, primary key (id)) Hibernate: create table event (id int identity not null, creation_date datetime not null, description TEXT, event_date datetime not null, name varchar(255), author_id int, event_status_id int, event_type_id int, primary key (id)) ERROR SchemaExport - HHH000389: Unsuccessful: create table event (id int identity not null, creation_date datetime not null, description TEXT, event_date datetime not null, name varchar(255), author_id int, event_status_id int, event_type_id int, primary key (id)) ERROR SchemaExport - В базе данных уже существует объект с именем "event". Hibernate: create table event_candidate (events_id int not null, candidates_id int not null, primary key (events_id, candidates_id)) Hibernate: create table event_status (id int identity not null, name varchar(255) not null, primary key (id)) ERROR SchemaExport - HHH000389: Unsuccessful: create table event_status (id int identity not null, name varchar(255) not null, primary key (id)) ERROR SchemaExport - В базе данных уже существует объект с именем "event_status". Hibernate: create table event_type (id int identity not null, name varchar(255) not null, primary key (id)) ERROR SchemaExport - HHH000389: Unsuccessful: create table event_type (id int identity not null, name varchar(255) not null, primary key (id)) ERROR SchemaExport - В базе данных уже существует объект с именем "event_type". Hibernate: create table event_vacancy (events_id int not null, vacancies_id int not null, primary key (events_id, vacancies_id)) Hibernate: create table note (id int identity not null, creation_date datetime, text TEXT not null, author_id int, candidate_id int, vacancy_id int, primary key (id)) Hibernate: create table skill (id int identity not null, name varchar(255) not null, primary key (id)) ERROR SchemaExport - HHH000389: Unsuccessful: create table skill (id int identity not null, name varchar(255) not null, primary key (id)) ERROR SchemaExport - В базе данных уже существует объект с именем "skill". Hibernate: create table status_for_candidate (id int identity not null, name varchar(255) not null, primary key (id)) Hibernate: create table status_for_vacancy (id int identity not null, name varchar(255) not null, primary key (id)) ERROR SchemaExport - HHH000389: Unsuccessful: create table status_for_vacancy (id int identity not null, name varchar(255) not null, primary key (id)) ERROR SchemaExport - В базе данных уже существует объект с именем "status_for_vacancy". Hibernate: create table vacancy (id int identity not null, creation_date datetime not null, description TEXT, name varchar(30) not null, author_id int, primary key (id)) ERROR SchemaExport - HHH000389: Unsuccessful: create table vacancy (id int identity not null, creation_date datetime not null, description TEXT, name varchar(30) not null, author_id int, primary key (id)) ERROR SchemaExport - В базе данных уже существует объект с именем "vacancy". Hibernate: create table vacancy_candidate (vacancy_id int not null, candidate_id int not null, primary key (vacancy_id, candidate_id)) Hibernate: create table vacancy_skill (vacancy_id int not null, skill_id int not null, primary key (vacancy_id, skill_id)) Hibernate: create table vacancy_status (id int identity not null, creation_date datetime, author_id int, status_for_vacancy_id int, vacancy_id int, primary key (id)) Hibernate: alter table candidate add constraint FK_0a095d3c05344292b630736d577 foreign key (author_id) references author Hibernate: alter table candidate_skill add constraint FK_f487e8a130cb44478cf45d2bed6 foreign key (skills_id) references skill Hibernate: alter table candidate_skill add constraint FK_d5e271d145df45948a4928ef92f foreign key (candidate_id) references candidate Hibernate: alter table candidate_status add constraint FK_26a9279492b14ebd9068df09af9 foreign key (author_id) references author Hibernate: alter table candidate_status add constraint FK_d6fda5bc80c140bb8053a15595b foreign key (candidate_id) references candidate Hibernate: alter table candidate_status add constraint FK_04345df148d644c2a458c48aa7a foreign key (status_for_candidate_id) references status_for_candidate Hibernate: alter table event add constraint FK_3221e14f1a5546aea264084277b foreign key (author_id) references author Hibernate: alter table event add constraint FK_1e065236f2aa41a886818431a74 foreign key (event_status_id) references event_status Hibernate: alter table event add constraint FK_8cd8c0859ba8443e9f6ec783473 foreign key (event_type_id) references event_type Hibernate: alter table event_candidate add constraint FK_04a181b9bf6842ce808631f5b26 foreign key (candidates_id) references candidate Hibernate: alter table event_candidate add constraint FK_60493ff24dc343bf9edd017b5f1 foreign key (events_id) references event Hibernate: alter table event_vacancy add constraint FK_87e5ee2c1ed24cabb2a0e8472e8 foreign key (vacancies_id) references vacancy Hibernate: alter table event_vacancy add constraint FK_99ca9c03c1a34a0d8c4c0e62a5b foreign key (events_id) references event Hibernate: alter table note add constraint FK_a30769362d244864aea0d90fa53 foreign key (author_id) references author Hibernate: alter table note add constraint FK_9ea4fdfb6a4249cd8fc4365b08e foreign key (candidate_id) references candidate Hibernate: alter table note add constraint FK_ef76fe39f36e41439347bbe0d50 foreign key (vacancy_id) references vacancy Hibernate: alter table vacancy add constraint FK_78a4a7e3d0484999aedab350f6c foreign key (author_id) references author Hibernate: alter table vacancy_candidate add constraint FK_70b128e5e6a24a78b7b500feeb0 foreign key (candidate_id) references candidate Hibernate: alter table vacancy_candidate add constraint FK_c9145cfab1c4417ba935cea8ca5 foreign key (vacancy_id) references vacancy Hibernate: alter table vacancy_skill add constraint FK_66c9f60532da40249fe0286747d foreign key (skill_id) references skill Hibernate: alter table vacancy_skill add constraint FK_3e3cb30e74ca45f786bf86ad790 foreign key (vacancy_id) references vacancy Hibernate: alter table vacancy_status add constraint FK_f867af57b4374eb892c86b76076 foreign key (author_id) references author Hibernate: alter table vacancy_status add constraint FK_da55313052974e3bafeb61dc55a foreign key (status_for_vacancy_id) references status_for_vacancy Hibernate: alter table vacancy_status add constraint FK_958985ec3e7d43fcb8b01b12b0d foreign key (vacancy_id) references vacancy INFO SchemaExport - HHH000230: Schema export complete 

any messages — on my native language. These messages return jdbc driver. Gist of these messsages is that, table is has constraints with another table. Can you help me to find way for clear my database ?

Невозможно удалить объект так как на него ссылается ограничение foreign key

Типы данных сами по себе ограничивают множество данных, которые можно сохранить в таблице. Однако для многих приложений такие ограничения слишком грубые. Например, столбец, содержащий цену продукта, должен, вероятно, принимать только положительные значения. Но такого стандартного типа данных нет. Возможно, вы также захотите ограничить данные столбца по отношению к другим столбцам или строкам. Например, в таблице с информацией о товаре должна быть только одна строка с определённым кодом товара.

Для решения подобных задач SQL позволяет вам определять ограничения для столбцов и таблиц. Ограничения дают вам возможность управлять данными в таблицах так, как вы захотите. Если пользователь попытается сохранить в столбце значение, нарушающее ограничения, возникнет ошибка. Ограничения будут действовать, даже если это значение по умолчанию.

5.4.1. Ограничения-проверки

Ограничение-проверка — наиболее общий тип ограничений. В его определении вы можете указать, что значение данного столбца должно удовлетворять логическому выражению (проверке истинности). Например, цену товара можно ограничить положительными значениями так:

CREATE TABLE products ( product_no integer, name text, price numeric CHECK (price > 0) );

Как вы видите, ограничение определяется после типа данных, как и значение по умолчанию. Значения по умолчанию и ограничения могут указываться в любом порядке. Ограничение-проверка состоит из ключевого слова CHECK , за которым идёт выражение в скобках. Это выражение должно включать столбец, для которого задаётся ограничение, иначе оно не имеет большого смысла.

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

CREATE TABLE products ( product_no integer, name text, price numeric CONSTRAINT positive_price CHECK (price > 0) );

То есть, чтобы создать именованное ограничение, напишите ключевое слово CONSTRAINT , а за ним идентификатор и собственно определение ограничения. (Если вы не определите имя ограничения таким образом, система выберет для него имя за вас.)

Ограничение-проверка может также ссылаться на несколько столбцов. Например, если вы храните обычную цену и цену со скидкой, так вы можете гарантировать, что цена со скидкой будет всегда меньше обычной:

CREATE TABLE products ( product_no integer, name text, price numeric CHECK (price > 0), discounted_price numeric CHECK (discounted_price > 0), CHECK (price > discounted_price) );

Первые два ограничения определяются похожим образом, но для третьего используется новый синтаксис. Оно не связано с определённым столбцом, а представлено отдельным элементом в списке. Определения столбцов и такие определения ограничений можно переставлять в произвольном порядке.

Про первые два ограничения можно сказать, что это ограничения столбцов, тогда как третье является ограничением таблицы, так как оно написано отдельно от определений столбцов. Ограничения столбцов также можно записать в виде ограничений таблицы, тогда как обратное не всегда возможно, так как подразумевается, что ограничение столбца ссылается только на связанный столбец. (Хотя PostgreSQL этого не требует, но для совместимости с другими СУБД лучше следовать это правилу.) Ранее приведённый пример можно переписать и так:

CREATE TABLE products ( product_no integer, name text, price numeric, CHECK (price > 0), discounted_price numeric, CHECK (discounted_price > 0), CHECK (price > discounted_price) );

CREATE TABLE products ( product_no integer, name text, price numeric CHECK (price > 0), discounted_price numeric, CHECK (discounted_price > 0 AND price > discounted_price) );

Ограничениям таблицы можно присваивать имена так же, как и ограничениям столбцов:

CREATE TABLE products ( product_no integer, name text, price numeric, CHECK (price > 0), discounted_price numeric, CHECK (discounted_price > 0), CONSTRAINT valid_discount CHECK (price > discounted_price) );

Следует заметить, что ограничение-проверка удовлетворяется, если выражение принимает значение true или NULL. Так как результатом многих выражений с операндами NULL будет значение NULL, такие ограничения не будут препятствовать записи NULL в связанные столбцы. Чтобы гарантировать, что столбец не содержит значения NULL, можно использовать ограничение NOT NULL, описанное в следующем разделе.

Примечание

PostgreSQL не поддерживает ограничения CHECK , которые обращаются к данным, не относящимся к новой или изменённой строке. Хотя ограничение CHECK , нарушающее это правило, может работать в простых случаях, в общем случае нельзя гарантировать, что база данных не придёт в состояние, когда условие ограничения окажется ложным (вследствие последующих изменений других участвующих в его вычислении строк). В результате восстановление выгруженных данных может оказаться невозможным. Во время восстановления возможен сбой, даже если полное состояние базы данных согласуется с условием ограничения, по причине того, что строки загружаются не в том порядке, в котором это условие будет соблюдаться. Поэтому для определения ограничений, затрагивающих другие строки и другие таблицы, используйте ограничения UNIQUE , EXCLUDE или FOREIGN KEY , если это возможно.

Если вам не нужна постоянно поддерживаемая гарантия целостности, а достаточно разовой проверки добавляемой строки по отношению к другим строкам, вы можете реализовать эту проверку в собственном триггере. (Этот подход исключает вышеописанные проблемы при восстановлении, так как в выгрузке pg_dump триггеры воссоздаются после восстановления данных, и поэтому эта проверка не будет действовать в процессе выгрузки/восстановления.)

Примечание

В PostgreSQL предполагается, что условия ограничений CHECK являются постоянными, то есть при одинаковых данных в строке они всегда выдают одинаковый результат. Именно этим предположением оправдывается то, что ограничения CHECK проверяются только при добавлении или изменении строк, а не при каждом обращении к ним. (Приведённое выше предупреждение о недопустимости обращений к другим таблицам является частным следствием этого предположения.)

Однако это предположение может нарушаться, как часто бывает, когда в выражении CHECK используется пользовательская функция, поведение которой впоследствии меняется. PostgreSQL не запрещает этого, и если строки в таблице перестанут удовлетворять ограничению CHECK , это останется незамеченным. В итоге при попытке загрузить выгруженные позже данные могут возникнуть проблемы. Поэтому подобные изменения рекомендуется осуществлять следующим образом: удалить ограничение (используя ALTER TABLE ), изменить определение функции, а затем пересоздать ограничение той же командой, которая при этом перепроверит все строки таблицы.

5.4.2. Ограничения NOT NULL

Ограничение NOT NULL просто указывает, что столбцу нельзя присваивать значение NULL. Пример синтаксиса:

CREATE TABLE products ( product_no integer NOT NULL, name text NOT NULL, price numeric );

Ограничение NOT NULL всегда записывается как ограничение столбца и функционально эквивалентно ограничению CHECK ( имя_столбца IS NOT NULL) , но в PostgreSQL явное ограничение NOT NULL работает более эффективно. Хотя у такой записи есть недостаток — назначить имя таким ограничениям нельзя.

Естественно, для столбца можно определить больше одного ограничения. Для этого их нужно просто указать одно за другим:

CREATE TABLE products ( product_no integer NOT NULL, name text NOT NULL, price numeric NOT NULL CHECK (price > 0) );

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

Для ограничения NOT NULL есть и обратное: ограничение NULL . Оно не означает, что столбец должен иметь только значение NULL, что конечно было бы бессмысленно. Суть же его в простом указании, что столбец может иметь значение NULL (это поведение по умолчанию). Ограничение NULL отсутствует в стандарте SQL и использовать его в переносимых приложениях не следует. (Оно было добавлено в PostgreSQL только для совместимости с некоторыми другими СУБД.) Однако некоторые пользователи любят его использовать, так как оно позволяет легко переключать ограничения в скрипте. Например, вы можете начать с:

CREATE TABLE products ( product_no integer NULL, name text NULL, price numeric NULL );

и затем вставить ключевое слово NOT , где потребуется.

Подсказка

При проектировании баз данных чаще всего большинство столбцов должны быть помечены как NOT NULL.

5.4.3. Ограничения уникальности

Ограничения уникальности гарантируют, что данные в определённом столбце или группе столбцов уникальны среди всех строк таблицы. Ограничение записывается так:

CREATE TABLE products ( product_no integer UNIQUE, name text, price numeric );

в виде ограничения столбца и так:

CREATE TABLE products ( product_no integer, name text, price numeric, UNIQUE (product_no) );

в виде ограничения таблицы.

Чтобы определить ограничение уникальности для группы столбцов, запишите его в виде ограничения таблицы, перечислив имена столбцов через запятую:

CREATE TABLE example ( a integer, b integer, c integer, UNIQUE (a, c) );

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

Вы можете назначить уникальному ограничению имя обычным образом:

CREATE TABLE products ( product_no integer CONSTRAINT must_be_different UNIQUE, name text, price numeric );

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

Вообще говоря, ограничение уникальности нарушается, если в таблице оказывается несколько строк, у которых совпадают значения всех столбцов, включённых в ограничение. По умолчанию два значения NULL при таком сравнении не считаются равными. Это означает, что даже при наличии ограничения уникальности в таблице можно сохранить строки с дублирующимися значениями, если они содержат NULL в одном или нескольких столбцах ограничения. Это поведение можно изменить, добавив предложение NULLS NOT DISTINCT , например

CREATE TABLE products ( product_no integer UNIQUE NULLS NOT DISTINCT, name text, price numeric );

CREATE TABLE products ( product_no integer, name text, price numeric, UNIQUE NULLS NOT DISTINCT (product_no) );

Поведение по умолчанию можно выбрать явно, добавив NULLS DISTINCT . Согласно стандарту SQL вариант обработки значений NULL по умолчанию определяется реализацией, и в других СУБД встречается другое поведение. Это следует учитывать при разработке переносимых приложений.

5.4.4. Первичные ключи

Ограничение первичного ключа означает, что образующий его столбец или группа столбцов может быть уникальным идентификатором строк в таблице. Для этого требуется, чтобы значения были одновременно уникальными и отличными от NULL. Таким образом, таблицы со следующими двумя определениями будут принимать одинаковые данные:

CREATE TABLE products ( product_no integer UNIQUE NOT NULL, name text, price numeric );

CREATE TABLE products ( product_no integer PRIMARY KEY, name text, price numeric );

Первичные ключи могут включать несколько столбцов; синтаксис похож на запись ограничений уникальности:

CREATE TABLE example ( a integer, b integer, c integer, PRIMARY KEY (a, c) );

При добавлении первичного ключа автоматически создаётся уникальный индекс-B-дерево для столбца или группы столбцов, перечисленных в первичном ключе, и данные столбцы помечаются как NOT NULL .

Таблица может иметь максимум один первичный ключ. (Ограничений уникальности и ограничений NOT NULL, которые функционально почти равнозначны первичным ключам, может быть сколько угодно, но назначить ограничением первичного ключа можно только одно.) Теория реляционных баз данных говорит, что первичный ключ должен быть в каждой таблице. В PostgreSQL такого жёсткого требования нет, но обычно лучше ему следовать.

Первичные ключи полезны и для документирования, и для клиентских приложений. Например, графическому приложению с возможностями редактирования содержимого таблицы, вероятно, потребуется знать первичный ключ таблицы, чтобы однозначно идентифицировать её строки. Первичные ключи находят и другое применение в СУБД; в частности, первичный ключ в таблице определяет целевые столбцы по умолчанию для сторонних ключей, ссылающихся на эту таблицу.

5.4.5. Внешние ключи

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

Пусть у вас уже есть таблица продуктов, которую мы неоднократно использовали ранее:

CREATE TABLE products ( product_no integer PRIMARY KEY, name text, price numeric );

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

CREATE TABLE orders ( order_id integer PRIMARY KEY, product_no integer REFERENCES products (product_no), quantity integer );

С таким ограничением создать заказ со значением product_no , отсутствующим в таблице products (и не равным NULL), будет невозможно.

В такой схеме таблицу orders называют подчинённой таблицей, а products — главной. Соответственно, столбцы называют так же подчинённым и главным (или ссылающимся и целевым).

Предыдущую команду можно сократить так:

CREATE TABLE orders ( order_id integer PRIMARY KEY, product_no integer REFERENCES products, quantity integer );

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

Ограничению внешнего ключа можно назначить имя стандартным способом.

Внешний ключ также может ссылаться на группу столбцов. В этом случае его нужно записать в виде обычного ограничения таблицы. Например:

CREATE TABLE t1 ( a integer PRIMARY KEY, b integer, c integer, FOREIGN KEY (b, c) REFERENCES other_table (c1, c2) );

Естественно, число и типы столбцов в ограничении должны соответствовать числу и типам целевых столбцов.

Иногда имеет смысл задать в ограничении внешнего ключа в качестве « другой таблицы » ту же таблицу; такой внешний ключ называется ссылающимся на себя. Например, если вы хотите, чтобы строки таблицы представляли узлы древовидной структуры, вы можете написать

CREATE TABLE tree ( node_id integer PRIMARY KEY, parent_id integer REFERENCES tree, name text, . );

Для узла верхнего уровня parent_id будет равен NULL, пока записи с отличным от NULL parent_id будут ссылаться только на существующие строки таблицы.

Таблица может содержать несколько ограничений внешнего ключа. Это полезно для связи таблиц в отношении многие-ко-многим. Скажем, у вас есть таблицы продуктов и заказов, но вы хотите, чтобы один заказ мог содержать несколько продуктов (что невозможно в предыдущей схеме). Для этого вы можете использовать такую схему:

CREATE TABLE products ( product_no integer PRIMARY KEY, name text, price numeric ); CREATE TABLE orders ( order_id integer PRIMARY KEY, shipping_address text, . ); CREATE TABLE order_items ( product_no integer REFERENCES products, order_id integer REFERENCES orders, quantity integer, PRIMARY KEY (product_no, order_id) );

Заметьте, что в последней таблице первичный ключ покрывает внешние ключи.

Мы знаем, что внешние ключи запрещают создание заказов, не относящихся ни к одному продукту. Но что делать, если после создания заказов с определённым продуктом мы захотим удалить его? SQL справится с этой ситуацией. Интуиция подсказывает следующие варианты поведения:

Запретить удаление продукта

Удалить также связанные заказы

Для иллюстрации давайте реализуем следующее поведение в вышеприведённом примере: при попытке удаления продукта, на который ссылаются заказы (через таблицу order_items ), мы запрещаем эту операцию. Если же кто-то попытается удалить заказ, то удалится и его содержимое:

CREATE TABLE products ( product_no integer PRIMARY KEY, name text, price numeric ); CREATE TABLE orders ( order_id integer PRIMARY KEY, shipping_address text, . ); CREATE TABLE order_items ( product_no integer REFERENCES products ON DELETE RESTRICT, order_id integer REFERENCES orders ON DELETE CASCADE, quantity integer, PRIMARY KEY (product_no, order_id) );

Ограничивающие и каскадные удаления — два наиболее распространённых варианта. RESTRICT предотвращает удаление связанной строки. NO ACTION означает, что если зависимые строки продолжают существовать при проверке ограничения, возникает ошибка (это поведение по умолчанию). (Главным отличием этих двух вариантов является то, что NO ACTION позволяет отложить проверку в процессе транзакции, а RESTRICT — нет.) CASCADE указывает, что при удалении связанных строк зависимые от них будут так же автоматически удалены. Есть ещё два варианта: SET NULL и SET DEFAULT . При удалении связанных строк они назначают зависимым столбцам в подчинённой таблице значения NULL или значения по умолчанию, соответственно. Заметьте, что это не будет основанием для нарушения ограничений. Например, если в качестве действия задано SET DEFAULT , но значение по умолчанию не удовлетворяет ограничению внешнего ключа, операция закончится ошибкой.

Какой вариант действия ON DELETE выбрать — зависит от того, какие типы объектов представляются в связанных таблицах. Когда в подчинённой таблице представляется объект, который является составной частью сущности, представленной в главной таблице, и не может существовать независимо, уместно выбрать действие CASCADE . Если две таблицы представляют независимые объекты, более подходящим действием будет RESTRICT или NO ACTION ; тогда приложению, которому действительно нужно удалить оба объекта, потребуется сделать это явно и выполнить две команды удаления. В приведённом выше примере позиции заказа являются частью заказа, и будет удобно, если они удалятся автоматически при удалении заказа. Но продукты и заказы — разные вещи, поэтому автоматическое удаление некоторых позиций заказов при удалении продуктов может быть неприемлемым. Если же отношение внешнего ключа представляет необязательную информацию, подходящим может быть действие SET NULL или SET DEFAULT . Например, если в таблице продуктов содержится ссылка на менеджера продукта, и запись менеджера удаляется, может быть полезным установить в поле менеджера продукта значение NULL или значение по умолчанию.

Действия SET NULL и SET DEFAULT могут принимать список столбцов, которые получат соответствующие значения. Обычно значения присваиваются всем столбцам ограничения внешнего ключа; присвоение для подмножества столбцов полезно только в некоторых особых случаях. Рассмотрите следующий пример:

CREATE TABLE tenants ( tenant_id integer PRIMARY KEY ); CREATE TABLE users ( tenant_id integer REFERENCES tenants ON DELETE CASCADE, user_id integer NOT NULL, PRIMARY KEY (tenant_id, user_id) ); CREATE TABLE posts ( tenant_id integer REFERENCES tenants ON DELETE CASCADE, post_id integer NOT NULL, author_id integer, PRIMARY KEY (tenant_id, post_id), FOREIGN KEY (tenant_id, author_id) REFERENCES users ON DELETE SET NULL (author_id) );

Без указания столбца внешний ключ также установил бы значение NULL для столбца tenant_id , но этот столбец является частью первичного ключа.

Аналогично указанию ON DELETE существует ON UPDATE , которое срабатывает при изменении заданного столбца. При этом возможные действия те же, за исключением того, что для SET NULL и SET DEFAULT нельзя задать списки столбцов. CASCADE в данном случае означает, что изменённые значения связанных столбцов будут скопированы в зависимые строки.

Обычно зависимая строка не должна удовлетворять ограничению внешнего ключа, если один из связанных столбцов содержит NULL. Если в объявление внешнего ключа добавлено MATCH FULL , строка будет удовлетворять ограничению, только если все связанные столбцы равны NULL (то есть при разных значениях (NULL и не NULL) гарантируется невыполнение ограничения MATCH FULL ). Если вы хотите, чтобы зависимые строки не могли избежать и этого ограничения, объявите связанные столбцы как NOT NULL .

Внешний ключ должен ссылаться на столбцы, образующие первичный ключ или ограничение уникальности. Таким образом, для связанных столбцов всегда будет существовать индекс (определённый соответствующим первичным ключом или ограничением), а значит проверки соответствия связанной строки будут выполняться эффективно. Так как команды DELETE для строк главной таблицы или UPDATE для зависимых столбцов потребуют просканировать подчинённую таблицу и найти строки, ссылающиеся на старые значения, полезно будет иметь индекс и для подчинённых столбцов. Но это нужно не всегда, и создать соответствующий индекс можно по-разному, поэтому объявление внешнего ключа не создаёт автоматически индекс по связанным столбцам.

Подробнее об изменении и удалении данных рассказывается в Главе 6. Вы также можете подробнее узнать о синтаксисе ограничений внешнего ключа в справке CREATE TABLE .

5.4.6. Ограничения-исключения

Ограничения-исключения гарантируют, что при сравнении любых двух строк по указанным столбцам или выражениям с помощью заданных операторов, минимум одно из этих сравнений возвратит false или NULL. Записывается это так:

CREATE TABLE circles ( c circle, EXCLUDE USING gist (c WITH &&) );

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

Пред.	Наверх	След.
5.3. Генерируемые столбцы	Начало	5.5. Системные столбцы