Как я могу установить ограничение на таблицу, чтобы только одна из записей имела поле бит isDefault
, установленное в 1?
Ограничение - это не область таблицы, а одно значение по умолчанию для набора строк, заданное FormID.
Как я могу установить ограничение на таблицу, чтобы только одна из записей имела поле бит isDefault
, установленное в 1?
Ограничение - это не область таблицы, а одно значение по умолчанию для набора строк, заданное FormID.
Здесь приведена модификация решения Damien_The_Unbeliever, которая позволяет по умолчанию установить один из идентификаторов FormID.
CREATE VIEW form_defaults
AS
SELECT FormID
FROM whatever
WHERE isDefault = 1
GO
CREATE UNIQUE CLUSTERED INDEX ix_form_defaults on form_defaults (FormID)
GO
Но серьезные реляционные люди скажут вам, что эта информация должна быть только в другой таблице.
CREATE TABLE form
FormID int NOT NULL PRIMARY KEY
DefaultWhateverID int FOREIGN KEY REFERENCES Whatever(ID)
В SQL Server 2008 или выше вы можете просто использовать уникальный отфильтрованный индекс
CREATE UNIQUE INDEX IX_TableName_FormID_isDefault
ON TableName(FormID)
WHERE isDefault = 1
Где таблица
CREATE TABLE TableName(
FormID INT NOT NULL,
isDefault BIT NOT NULL
)
Например, если вы попытаетесь вставить много строк с теми же FormId и isDefault, установленными в 1, вы получите эту ошибку:
Невозможно вставить повторяющуюся строку ключа в объект "dbo.TableName" с уникальным index 'IX_TableName_FormID_isDefault'. Значение дублирующегося ключа равно (1).
Источник: http://technet.microsoft.com/en-us/library/cc280372.aspx
С точки зрения нормализации это будет неэффективным способом хранения одного факта.
Я бы предпочел сохранить эту информацию на более высоком уровне, сохранив (в другой таблице) внешний ключ для идентификатора строки, который считается по умолчанию.
CREATE TABLE [dbo].[Foo](
[Id] [int] NOT NULL,
CONSTRAINT [PK_Foo] PRIMARY KEY CLUSTERED
(
[Id] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[DefaultSettings](
[DefaultFoo] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[DefaultSettings] WITH CHECK ADD CONSTRAINT [FK_DefaultSettings_Foo] FOREIGN KEY([DefaultFoo])
REFERENCES [dbo].[Foo] ([Id])
GO
ALTER TABLE [dbo].[DefaultSettings] CHECK CONSTRAINT [FK_DefaultSettings_Foo]
GO
Вы можете использовать включить/обновить триггер.
Внутри триггера после вставки или обновления, если количество строк с isDefault = 1 больше 1, откат транзакции.
Я не знаю о SQLServer. Но если он поддерживает индексы на основе функций, как в Oracle, я надеюсь, что это можно перевести, если нет, извините.
Вы можете сделать такой индекс, который предположил, что значение по умолчанию 1234
, столбец DEFAULT_COLUMN
и ID_COLUMN
- первичный ключ:
CREATE
UNIQUE
INDEX only_one_default
ON my_table
( DECODE(DEFAULT_COLUMN, 1234, -1, ID_COLUMN) )
Этот DDL создает уникальную индексацию индекса -1
, если значение DEFAULT_COLUMN
равно 1234
и ID_COLUMN
в любом другом случае. Затем, если два столбца имеют значение DEFAULT_COLUMN
, оно вызывает исключение.
CREATE VIEW vOnlyOneDefault
AS
SELECT 1 as Lock
FROM <underlying table>
WHERE Default = 1
GO
CREATE UNIQUE CLUSTERED INDEX IX_vOnlyOneDefault on vOnlyOneDefault (Lock)
GO
Для этого вам нужно включить правильные настройки ANSI.
Вопрос подразумевает, что у вас есть первичная таблица с несколькими дочерними записями, и одна из этих дочерних записей будет записываться по умолчанию. Использование адреса и отдельной таблицы по умолчанию здесь является примером того, как это сделать, используя третью нормальную форму. Конечно, я не знаю, стоит ли отвечать на что-то старое, но это поразило мою фантазию.
--drop table dev.defaultAddress;
--drop table dev.addresses;
--drop table dev.people;
CREATE TABLE [dev].[people](
[Id] [int] identity primary key,
name char(20)
)
GO
CREATE TABLE [dev].[Addresses](
id int identity primary key,
peopleId int foreign key references dev.people(id),
address varchar(100)
) ON [PRIMARY]
GO
CREATE TABLE [dev].[defaultAddress](
id int identity primary key,
peopleId int foreign key references dev.people(id),
addressesId int foreign key references dev.addresses(id))
go
create unique index defaultAddress on dev.defaultAddress (peopleId)
go
create unique index idx_addr_id_person on dev.addresses(peopleid,id);
go
ALTER TABLE dev.defaultAddress
ADD CONSTRAINT FK_Def_People_Address
FOREIGN KEY(peopleID, addressesID)
REFERENCES dev.Addresses(peopleId, id)
go
insert into dev.people (name)
select 'Bill' union
select 'John' union
select 'Harry'
insert into dev.Addresses (peopleid, address)
select 1, '123 someplace' union
select 1,'work place' union
select 2,'home address' union
select 3,'some address'
insert into dev.defaultaddress (peopleId, addressesid)
select 1,1 union
select 2,3
-- so two home addresses are default now
-- try adding another default address to Bill and you get an error
select * from dev.people
join dev.addresses on people.id = addresses.peopleid
left join dev.defaultAddress on defaultAddress.peopleid = people.id and defaultaddress.addressesid = addresses.id
insert into dev.defaultaddress (peopleId, addressesId)
select 1,2
GO
Вы можете сделать это с помощью вместо триггера, или если вы хотите, чтобы это как ограничение создало ограничение, ссылающееся на функцию, которая проверяет строку, для которой по умолчанию установлено значение 1
ИЗМЕНИТЬ, должно быть < =
Create table mytable(id1 int, defaultX bit not null default(0))
go
create Function dbo.fx_DefaultExists()
returns int as
Begin
Declare @Ret int
Set @ret = 0
Select @ret = count(1) from mytable
Where defaultX = 1
Return @ret
End
GO
Alter table mytable add
CONSTRAINT [CHK_DEFAULT_SET] CHECK
(([dbo].fx_DefaultExists()<=(1)))
GO
Insert into mytable (id1, defaultX) values (1,1)
Insert into mytable (id1, defaultX) values (2,1)
Это довольно сложный процесс, который не может быть обработан с помощью простого ограничения.
Мы делаем это с помощью триггера. Однако перед записью триггера вам нужно ответить на несколько вопросов:
мы хотим сбить вставку, если существует по умолчанию, изменить ее на 0 вместо 1 или изменить существующий по умолчанию на 0 и оставить это как 1? что мы хотим сделать, если запись по умолчанию удалена, а другие записи по умолчанию по-прежнему отсутствуют? Делаем ли мы один по умолчанию, если да, то как мы определяем, какой из них?
Вам также нужно быть очень осторожным, чтобы заставить триггер обрабатывать несколько строк. Например, клиент может решить, что все записи определенного типа должны быть по умолчанию. Вы не изменили бы миллион записей по одному, поэтому этот триггер должен иметь возможность справиться с этим. Он также должен обрабатывать это без цикла или использования курсора (вы действительно не хотите, чтобы тип транзакции, описанной выше, занимал часы, блокируя таблицу все время).
Для этого триггера требуется очень большой сценарий для того, чтобы он появился в прямом эфире. Вам нужно проверить: добавление записи без дефолта, и это первая запись для этого клиента добавление записи по умолчанию, и это первая запись для этого клиента добавление записи без дефолта, и это не первая запись для этого клиента добавление записи по умолчанию, и это не первая запись для этого клиента Обновление записи, имеющей значение по умолчанию, когда никакая другая запись не имеет (при условии, что вы не требуете, чтобы одна запись всегда была установлена как deafault) Обновление записи для удаления значения по умолчанию Удаление записи с помощью deafult Удаление записи без использования по умолчанию Выполнение массовой вставки с несколькими ситуациями в данных, включая две записи, у которых есть isdefault, установленный на 1, и все ситуации, проверенные при запуске отдельных вставок записей Выполнение массового обновления с несколькими ситуациями в данных, включая две записи, у которых есть isdefault, установленный на 1, и все ситуации, проверенные при запуске отдельных обновлений записей Выполнение массового удаления с несколькими ситуациями в данных, включая две записи, у которых есть isdefault, установленный в 1, и все ситуации, протестированные при удалении отдельной записи,
@Энди Джонс дал ответ выше, ближайший к моему, но, имея в виду "Правило трех" , я поставил логику непосредственно в хранимый proc, который обновляет эту таблицу. Это было мое простое решение. Если мне нужно обновить таблицу из другого места, я переведу логику на триггер. Одно правило по умолчанию применяется к каждому набору записей, указанному FormID и ConfigID:
ALTER proc [dbo].[cpForm_UpdateLinkedReport]
@reportLinkId int,
@defaultYN bit,
@linkName nvarchar(150)
as
if @defaultYN = 1
begin
declare @formId int, @configId int
select @formId = FormID, @configId = ConfigID from csReportLink where ReportLinkID = @reportLinkId
update csReportLink set DefaultYN = 0 where isnull(ConfigID, @configId) = @configId and FormID = @formId
end
update
csReportLink
set
DefaultYN = @defaultYN,
LinkName = @linkName
where
ReportLinkID = @reportLinkId