CODEWITHSUNDEEP
sqlsql-server
Harry A
Harry A

Reputation: 121

can a SQL UNIQUE constraint have conditions?

I need to modify the following constraint so that it doesnt apply to negative AMOUNTS. Is that possible? I've played around with the syntax in the SSMS for a while now and it hasn't likeed anything I've come up with.

ALTER TABLE Payments
ADD CONSTRAINT Unique_Payment_2021 UNIQUE (InvoiceID, Amount, TransDate, TransTime);

FYI, this is happening on a 2012 SQL server.

thanks Harry

Upvotes: 0

Views: 67

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

A unique constraint is implemented using a unique index. You can get the same functionality using a filtered index:

create unique index unq_payment_2021 on
    payments(InvoiceID, Amount, TransDate, TransTime)
    where amount >= 0;

One slight difference is the error message that you get when the condition is violated. In one case, it refers to a unique constraint and in another to a unique index.

Here is a db<>fiddle.

Upvotes: 2

Related Questions