CODEWITHSUNDEEP
sqlsql-server
Syntax Error
Syntax Error

Reputation: 1640

Constraints over multiple columns

Here is my table:

CREATE TABLE [dbo].[charges]
(
    [recordid] [bigint] IDENTITY(1,1) NOT NULL,
    [id] [bigint] NOT NULL,
    [chargeNumber] [int] NULL,
    [name] [nvarchar](100) NULL,
    [date] [datetime] NULL,
    [hunID] [bigint] NULL,

    CONSTRAINT [PK_charges] 
        PRIMARY KEY CLUSTERED ([recordid] ASC)
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[charges] WITH CHECK 
   ADD CONSTRAINT [CK_charges_Column_Range] 
       CHECK (([chargeNumber] >= (1) AND [chargeNumber] <= (3)))
GO

ALTER TABLE [dbo].[charges] CHECK CONSTRAINT [CK_charges_Column_Range]
GO

ALTER TABLE [dbo].[charges] 
   ADD CONSTRAINT [DF_charges_date]  
       DEFAULT (getutcdate()) FOR [date]
GO

How can I ensure that no duplicate charge numbers are allowed, per id? In the examples below I have omitted unnecessary columns. I can't figure out a constraint that would do this. Should I be doing this in the application rather than at database level?

I would allow rows like this, where each ID has 1-3 in the chargeNumber column:

+----------+----+--------------+--------+
| recordid | id | chargeNumber | name   |
+----------+----+--------------+--------+
| 1        | 7  | 1            | Peter  |
| 2        | 7  | 2            | Roger  |
| 3        | 7  | 3            | Emily  |
| 4        | 8  | 1            | Rachel |
| 5        | 8  | 2            | Dave   |
| 6        | 8  | 3            | Bob    |
+----------+----+--------------+--------+

but not like this:

+----------+----+--------------+--------+
| recordid | id | chargeNumber | name   |
+----------+----+--------------+--------+
| 1        | 7  | 1            | Peter  |
| 2        | 7  | 2            | Roger  |
| 3        | 7  | 3            | Emily  |
| 4        | 7  | 1            | Rachel |
| 5        | 8  | 1            | Dave   |
| 6        | 8  | 1            | Bob    |
+----------+----+--------------+--------+

Note the duplicate chargeNumbers for id's 7 and 8. recordid 4 and either 5/6, would not be allowed.

Upvotes: 0

Views: 33

Answers (2)

Fuzzy
Fuzzy

Reputation: 3810

This is what you need:

CREATE UNIQUE NONCLUSTERED INDEX [IDX_charges_id_chargeNumber] ON [dbo].[charges]
(
    [id] ASC,
    [chargeNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Upvotes: 0

Tab Alleman
Tab Alleman

Reputation: 31785

You could put a UNIQUE CONSTRAINT on the id & chargeNumber columns together.

Upvotes: 2

Related Questions