Как проверить, существует ли ограничение на сервере Sql?
У меня есть этот sql:
ALTER TABLE dbo.ChannelPlayerSkins
DROP CONSTRAINT FK_ChannelPlayerSkins_Channels
но, очевидно, в некоторых других базах данных, которые мы используем, ограничение имеет другое имя. Как я могу проверить, есть ли ограничение с именем FK_ChannelPlayerSkins_Channels,
15 ответов
Попробуй это:
SELECT
*
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
WHERE CONSTRAINT_NAME ='FK_ChannelPlayerSkins_Channels'
-- РЕДАКТИРОВАТЬ --
Когда я первоначально ответил на этот вопрос, я думал "Внешний ключ", потому что первоначальный вопрос задавался о поиске "FK_ChannelPlayerSkins_Channels". С тех пор многие люди прокомментировали нахождение других "ограничений", вот некоторые другие запросы для этого:
--Returns one row for each CHECK, UNIQUE, PRIMARY KEY, and/or FOREIGN KEY
SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_NAME='XYZ'
--Returns one row for each FOREIGN KEY constrain
SELECT *
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
WHERE CONSTRAINT_NAME='XYZ'
--Returns one row for each CHECK constraint
SELECT *
FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS
WHERE CONSTRAINT_NAME='XYZ'
вот альтернативный метод
--Returns 1 row for each CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY, and/or DEFAULT
SELECT
OBJECT_NAME(OBJECT_ID) AS NameofConstraint
,SCHEMA_NAME(schema_id) AS SchemaName
,OBJECT_NAME(parent_object_id) AS TableName
,type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT'
AND OBJECT_NAME(OBJECT_ID)='XYZ'
Если вам нужна дополнительная информация об ограничениях, загляните в системную хранимую процедуру master.sys.sp_helpconstraint чтобы увидеть, как получить определенную информацию. Для просмотра исходного кода с помощью SQL Server Management Studio перейдите в "Обозреватель объектов". Оттуда вы расширяете базу данных "Мастер", затем "Программируемость", затем "Хранимые процедуры", затем "Хранимые процедуры системы". Затем вы можете найти "sys.sp_helpconstraint" и щелкнуть по нему правой кнопкой мыши и выбрать "изменить". Просто будьте осторожны, чтобы не сохранить какие-либо изменения в нем. Кроме того, вы можете просто использовать эту системную хранимую процедуру для любой таблицы, используя ее как EXEC sp_helpconstraint YourTableNameHere,
Самый простой способ проверить наличие ограничения (а затем сделать что-то, например, удалить его, если он существует), это использовать функцию OBJECT_ID()...
IF OBJECT_ID('dbo.[CK_ConstraintName]', 'C') IS NOT NULL
ALTER TABLE dbo.[tablename] DROP CONSTRAINT CK_ConstraintName
OBJECT_ID может использоваться без второго параметра ("C" только для проверочных ограничений), и это также может работать, но если имя вашего ограничения совпадает с именем других объектов в базе данных, вы можете получить неожиданные результаты.
IF OBJECT_ID('dbo.[CK_ConstraintName]') IS NOT NULL
ALTER TABLE dbo.[tablename] DROP CONSTRAINT CK_ConstraintName
OBJECT_ID также может использоваться с другими "ограничениями", такими как ограничения внешнего ключа или ограничения первичного ключа и т. Д. Для достижения наилучших результатов всегда включайте соответствующий тип объекта в качестве второго параметра для функции OBJECT_ID:
Типы объектов ограничений:
- C = CHECK ограничение
- D = DEFAULT (ограничение или автономно)
- F = ограничение КЛЮЧЕВОЙ КЛЮЧ
- PK = ограничение первичного ключа
- R = правило (в старом стиле, автономное)
- UQ = УНИКАЛЬНОЕ ограничение
Также обратите внимание, что схема часто требуется. Схема ограничений обычно принимает схему родительской таблицы.
Неспособность поместить ваши ограничения (или что вы проверяете) в скобки при использовании этого метода также может привести к ложному отрицанию - если ваш объект использует необычные символы (такие как.), Скобки требуются.
Если вы ищете другой тип ограничения, например, по умолчанию, вы должны использовать другой запрос (Из Как найти ограничение по умолчанию, используя INFORMATION_SCHEMA? Ответил devio). Использование:
SELECT * FROM sys.objects WHERE type = 'D' AND name = @name
найти ограничение по умолчанию по имени.
В своем сообщении я собрал различные проверки " ЕСЛИ НЕ СУЩЕСТВУЕТ", условия " DDL" ЕСЛИ НЕ СУЩЕСТВУЕТ ", чтобы сделать сценарии SQL повторно запускаемыми"
IF (OBJECT_ID('FK_ChannelPlayerSkins_Channels') IS NOT NULL)
Вы смотрите на что-то вроде этого, ниже тестируется в SQL Server 2005
SELECT * FROM sys.check_constraints WHERE
object_id = OBJECT_ID(N'[dbo].[CK_accounts]') AND
parent_object_id = OBJECT_ID(N'[dbo]. [accounts]')
Просто что-то, чтобы остерегаться......
В SQL Server 2008 R2 SSMS команда "Ограничение сценария как -> DROP And CREATE To" создает T-SQL, как показано ниже
USE [MyDatabase]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DEF_Detail_IsDeleted]') AND type = 'D')
BEGIN
ALTER TABLE [Patient].[Detail] DROP CONSTRAINT [DEF_Detail_IsDeleted]
END
GO
USE [MyDatabase]
GO
ALTER TABLE [Patient].[Detail] ADD CONSTRAINT [DEF_Detail_IsDeleted] DEFAULT ((0)) FOR [IsDeleted]
GO
Из этого скрипта НЕ удаляется ограничение, поскольку SELECT возвращает 0 строк. (см. пост Microsoft Connect).
Имя ограничения по умолчанию неверно, но я полагаю, что оно также имеет отношение к функции OBJECT_ID, потому что изменение имени не решает проблему.
Чтобы это исправить, я удалил использование OBJECT_ID и вместо этого использовал имя ограничения по умолчанию.
(SELECT * FROM dbo.sysobjects WHERE [name] = (N'DEF_Detail_IsDeleted') AND type = 'D')
Я использую следующий запрос, чтобы проверить существующее ограничение, прежде чем создать его.
IF (NOT EXISTS(SELECT 1 FROM sysconstraints WHERE OBJECT_NAME(constid) = 'UX_CONSTRAINT_NAME' AND OBJECT_NAME(id) = 'TABLE_NAME')) BEGIN
...
END
Это запрашивает ограничение по имени, нацеливающемуся на данное имя таблицы. Надеюсь это поможет.
Начиная с SQL Server 2016, вы можете просто использоватьIF EXISTSключевые слова.
ALTER TABLE dbo.ChannelPlayerSkins
DROP CONSTRAINT IF EXISTS FK_ChannelPlayerSkins_Channels
Я использую SQL Server 2019, но здесь упоминается, что он был доступен с SQL Server 2016.
Документы SQL Server упоминают об этом здесь подALTER TABLEстранице, а не на этой странице Удалить ограничения проверки. Я не уверен, почему.
ЕСЛИ СУЩЕСТВУЕТ. Применяется к: SQL Server (SQL Server 2016 (13.x) и более поздние версии) и базе данных SQL Azure. Условно удаляет столбец или ограничение, только если он уже существует.
IF EXISTS(SELECT TOP 1 1 FROM sys.default_constraints WHERE parent_object_id = OBJECT_ID(N'[dbo].[ChannelPlayerSkins]') AND name = 'FK_ChannelPlayerSkins_Channels')
BEGIN
DROP CONSTRAINT FK_ChannelPlayerSkins_Channels
END
GO
IF EXISTS(SELECT 1 FROM sys.foreign_keys WHERE parent_object_id = OBJECT_ID(N'dbo.TableName'))
BEGIN
ALTER TABLE TableName DROP CONSTRAINT CONSTRAINTNAME
END
INFORMATION_SCHEMA твой друг. Он имеет все виды представлений, которые показывают все виды информации о схеме. Проверьте ваши системные представления. Вы найдете три взгляда на ограничения CHECK_CONSTRAINTS,
SELECT tabla.name as Tabla,
restriccion.name as Restriccion,
restriccion.type as Tipo,
restriccion.type_desc as Tipo_Desc
FROM {DATABASE_NAME}.sys.objects tabla
INNER JOIN {DATABASE_NAME}.sys.objects restriccion
ON tabla.object_id = restriccion.parent_object_id
WHERE tabla.type = 'U' - Solo tablas creadas por el usuario.
AND restriccion.type = 'UQ' --Tipo de Restriccion UNIQUE
ORDER BY tabla.name, restriccion.type_desc
Я использую это для проверки и удаленных ограничений на столбец. В нем должно быть все необходимое.
DECLARE
@ps_TableName VARCHAR(300)
, @ps_ColumnName VARCHAR(300)
SET @ps_TableName = 'mytable'
SET @ps_ColumnName = 'mycolumn'
DECLARE c_ConsList CURSOR LOCAL STATIC FORWARD_ONLY FOR
SELECT
'ALTER TABLE ' + RTRIM(tb.name) + ' drop constraint ' + sco.name AS csql
FROM
sys.Objects tb
INNER JOIN sys.Columns tc on (tb.Object_id = tc.object_id)
INNER JOIN sys.sysconstraints sc ON (tc.Object_ID = sc.id and tc.column_id = sc.colid)
INNER JOIN sys.objects sco ON (sc.Constid = sco.object_id)
where
tb.name=@ps_TableName
AND tc.name=@ps_ColumnName
OPEN c_ConsList
FETCH c_ConsList INTO @ls_SQL
WHILE (@@FETCH_STATUS = 0) BEGIN
IF RTRIM(ISNULL(@ls_SQL, '')) <> '' BEGIN
EXECUTE(@ls_SQL)
END
FETCH c_ConsList INTO @ls_SQL
END
CLOSE c_ConsList
DEALLOCATE c_ConsList
Вы можете использовать вышеупомянутое с одной оговоркой:
IF EXISTS(
SELECT 1 FROM sys.foreign_keys
WHERE parent_object_id = OBJECT_ID(N'dbo.TableName')
AND name = 'CONSTRAINTNAME'
)
BEGIN
ALTER TABLE TableName DROP CONSTRAINT CONSTRAINTNAME
END
Нужно использовать name = [Constraint name] поскольку таблица может иметь несколько внешних ключей и при этом не иметь проверяемый внешний ключ
В mySql вам нужно убедиться, что вы запрашиваете правильную базу данных! Следовательно
table_schema=DATABASE()
Вот мои функции, использующие knex, чтобы проверить, определен ли конкретный внешний ключ или индекс в конкретной базе данных и таблице.
const isFKExists = async (knex, tableName, fkName) => {
const result = await knex.raw(
`SELECT COUNT(*) AS 'isExists' FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE table_schema=DATABASE() AND table_name='${tableName}' AND CONSTRAINT_NAME='${fkName}' AND CONSTRAINT_TYPE = 'FOREIGN KEY'`
)
return (result[0][0].isExists === 1)
}
const isIndexExists = async (knex, tableName, indexName) => {
const result = await knex.raw(
`SELECT COUNT(*) AS 'isExists' FROM INFORMATION_SCHEMA.STATISTICS WHERE table_schema=DATABASE() AND table_name='${tableName}' AND index_name='${indexName}'`
)
return (result[0][0].isExists > 0)
}
Наслаждаться