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

Суть такая, при удалении колонки в таблице возникает исключение из-за того, что поле связано с полем другой таблицы.
Как удалить CONSTRAINT я знаю... не могу понять, как вытащить скриптом его название?
9 ноя 14, 10:13    [16817787]     Ответить | Цитировать Сообщить модератору
 Re: Как определить CONSTRAINT по колонке?  [new]
iap
Member

Откуда: Москва
Сообщений: 47144
SELECT OBJECT_DEFINITION(object_id),*
FROM sys.objects
WHERE type='C' AND parent_object_id=OBJECT_ID(N'ИмяТаблицуы','U');
9 ноя 14, 11:22    [16817859]     Ответить | Цитировать Сообщить модератору
 Re: Как определить CONSTRAINT по колонке?  [new]
aleks2
Guest
И когда народ привыкнет пользоваться стандартными вещами?
select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where TABLE_NAME = N'ИмяТаблицы'
9 ноя 14, 12:15    [16817922]     Ответить | Цитировать Сообщить модератору
 Re: Как определить CONSTRAINT по колонке?  [new]
Klemzig
Guest
aleks2,

У микрософта INFORMATION_SCHEMA несколько расходится с объективной реальностью. Хотя, может это просто стандарт такой, и они его реализуют буквально - не знаю, не интересовался.

P.S. А, ну да, ты же у нас гуглить не умеешь, тебе пример подвай. На, держи:
+
:setvar dbname tst
go
use master;
go
if db_id('$(dbname)') is not null
	drop database $(dbname);
go
create database $(dbname);
go
use $(dbname);
go
create table dbo.Parent (
	Id int identity(1,1) primary key,
	NK char(10) not null unique,
	ExtKey bigint not null,
	Title varchar(100) not null
);
go
create unique index [IX_Parent_ExtKey] on dbo.Parent(ExtKey);
go
create table dbo.Child1 (
	Id int identity(1,1) primary key,
	ParentId int not null references dbo.Parent (Id),
	Title varchar(100) not null
);
go
create table dbo.Child2 (
	Id int identity(1,1) primary key,
	ParentKey char(10) not null references dbo.Parent (NK),
	Title varchar(100) not null
);
go
create table dbo.Child3 (
	Id int identity(1,1) primary key,
	ParentExtKey bigint not null references dbo.Parent (ExtKey),
	Title varchar(100) not null
);
go
-- Get parent columns for all FKs
select rc.CONSTRAINT_NAME, rc.UNIQUE_CONSTRAINT_NAME, ccu.COLUMN_NAME
from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
	left join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu on rc.UNIQUE_CONSTRAINT_SCHEMA = ccu.CONSTRAINT_SCHEMA
		and rc.UNIQUE_CONSTRAINT_NAME = ccu.CONSTRAINT_NAME
go
10 ноя 14, 03:17    [16820407]     Ответить | Цитировать Сообщить модератору
 Re: Как определить CONSTRAINT по колонке?  [new]
Сон Веры Павловны
Member

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

вы не понимаете разницы между собственно констрейнтом, т.е. декларативным обеспечением целостности, и физическим осуществлением целостности? Ваше поле ExtKey не является констрейнтом, оно уникально за счет наличия уникального индекса. Но как констрейнт оно не объявлено - попробуйте найти его в sys.key_constraints (куда обращается INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE посредством обращения к INFORMATION_SCHEMA.KEY_COLUMN_USAGE).
+ INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
SET QUOTED_IDENTIFIER OFF
ALTER VIEW INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
AS
SELECT
	KCU.TABLE_CATALOG,
	KCU.TABLE_SCHEMA,
	KCU.TABLE_NAME,
	KCU.COLUMN_NAME,
	KCU.CONSTRAINT_CATALOG,
	KCU.CONSTRAINT_SCHEMA,
	KCU.CONSTRAINT_NAME
FROM	
	INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
UNION ALL
SELECT
	DB_NAME()				AS TABLE_CATALOG,
	SCHEMA_NAME(u.schema_id)		AS TABLE_SCHEMA,
	u.name					AS TABLE_NAME,
	col_name(d.referenced_major_id, d.referenced_minor_id)
						AS COLUMN_NAME,
	DB_NAME()				AS CONSTRAINT_CATALOG,
	SCHEMA_NAME(k.schema_id)		AS CONSTRAINT_SCHEMA,
	k.name					AS CONSTRAINT_NAME
FROM	
	sys.check_constraints k JOIN sys.objects u ON u.object_id = k.parent_object_id
	JOIN sys.sql_dependencies d ON d.class = 1
		AND d.object_id = k.object_id
		AND d.column_id = 0
		AND d.referenced_major_id = u.object_id
WHERE u.type <> 'TF' -- skip constraints in TVFs.
UNION ALL
SELECT
	DB_NAME()				AS TABLE_CATALOG,
	SCHEMA_NAME(t.schema_id)		AS TABLE_SCHEMA,
	t.name					AS TABLE_NAME,
	col_name(f.object_id, f.column_id)	AS COLUMN_NAME,
	DB_NAME()				AS CONSTRAINT_CATALOG,
	SCHEMA_NAME(r.schema_id)		AS CONSTRAINT_SCHEMA,
	r.name					AS CONSTRAINT_NAME
FROM
	sys.objects t JOIN sys.columns f ON f.object_id = t.object_id
	JOIN sys.objects r ON r.object_id = f.rule_object_id

+ INFORMATION_SCHEMA.KEY_COLUMN_USAGE
SET QUOTED_IDENTIFIER OFF
ALTER VIEW INFORMATION_SCHEMA.KEY_COLUMN_USAGE
AS
SELECT
	DB_NAME()				AS CONSTRAINT_CATALOG,
	SCHEMA_NAME(f.schema_id)		AS CONSTRAINT_SCHEMA,
	f.name					AS CONSTRAINT_NAME,
	DB_NAME()				AS TABLE_CATALOG,
	SCHEMA_NAME(p.schema_id)		AS TABLE_SCHEMA,
	p.name					AS TABLE_NAME,
	col_name(k.parent_object_id, k.parent_column_id)
						AS COLUMN_NAME,
	k.constraint_column_id			AS ORDINAL_POSITION
FROM
	sys.foreign_keys f JOIN sys.foreign_key_columns k
		ON k.constraint_object_id = f.object_id
	JOIN sys.tables	p ON p.object_id = f.parent_object_id
UNION
SELECT
	DB_NAME()				AS CONSTRAINT_CATALOG,
	SCHEMA_NAME(k.schema_id)		AS CONSTRAINT_SCHEMA,
	k.name					AS CONSTRAINT_NAME,
	DB_NAME()				AS TABLE_CATALOG,
	SCHEMA_NAME(t.schema_id)		AS TABLE_SCHEMA,
	t.name					AS TABLE_NAME,
	col_name(c.object_id, c.column_id)	AS COLUMN_NAME,
	c.key_ordinal				AS ORDINAL_POSITION
FROM
	sys.key_constraints k JOIN sys.index_columns c
		ON c.object_id = k.parent_object_id
		AND c.index_id = k.unique_index_id
	JOIN sys.tables t ON t.object_id = k.parent_object_id
10 ноя 14, 06:08    [16820438]     Ответить | Цитировать Сообщить модератору
 Re: Как определить CONSTRAINT по колонке?  [new]
Klemzig
Guest
Сон Веры Павловны,

Не буду я его там искать - зачем мне повторять чужие ошибки?
select object_name(fk.parent_object_id) as [Table], fk.name as [FK], c.name as [KeyColumn]
from sys.foreign_keys fk
	inner join sys.foreign_key_columns kc on fk.object_id = kc.constraint_object_id
	inner join sys.columns c on c.object_id = kc.referenced_object_id and c.column_id = kc.referenced_column_id;
Я на этом уже обжегся однажды, когда запросы к метаданным внезапно начали терять внешние ключи. А вот вам на пару с aleks2, судя по всему, все еще нужны шашечки.
10 ноя 14, 07:27    [16820472]     Ответить | Цитировать Сообщить модератору
 Re: Как определить CONSTRAINT по колонке?  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 6202
Klemzig
Я на этом уже обжегся однажды, когда запросы к метаданным внезапно начали терять внешние ключи. А вот вам на пару с aleks2, судя по всему, все еще нужны шашечки.

Они стали эти ключи "внезапно терять", потому что у внешних ключей не было в принципе ключей, куда они ссылались - они ссылались на поля с физически гарантированной уникальностью, но логически не декларированной. Ключей просто не было.
10 ноя 14, 07:55    [16820488]     Ответить | Цитировать Сообщить модератору
 Re: Как определить CONSTRAINT по колонке?  [new]
Klemzig
Guest
Сон Веры Павловны
Они стали эти ключи "внезапно терять", потому что у внешних ключей не было в принципе ключей, куда они ссылались - они ссылались на поля с физически гарантированной уникальностью, но логически не декларированной. Ключей просто не было.
Шашечки?..
11 ноя 14, 01:20    [16825696]     Ответить | Цитировать Сообщить модератору
 Re: Как определить CONSTRAINT по колонке?  [new]
o-o
Guest
Сон Веры Павловны
вы не понимаете разницы между собственно констрейнтом, т.е. декларативным обеспечением целостности, и физическим осуществлением целостности? Ваше поле ExtKey не является констрейнтом, оно уникально за счет наличия уникального индекса

подождите, а при чем тут ExtKey?
constraint (FK) тут объявлен, почему нет-то?
create table dbo.Child3 (
	Id int identity(1,1) primary key,
	ParentExtKey bigint not null references dbo.Parent (ExtKey),
	Title varchar(100) not null
);

FOREIGN KEY Constraints (1)
A foreign key (FK) is a column or combination of columns that is used to establish and enforce a link between the data in two tables. You can create a foreign key by defining a FOREIGN KEY constraint when you create or modify a table. -- ну вон же выше объявлено. согласно синтаксису (обязательное синим, необязательное жирным шрифтом):

<column_constraint> ::=
[ CONSTRAINT constraint_name ]
{ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
[
WITH FILLFACTOR = fillfactor
| WITH ( < index_option > [ , ...n ] )
]
[ ON { partition_scheme_name ( partition_column_name )
| filegroup | "default" } ]
| [ FOREIGN KEY ]
REFERENCES [ schema_name . ] referenced_table_name [ ( ref_column ) ]
[ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
[ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
[ NOT FOR REPLICATION ]
| CHECK [ NOT FOR REPLICATION ] ( logical_expression )
}
ссылка: CREATE TABLE (Transact-SQL) (2)


все там же (1) далее по тексту:
A FOREIGN KEY constraint does not have to be linked only to a PRIMARY KEY constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table.

снова обратимся к (2):

<column_constraint> ::=
[ CONSTRAINT constraint_name ]
{ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
[
WITH FILLFACTOR = fillfactor
| WITH ( < index_option > [ , ...n ] )
]
[ ON { partition_scheme_name ( partition_column_name )
| filegroup | "default" } ]
| [ FOREIGN KEY ]
REFERENCES [ schema_name . ] referenced_table_name [ ( ref_column ) ]
[ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
[ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
[ NOT FOR REPLICATION ]
| CHECK [ NOT FOR REPLICATION ] ( logical_expression )
}

это тоже декларативное объявление, а не физическое.
Klemzig не CREATE INDEX использовал, а еще как задекларировал UNIQUE constraint.

короче,
Klemzig +1
11 ноя 14, 02:12    [16825731]     Ответить | Цитировать Сообщить модератору
 Re: Как определить CONSTRAINT по колонке?  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 6202
o-o
Klemzig не CREATE INDEX использовал, а еще как задекларировал UNIQUE constraint.

Да, но только не поле ExtKey
Klemzig
  .....
create table dbo.Parent (
	Id int identity(1,1) primary key,
	NK char(10) not null unique,
	ExtKey bigint not null,
	Title varchar(100) not null
);
go
create unique index [IX_Parent_ExtKey] on dbo.Parent(ExtKey);
go
create table dbo.Child1 (
  .....


именно которое он не может найти по связке с foreign key constraint. Потому что у этого поля нет связки с констрейнтом - потому что констрейнта не существует. Если что, речь именно о констрейнте, связанном с полем ExtId. Остальные поля/констрейнты вполне себе находятся.
11 ноя 14, 06:01    [16825897]     Ответить | Цитировать Сообщить модератору
 Re: Как определить CONSTRAINT по колонке?  [new]
o-o
Guest
Сон Веры Павловны,

ааааа, точно, ночью надо спать
именно его он через индекс заюничил, извиняюсь.
ну тогда вообще все сходится, не помню, где написано, но типа INFORMATION SCHEMA это все по стандарту,
а системные представления -- уже с подробностями реализации
11 ноя 14, 08:26    [16826014]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить