how to do a SELECT with a composite key?

Question

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

Solution 1

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.

Solution 2

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.

Comments

[J Weezy]

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.

[paparazzo]

You gave up index seek for that?

[waroxx]

I needed it to be a specific enumeration, if I index the table it will give me a new one

[paparazzo]

A composite primary key is an index

[J Weezy]

@waroxx You might want to consider marking your question as answered.