CODEWITHSUNDEEP
sql-serverconstraints
user3266033
user3266033

Reputation: 157

SQL field constraint

I have the below table and need some sort of constraint which only allows a client to only have one supplier distributor. Therefore if you tried to change the ProductDistribution field for SupplierClientID record '2' to true, it wouldn't work as that client (847) is already enabled for SupplierID 10.

Supplier Table

SupplierClientID  ClientID  SupplierID  ProductDistribution
1                 847         10        1
2                 847         11        0
3                 800         10        1
4                 800         11        0
5                 800         12        0
6                 570         11        1
7                 570         12        0

Is this possible by some sort of unique constraint?

Upvotes: 3

Views: 74

Answers (2)

Raul
Raul

Reputation: 3131

SQL Server offers unique Indexes.

Following Index will only allow unique combinations of client and supplier:

CREATE UNIQUE NONCLUSTERED INDEX NCUIX_Suplier_ClientSupplier
   ON dbo.Supplier (ClientID , SupplierID); 
GO

To allow one Client to have only once the ProductDistribution to 1, you would need another index:

CREATE UNIQUE NONCLUSTERED INDEX NCUIX_Suplier_ClientActiveDistribution
   ON dbo.Suplier (ClientID) WHERE ProductDistribution = 1 ; 
GO

If you don't want to have an index for your constraint, here is a broader approach using check constraints with a function:

CREATE FUNCTION CountClientProductDistributions(@ClientID INT)
RETURNS INT
AS 
BEGIN
   RETURN (SELECT COUNT(1) FROM dbo.Suplier WHERE ClientID = @ClientID AND ProductDistribution = 1)
END
GO

ALTER TABLE dbo.Suplier
ADD CONSTRAINT Suplier_CheckClientProductDistribution CHECK (dbo.CountClientProductDistributions(ClientID) <= 1);
GO

Read about Unique Indexes: https://msdn.microsoft.com/en-us/library/ms187019(v=sql.120).aspx

Read about Unique Constraints: http://www.w3schools.com/sql/sql_unique.asp

Read about Check Constraints: https://msdn.microsoft.com/en-us/library/ms190377(v=sql.120).aspx

Upvotes: 5

user3266033
user3266033

Reputation: 157

This worked too

 --Create index SupplierClients_ ProductDistribution
Create UNIQUE NONCLUSTERED INDEX NCUIX_Suplier_ClientSupplier
   ON dbo.SupplierClients (ClientID ) WHERE ProductDistribution = 1 ; 
GO

Upvotes: 1

Related Questions