Skip to Main Content

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Connor McDonald

Thanks for the question, Geraldo.

Asked: October 29, 2019 - 3:33 pm UTC

Answered by: Connor McDonald - Last updated: November 05, 2019 - 3:59 am UTC

Category: Database Development - Version: 18.4

Viewed 100+ times

Whilst you are here, check out some content from the AskTom team: Learn SQL with this FREE Online Course

You Asked

Hello, Ask Tom Team.

I have a table A with columns (client_id,invoice_number,invoice_type) with a composite primary key (client_id,invoice_number). At business level there are 5 invoice_types (70,71,72,73,74). The invoice_number always brings the invoice_type taking position 2 an 3 of string. A client_id can have the same invoice_number as another client_id.

Data Example:

Table: A
client_id invoice_number invoice_type
123456 Z7000001 70
654321 Z7000001 70
123456 Z7100001 71
123456 Z7400001 74

Requirement:
Now, the business introduces a new invoice_type (75). This invoice_type can be repeated many times as client sends the data. There is no restriction here (primary key is broken here).

Table: A
client_id invoice_number invoice_type
123456 Z7000001 70
123456 Z7000003 70
654321 Z7000001 70
987654 Z7100001 71
123456 Z7400004 74
987654 Z7500001 75
987654 Z7100001 75

How can we accomplish this in the best possible way knowing that invoice_type (70,71,72,73,74) can not be repeated in the table?
What would be the suggested db design?

Thanks in advanced.

and we said...

In terms of a true *design* change, you would end up with another table to potentially track the multiple records for invoice type 75.

But in terms of a quick fix, as long as you have something that defines uniqueness for invoice type 75 records (lets say it was an transaction date/time), then you could adjust your uniqueness definition to be:

create unique index IX on tableA
(client,invoice_number, 
   case 
      when invoice_type in (70,71,72,73,74) then invoice_type
      when invoice_type = 75 then transaction_date 
   end
)



This could be done via the index as above, or you could add that case expression as a virtual column and define a standard uniqueness constraint using that.

and you rated our response

  (2 ratings)

Reviews

Modified answer

October 31, 2019 - 9:58 am UTC

Reviewer: vinod thupati from INDIA

It can be like below :

create unique index IX_A_test on test_a
(case when invoice_type = 75 then null else (client_id||invoice_number) end)
Connor McDonald

Followup  

November 01, 2019 - 1:09 am UTC

nice input

Review

November 03, 2019 - 12:13 pm UTC

Reviewer: Geraldo Peralta

Thanks for the response.
Connor McDonald

Followup  

November 05, 2019 - 3:59 am UTC

Glad we could help

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.