CODEWITHSUNDEEP
sqlsql-servert-sqlconstraints
David Waldron
David Waldron

Reputation: 162

t-SQL - constraint on one column that has multiple conditions

I am currently re structuring a poorly designed database, one of the things I want to do for integrity purposes is make a certain record unique dependent on a few variables.

The table I am using is Quotes, the column in question is the Quote number, what I need is for this to be unique to each Company and group, so for example Quote001 should be unique unless there is a new company / group used.

So you could have Quote001 with company1 and group1, but then if a new quote is made with company1 and group1 the new quote number should be Quote002. However if Company2 and group1 make a quote it should be back to Quote001.

I am unsure as to how to achieve this most effectively, using constraints in the database.

Thanks, David

Upvotes: 1

Views: 230

Answers (2)

Zohar Peled
Zohar Peled

Reputation: 82474

If you already have an identity column on this table, I would use it to create a computed column for the qoute number. If you don't alrady have one, you can add it.

To do that, first you need to define a UDF that will do the calculation for you:

CREATE FUNCTION dbo.CalculateQuoteNumber(@id int, @Company int, @Group int)
RETURNS int
AS
BEGIN
    RETURN 
    (
        SELECT COUNT(*) 
        FROM YourTable
        WHERE Company = @Company
        AND [Group] = @Group
        AND id <= @id            
    )
END
GO

Then, you add the Quote number as a calculated column:

ALTER TABLE YourTable
   ADD QuoteNumber as dbo.CalculateQuoteNumber(id, Company, [Group])

Now sql server will do the calculation for you, and you are safe since computed columns can't be inserted to or updated by the users.

You can see a live demo on rextester.

Upvotes: 4

Peter
Peter

Reputation: 850

You need a unique constraint.

            ALTER TABLE Quotes
            ADD CONSTRAINT ucQuote UNIQUE (QuoteNumber, Company, Group)

Upvotes: 2

Related Questions