termine - restaurar base de datos sql server 2014

Restricción para solo un registro marcado como predeterminado

Question

¿Cómo podría establecer una restricción en una tabla para que solo uno de los registros tenga su campo de bits isDefault en 1?

La restricción no es el alcance de la tabla, sino un valor predeterminado por conjunto de filas, especificado por un FormID.

Answer 1

Use un índice filtrado único

En SQL Server 2008 o superior, simplemente puede usar un índice único filtrado

CREATE UNIQUE INDEX IX_TableName_FormID_isDefault
    ON TableName(FormID)
    WHERE isDefault = 1

Donde está la mesa

CREATE TABLE TableName(
    FormID INT NOT NULL,
    isDefault BIT NOT NULL
)

Por ejemplo, si intenta insertar muchas filas con el mismo FormId y está configurado por defecto en 1, tendrá este error:

No se puede insertar una fila de clave duplicada en el objeto ''dbo.TableName'' con el índice único ''IX_TableName_FormID_isDefault''. El valor clave duplicado es (1).

Fuente: http://technet.microsoft.com/en-us/library/cc280372.aspx

Answer 2

@Andy Jones dio una respuesta más cercana a la mía, pero teniendo en cuenta la Regla de los Tres , puse la lógica directamente en el proceso almacenado que actualiza esta tabla. Esta fue mi solución simple. Si necesito actualizar la tabla desde otro lugar, moveré la lógica a un disparador. La regla predeterminada se aplica a cada conjunto de registros especificado por un FormID y un 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

Answer 3

Aquí hay una modificación de la solución de Damien_The_Unbeliever que permite un incumplimiento por 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

Pero la gente relacional seria le dirá que esta información debería estar en otra mesa.

CREATE TABLE form
FormID int NOT NULL PRIMARY KEY
DefaultWhateverID int FOREIGN KEY REFERENCES Whatever(ID)

Answer 4

Desde una perspectiva de normalización, esta sería una forma ineficiente de almacenar un solo hecho.

Optaría por mantener esta información en un nivel superior, almacenando (en una tabla diferente) una clave externa al identificador de la fila que se considera como el valor predeterminado.

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

Answer 5

Este es un proceso bastante complejo que no se puede manejar a través de una restricción simple.

Hacemos esto a través de un disparador. Sin embargo, antes de escribir el activador, debe poder responder varias cosas:

¿Queremos que falle el inserto si existe un valor predeterminado, cámbielo a 0 en lugar de 1 o cambie el valor predeterminado existente a 0 y deje este como 1? ¿Qué queremos hacer si el registro predeterminado se elimina y otros registros no predeterminados todavía están allí? ¿Hacemos uno por defecto? De ser así, ¿cómo determinamos cuál?

También deberá tener mucho, mucho cuidado para que el desencadenador controle el procesamiento de múltiples filas. Por ejemplo, un cliente puede decidir que todos los registros de un tipo particular deben ser los predeterminados. No cambiaría un millón de registros de a uno por vez, por lo que este desencadenante debe ser capaz de manejar eso. También necesita manejar eso sin bucles o el uso de un cursor (realmente no desea que el tipo de transacción discutida anteriormente lleve horas encerrando la tabla todo el tiempo).

También necesita un escenario de prueba muy extenso para este desencadenante antes de que se active. Debe probar: agregar un registro sin ningún valor predeterminado y es el primer registro para ese cliente que agrega un registro con un valor predeterminado y es el primer registro para ese cliente que agrega un registro sin ningún valor predeterminado y no es el primer registro de ese cliente agrega un registro con un valor predeterminado y no es el primer registro para ese cliente. La actualización de un registro tiene el valor predeterminado cuando no lo tiene ningún otro registro (suponiendo que no requiera que un registro esté siempre configurado como el predeterminado). un registro para eliminar el valor predeterminado. Eliminar el registro con la desactivación. Eliminar un registro sin el valor predeterminado. Realización de una inserción masiva con múltiples situaciones en los datos, incluidos dos registros que tienen configurada por defecto en 1 y todas las situaciones probadas al ejecutar inserciones de registro individuales. una actualización masiva con múltiples situaciones en los datos, incluidos dos registros que tienen configurado por defecto en 1 y todas las situaciones probadas al ejecutar actualizaciones de registros individuales. Realizar una eliminación masiva con múltiples situaciones en los datos, incluidos dos registros que tienen ambos configurados por defecto en 1 y todas las situaciones probadas cuando se ejecutan registros individuales se eliminan

Answer 6

La pregunta implica para mí que tiene una tabla primaria que tiene algunos registros secundarios y uno de esos registros secundarios será el registro predeterminado. El uso de la dirección y una tabla predeterminada por separado aquí es un ejemplo de cómo hacer que eso suceda utilizando la tercera forma normal. Por supuesto, no sé si es valioso responder a algo que es tan antiguo, pero me pareció fantástico.

--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

Answer 7

No sé sobre SQLServer. Pero si es compatible con los índices basados ​​en funciones como en Oracle, espero que esto se pueda traducir, si no, lo siento.

Puede hacer un índice como este en el supuesto de que el valor predeterminado es 1234 , la columna es DEFAULT_COLUMN y ID_COLUMN es la clave principal:

CREATE 
UNIQUE 
 INDEX only_one_default 
    ON my_table
     ( DECODE(DEFAULT_COLUMN, 1234, -1, ID_COLUMN) )

Este DDL crea una indización de índice única -1 si el valor de DEFAULT_COLUMN es 1234 e ID_COLUMN en cualquier otro caso. Entonces, si dos columnas tienen el valor DEFAULT_COLUMN , genera una excepción.

Answer 8

Podría usar un disparador de inserción / actualización .

Dentro del desencadenante después de una inserción o actualización, si el recuento de filas con isDefault = 1 es más de 1, deshaga la transacción.

Answer 9

Puede hacerlo a través de un en lugar de desencadenar, o si lo quiere como una restricción crear una restricción que hace referencia a una función que busca una fila que tiene el valor predeterminado en 1

EDITAR ups, debe ser <=

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)

Answer 10

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

Deberá tener las configuraciones ANSI correctas activadas para esto.