4

I have a table with a composite key, and I want to do my SELECT query with that key, is there a way to put a name to the composite key or to do the query without having to add an AND

let's say:

SELECT * FROM MyTable WHERE PRIMARY KEY = 12345

being that primary key a composite key

waroxx
  • 73
  • 1
  • 1
  • 6
  • 1
    There is no reasonable way other than using `AND`. I mean, you could construct something equivalent, but `AND` is the way to go. – Gordon Linoff Apr 04 '18 at 14:57
  • Key or not, doesn't matter when you do select. – jarlh Apr 04 '18 at 14:57
  • You could, I suppose, add a calculated column to your tables definition with the combination of the columns, and query that in your `SELECT`. Not sure that'll actually help for performance though. – Thom A Apr 04 '18 at 15:00
  • You could add a computed column that combines the individual columns of the composite primary key. You'd likely want to add a separator or something similar to involve false positives. You'd also likely lose the benefits of indexes applied to the tables and over all, I'd suggest it is a really bad idea. – Mr Moose Apr 04 '18 at 15:05
  • Do you have a reason to want to do this, or it this just an "out of curiosity" question? – Eric Brandt Apr 04 '18 at 15:12

4 Answers4

5

No, there is no concept of SELECT * FROM MyTable WHERE PRIMARY KEY = 'PrimaryKeyName'. In other words, the predicate (WHERE clause) does not allow for this. You will need to list all columns that are contained within the composite primary key in the predicate.

A potential solution is to build a table that stores all primary key combinations for the fact table. You can add an identity field to that table and make it a foreign key constraint on your table. Then, your predicate uses the single foreign key column.

J Weezy
  • 3,507
  • 3
  • 32
  • 88
1

There are other ways you could write equivalent logic. For instance:

where not (key1 <> val1 or key2 <> val2)

or:

select t.*
from (select t.* from t where key1 = val1) t
where t.key2 = val2

or perhaps:

where t.key1 + '|' + t.key2 = val1 + '|' + val2

(assuming | is not ever in either value).

or:

where (case when t.key1 <> val1 then 0
            when t.key2 <> val2 then 0
            else 1
       end) = 1

or even:

where (case when t.key1 = val1 then 1 end) = (case when t.key2 = val2 then 1 end)

But there is no reasonable alternative to using AND.

I should note that tables that I create almost always have identity columns as primary keys. I'm not a fan of composite primary keys, so you might want to use an identity column.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I believe you may have misread the OP's question - they wanted to know if there was a command to for including the composite primary key within the predicate and then having the optimizer figure it out. – J Weezy Apr 04 '18 at 15:37
0

Maybe Computed column is the thing you are looking for.

Briefly, you can have another column, which is system generated based on your definition (here is the combination of each column of your composite key).

Then, when you query the table, you can query that Computed Column.

For example, for a Charge: you have ChargeCode and ChargeAmount, and those two are the composite key. Then the Computed Column will have the value (ChargeCode + ChargeAmount).

Charge example one: ChargeCode: A ChargeAmount: 100 (Composite key) If you write a query as usual: SELECT * FROM Table WHERE ChargeCode = 'A' AND ChargeAmount = '100'

If with Computed Column: ComputedColumn_Key: A100 (notice the data type conversion here for string concatenation)

Then the query will be SELECT * FROM TABLE WHERE ComputedColumn_Key = 'A100'

LONG
  • 4,490
  • 2
  • 17
  • 35
0

thanks to everybody for the brainstorm, it helped me.

I ended making a view concatenating the columns of the composite key in one new column and doing the SELECT query to that view.

view:

SELECT (CONVERT(VARCHAR,PK1) + CONVERT(VARCHAR, PK2)) AS NEW_ID, nameColumn FROM MyTable

query

SELECT nameColumn FROM viewName WHERE NEW_ID = 12345
waroxx
  • 73
  • 1
  • 1
  • 6