-1

I have a table with Id, AccountId, PeriodId, and Comment. I have a unique index for (AccountId, PeriodId). I want to create such a row which does not violate the unique index. AccountId and PeriodId cannot be null, they are foreign keys.

My only idea is to cross join the Account and Period table to get all valid combination and discard the already existing combinations and chose one from the rest?

Is it a good approach or is there a better way?

Update 1: Some of you wanted to know the reason. I need this functionality in a program which adds a new row to the current table and only allows the user to modify the row after it is already in the db. This program now uses a default constructor to create a new row with AccountId1 and PeriodId1, empty comment. If this combination is available then after insertion the user can change it and provide a meaningful comment (there can be at most one comment for each (AccountId, PeriodId). But if the combination is not available then the original insertion will fail. So my task is to give a combination to the program which is not used, so it can be safely inserted.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Istvan Heckl
  • 864
  • 10
  • 22
  • _and chose one from the rest_ That doesn't sound like a useful goal. What exactly does your table represent as an entity and why do you want to insert rows without any particular reason? What "comment" will you apply for all these rows that you want to insert? – SMor Mar 04 '22 at 21:12
  • To be able to provide meaningful assistance we need to understand what exactly you do with that record and why you wish to pre-populate the table. (And how you plan to handle the situation as more Accounts and Periods are added). – Dale K Mar 04 '22 at 21:23
  • 1
    Just a note, tables have *rows* not records. – Stu Mar 04 '22 at 21:31
  • Given its a one off, who cares if there is a better way? If it does what you need then all good. If it doesn't then let us know where you are stuck. – Dale K Mar 05 '22 at 05:02
  • @DaleK I just wonder if the unique index itself can report where is the first 'hole' in it. – Istvan Heckl Mar 05 '22 at 05:30
  • Nope... but even then, its a 2 minute job to write an the `cross join` query with a `not exists` - my guess is you've taken longer to ask this question than to do the job :) – Dale K Mar 05 '22 at 05:32
  • 1
    Please paste your table definitions. - run `SHOW CREATE TABLE table_name;` –  Mar 05 '22 at 07:05

1 Answers1

0

As it turns out my original idea is quick enough. This query returns an unused (AccountId, PeriodId).

select top 1 * 
from 
(
    select Account.Id as AccountId, [Period].Id as PeriodId
    from Account cross join [Period]

    except

    select AccountId, PeriodId
    from AccountPeriodFilename
) as T
Istvan Heckl
  • 864
  • 10
  • 22