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

Откуда:
Сообщений: 2267
Скрипт таблички:
create table t (f1 int not null, f2 int not null,
 CONSTRAINT [U_f1_f2] UNIQUE NONCLUSTERED 
(
	[f1] ASC,
	[f2] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
,
 CONSTRAINT [U_f2_f1] UNIQUE NONCLUSTERED 
(
	[f2] ASC,
	[f1] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]
go


Сделав два уникальных индекса пытался добиться невозможности ввести значения values (2,1):

insert into t values (1,2)
insert into t values (2,1)
select * from t

drop table t


те в моём понимании:

индекс U_f1_f2 даст комбинацию 12

индекс U_f2_f1 даст комбинацию 21

и при вводе insert into t values (2,1) должен сработать индекс U_f2_f1, но ... не сработал :)

Вопрос: какой ключ (CONSTRAINT) необходимо навесит на табличку, что бы нельзя было сделать

insert into t values (2,1)
26 фев 13, 18:10    [13984618]     Ответить | Цитировать Сообщить модератору
 Re: Уникальный ключ (CONSTRAINT)  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2423
PaulWist,

Индекс проверяет уникальность в строках а не в столбцах, вам нужен в данном случае check constraint например (f1>f2 )
26 фев 13, 18:14    [13984641]     Ответить | Цитировать Сообщить модератору
 Re: Уникальный ключ (CONSTRAINT)  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22550
вычислимое поле, в котором f1 и f2 отсортированы по умолчанию, а затем слиты в одну текстовую строку через запятую
26 фев 13, 18:15    [13984645]     Ответить | Цитировать Сообщить модератору
 Re: Уникальный ключ (CONSTRAINT)  [new]
PaulWist
Member

Откуда:
Сообщений: 2267
WarAnt
PaulWist,

Индекс проверяет уникальность в строках а не в столбцах, вам нужен в данном случае check constraint например (f1>f2 )


1. Э, насколько я понимаю комбинация:

CONSTRAINT [U_f1_f2] f1, f2 и CONSTRAINT [U_f2_f1] f2, f1 даёт именно для ОДНОЙ "строки" ДВА значения, 12 и 21, соотвественно новая запись которая даёт такой же результат для констрейнтна (либо 12 либо 21) должна отвергаться, но видать моё понимание неправильное :)

2. Check тут не пойдёт f1 и f2 - это FK.
27 фев 13, 09:14    [13986563]     Ответить | Цитировать Сообщить модератору
 Re: Уникальный ключ (CONSTRAINT)  [new]
Гость333
Member

Откуда:
Сообщений: 3683
PaulWist
1. Э, насколько я понимаю комбинация:

CONSTRAINT [U_f1_f2] f1, f2 и CONSTRAINT [U_f2_f1] f2, f1 даёт именно для ОДНОЙ "строки" ДВА значения, 12 и 21

Даёт, да.

PaulWist
соотвественно новая запись которая даёт такой же результат для констрейнтна (либо 12 либо 21) должна отвергаться

Должна отвергаться, да. Но отвергаться в пределах одного констрейнта. Вот вы вставляете вторую строку в таблицу со значениями 2, 1. Идёт проверка: в U_f1_f2 есть значение (2, 1)? Нет (там только 1, 2) — отлично. Дальше проверка: в U_f2_f1 есть значение (1, 2)? Опять же нет — значит, эти констрейнты (каждый по отдельности) соблюдаются и не мешают вставке. Сделать так, чтобы при проверке U_f1_f2 шёл поиск ещё и в U_f2_f1, невозможно.

PaulWist
2. Check тут не пойдёт f1 и f2 - это FK.

Тогда через триггер или как Паганель предложил.
27 фев 13, 09:29    [13986610]     Ответить | Цитировать Сообщить модератору
 Re: Уникальный ключ (CONSTRAINT)  [new]
PaulWist
Member

Откуда:
Сообщений: 2267
Паганель
вычислимое поле, в котором f1 и f2 отсортированы по умолчанию, а затем слиты в одну текстовую строку через запятую


Можно Вас попросить написать код, а как это должно выглядеть

CREATE TABLE [dbo].[t](
	[f1] [int] NOT NULL,
	[f2] [int] NOT NULL,
	[f3]  AS (Что сюда надо написать),
 CONSTRAINT [IX_t] UNIQUE NONCLUSTERED 
(
	[f3] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
27 фев 13, 09:30    [13986618]     Ответить | Цитировать Сообщить модератору
 Re: Уникальный ключ (CONSTRAINT)  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
Гость333
Сделать так, чтобы при проверке U_f1_f2 шёл поиск ещё и в U_f2_f1, невозможно.
Чой-то невозможно-то?
Сделать UDF - и вперёд!
Но тормозить, наверно, будет
27 фев 13, 09:33    [13986633]     Ответить | Цитировать Сообщить модератору
 Re: Уникальный ключ (CONSTRAINT)  [new]
Гость333
Member

Откуда:
Сообщений: 3683
PaulWist
Паганель
вычислимое поле, в котором f1 и f2 отсортированы по умолчанию, а затем слиты в одну текстовую строку через запятую


Можно Вас попросить написать код, а как это должно выглядеть

CREATE TABLE [dbo].[t](
	[f1] [int] NOT NULL,
	[f2] [int] NOT NULL,
	[f3]  AS (Что сюда надо написать),
 CONSTRAINT [IX_t] UNIQUE NONCLUSTERED 
(
	[f3] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Как-то так.
USE tempdb;
GO
CREATE TABLE [dbo].[t](
	[f1] [int] NOT NULL,
	[f2] [int] NOT NULL,
	[f3]  AS CASE WHEN f1 < f2 THEN CAST(f1 AS VARCHAR(11)) + ',' + CAST(f2 AS VARCHAR(11)) ELSE CAST(f2 AS VARCHAR(11)) + ',' + CAST(f1 AS VARCHAR(11)) END,
 CONSTRAINT [IX_t] UNIQUE NONCLUSTERED 
(
	[f3] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT dbo.t (f1, f2) VALUES (1, 2);
INSERT dbo.t (f1, f2) VALUES (2, 1);

SELECT * FROM dbo.t;
GO
DROP TABLE dbo.t;
GO

(1 row(s) affected)
Msg 2627, Level 14, State 1, Line 3
Violation of UNIQUE KEY constraint 'IX_t'. Cannot insert duplicate key in object 'dbo.t'. The duplicate key value is (1,2).
The statement has been terminated.
f1 f2 f3
----------- ----------- -----------------------
1 2 1,2

(1 row(s) affected)

Хотя при этом получается индекс по полю varchar(23). Не очень хорошо. Наверное, рациональнее склеивать два отсортированных int'а в один bigint.
27 фев 13, 09:42    [13986667]     Ответить | Цитировать Сообщить модератору
 Re: Уникальный ключ (CONSTRAINT)  [new]
Гость333
Member

Откуда:
Сообщений: 3683
iap
Сделать UDF - и вперёд!

Что-то не соображу, какая UDF?
27 фев 13, 09:48    [13986709]     Ответить | Цитировать Сообщить модератору
 Re: Уникальный ключ (CONSTRAINT)  [new]
PaulWist
Member

Откуда:
Сообщений: 2267
2 Гость333

Пойдёт, спасибо, ...с меня пиво !!!
27 фев 13, 10:08    [13986803]     Ответить | Цитировать Сообщить модератору
 Re: Уникальный ключ (CONSTRAINT)  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
Гость333
iap
Сделать UDF - и вперёд!

Что-то не соображу, какая UDF?
Скалярная:
USE tempdb;
if object_id(N't','U') is not null drop table t;
if object_id(N'Check_f2_f1','FN') is not null drop function Check_f2_f1;
create table t (f1 int not null, f2 int not null);
GO
CREATE FUNCTION dbo.Check_f2_f1(@f1 int,@f2 int) RETURNS INT AS BEGIN RETURN ISNULL((SELECT 1 FROM dbo.t WHERE f1=@f2 AND f2=@f1),0); END;
GO
ALTER TABLE t ADD CONSTRAINT uf1f2 UNIQUE(f1,f2);
ALTER TABLE t ADD CONSTRAINT cf1f2 CHECK(dbo.Check_f2_f1(f1,f2)=0);

insert into t values (1,2);
insert into t values (2,1);
27 фев 13, 10:09    [13986809]     Ответить | Цитировать Сообщить модератору
 Re: Уникальный ключ (CONSTRAINT)  [new]
PaulWist
Member

Откуда:
Сообщений: 2267
iap
Гость333
пропущено...

Что-то не соображу, какая UDF?
Скалярная:
USE tempdb;
if object_id(N't','U') is not null drop table t;
if object_id(N'Check_f2_f1','FN') is not null drop function Check_f2_f1;
create table t (f1 int not null, f2 int not null);
GO
CREATE FUNCTION dbo.Check_f2_f1(@f1 int,@f2 int) RETURNS INT AS BEGIN RETURN ISNULL((SELECT 1 FROM dbo.t WHERE f1=@f2 AND f2=@f1),0); END;
GO
ALTER TABLE t ADD CONSTRAINT uf1f2 UNIQUE(f1,f2);
ALTER TABLE t ADD CONSTRAINT cf1f2 CHECK(dbo.Check_f2_f1(f1,f2)=0);

insert into t values (1,2);
insert into t values (2,1);


Тоже вариант.

2 iap

А почему Вы высказали предположение, что будет тормозить?
27 фев 13, 10:21    [13986884]     Ответить | Цитировать Сообщить модератору
 Re: Уникальный ключ (CONSTRAINT)  [new]
Гость333
Member

Откуда:
Сообщений: 3683
iap
CHECK(dbo.Check_f2_f1(f1,f2)=0)

Так вот оно чо, Михалыч! :-)
Ну это всё равно не отменяет высказывания, что один unique constraint не может "залезать внутрь" другого unique constraint.
27 фев 13, 10:23    [13986898]     Ответить | Цитировать Сообщить модератору
 Re: Уникальный ключ (CONSTRAINT)  [new]
invm
Member

Откуда: Москва
Сообщений: 9845
iap
Скалярная:
USE tempdb;
if object_id(N't','U') is not null drop table t;
if object_id(N'Check_f2_f1','FN') is not null drop function Check_f2_f1;
create table t (f1 int not null, f2 int not null);
GO
CREATE FUNCTION dbo.Check_f2_f1(@f1 int,@f2 int) RETURNS INT AS BEGIN RETURN ISNULL((SELECT 1 FROM dbo.t WHERE f1=@f2 AND f2=@f1),0); END;
GO
ALTER TABLE t ADD CONSTRAINT uf1f2 UNIQUE(f1,f2);
ALTER TABLE t ADD CONSTRAINT cf1f2 CHECK(dbo.Check_f2_f1(f1,f2)=0);

insert into t values (1,2);
insert into t values (2,1);
Только так делать нельзя -- на RCSI и SI не сработает. Как минимум, нужно
CREATE FUNCTION dbo.Check_f2_f1(@f1 int,@f2 int) RETURNS INT AS BEGIN RETURN ISNULL((SELECT 1 FROM dbo.t with (readcommittedlock) WHERE f1=@f2 AND f2=@f1),0); END;
27 фев 13, 10:36    [13986987]     Ответить | Цитировать Сообщить модератору
 Re: Уникальный ключ (CONSTRAINT)  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
PaulWist
А почему Вы высказали предположение, что будет тормозить?
При вставке/редактировании миллиона записей функция будет вызвана миллион раз.
В триггере же можно сделать проверку одной инструкцией.
27 фев 13, 10:55    [13987104]     Ответить | Цитировать Сообщить модератору
 Re: Уникальный ключ (CONSTRAINT)  [new]
PaulWist
Member

Откуда:
Сообщений: 2267
iap
PaulWist
А почему Вы высказали предположение, что будет тормозить?
При вставке/редактировании миллиона записей функция будет вызвана миллион раз.
В триггере же можно сделать проверку одной инструкцией.


Ок, доходчиво :)
27 фев 13, 10:57    [13987112]     Ответить | Цитировать Сообщить модератору
 Re: Уникальный ключ (CONSTRAINT)  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
Кстати, если быть уж совсем точным, то в функции надо бы написать в SELECTе TOP(1).
ISNULL там необязательна - без неё надо в CHECK "=0" заменить на "IS NULL".

Однако, UNIQUE с вычисляемым полем всё равно лучше.
27 фев 13, 10:59    [13987117]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить