CODEWITHSUNDEEP
sqlsql-servert-sql
Andrian Durlestean
Andrian Durlestean

Reputation: 1730

SQL custom logic in constraints

I need to create such custom constrain logic no duplicate combination in one period of time

CREATE FUNCTION [dbo].[CheckPriceListDuplicates](
@priceListId uniqueidentifier,
@supplierId uniqueidentifier,
@transportModeId uniqueidentifier,
@currencyId uniqueidentifier,
@departmentTypeId uniqueidentifier,
@consolidationModeId uniqueidentifier,
@importerId uniqueidentifier,
@exporterId uniqueidentifier,
@validFrom datetimeoffset(7),
@validTo datetimeoffset(7))
RETURNS int
AS
BEGIN
DECLARE @result int

IF EXISTS (SELECT * FROM [dbo].[PriceListEntries] AS [Extent1]
        WHERE ([Extent1].[Id] <> @priceListId) AND 
        ((([Extent1].[SupplierAddressBook_Id] IS NULL) AND (@supplierId IS NULL)) OR ([Extent1].[SupplierAddressBook_Id] = @supplierId)) AND 
        ([Extent1].[TransportMode_Id] = @transportModeId) AND 
        ([Extent1].[Currency_Id] = @currencyId) AND 
        ([Extent1].[DepartmentType_Id] = @departmentTypeId) AND 
        ((([Extent1].[ConsolidationMode_Id] IS NULL) AND (@consolidationModeId IS NULL)) OR ([Extent1].[ConsolidationMode_Id] = @consolidationModeId)) AND 
        ((([Extent1].[Importer_Id] IS NULL) AND (@importerId IS NULL)) OR ([Extent1].[Importer_Id] = @importerId)) AND 
        ((([Extent1].[Exporter_Id] IS NULL) AND (@exporterId IS NULL)) OR ([Extent1].[Exporter_Id] = @exporterId)) AND 
        ((@validFrom >= [Extent1].[ValidFrom]) OR (@validTo <= [Extent1].[ValidTo]))
    )
BEGIN
    SET @result = 0
END
ELSE
BEGIN
    SET @result = 1
END
RETURN @result
END

ALTER TABLE [dbo].[PriceListEntries]
ADD CONSTRAINT UniquCombinations CHECK ([dbo].[CheckPriceListDuplicates](
Id,
SupplierAddressBook_Id,
TransportMode_Id,
Currency_Id,
DepartmentType_Id,
ConsolidationMode_Id,
Importer_Id,
Exporter_Id,
ValidFrom,
ValidTo) = 1)

any idea how to do with out function?

Upvotes: 1

Views: 303

Answers (2)

TommCatt
TommCatt

Reputation: 5636

It's a generally accepted concept that business rules should not be enforced in the DB. This is also generally difficult to strictly enforce as there is a large amount of overlap between business rules and data integrity rules. A data integrity constraint may limit a field to an integer value between 5 and 20, but that is because some business rule somewhere stipulates those are the only valid values.

So the difference between a business rule and a constraint is usually de facto defined as: a business rule is something that can't be easily enforced with the built-in checks available in the database and a constraint can be.

But I would further narrow the definition to state that a business rule is liable to change and a constraint is more static. For example, the rule "a patron may have no more than 5 library items checked out at any one time" could well be easily enforced using database constraints. But the limit of 5 is arbitrary and could change at a moments notice. Therefore it should be defined as a business rule that should not be enforced at the database level.

If a structural or modeling change or enhancement/addition of a database feature makes a "business rule" easily enforceable in the database where it wasn't before, you still have to consider if the rule is rigidly defined such that it is not expected to change. The database should be the bedrock, the foundation of your data edifice. You don't want it shifting around a lot.

Upvotes: 1

Phil Cazella
Phil Cazella

Reputation: 854

One way to query multiple columns in a table and see if they are all (together) unique is to Concatenate the tested data together as a string.

Select CONCAT ( Col1, Col2, Col3) AS ConcateString 
from [TABLE_NAME] 
WHERE ConcateString = 'All_of_your_data_in_one_string';

https://msdn.microsoft.com/en-us/library/hh231515.aspx

If the result yields more than one result, it combination of the data is not unique.

Upvotes: 0

Related Questions