Logically determine a composite key in SQL

Question

I'm working with an MSSQL table that does not have a primary or unique key contstraint defined. There are two fields, lets call them xId and yId, that I believe together would be a composite key, but I want to confirm this by examining the data.

I'm thinking that I should be able to write a SQL count statement that I can compare to the total number of records on the table that would logically determine if the combination of xId and yId (or a third column id necessary) could in fact act as a composite key. However, I'm having trouble coming up with the right GROUP BY or other type of clause that would confirm or disprove this.

Any ideas?

score 4 · Accepted Answer · answered Nov 30 '11 at 22:15

4

Use group by and having:

select xid,yid
from table
group by xid,yid
having count(1) > 1

This will show any pairs that are non-unique, so if there are no rows returned its a good key.

answered Nov 30 '11 at 22:15

Jon Egerton

40,401
11
97
129

I like this soultion. Now I can examine the non-unique pairs to determine what I need to add to the key to make it unique. Thanks! – Paul Nov 30 '11 at 22:19

score 2 · Answer 2 · answered Nov 30 '11 at 22:12

Just do a count of the total rows of the table, and then do

select count(1)
from(
    select xid,yid
    from table
    group by xid,yid
)a;

if all pairs of xid and yid form a unique identifier, then the two numbers will be the same.

Alternatively, you could count the number of distinct pairs of xid and yid and find the largest such number:

select max(num_rows)
from(
    select xid,yid,count(1) as num_rows
    from table
    group by xid,yid
)a;

The result of this query is 1 if and only if (xid,yid) pairs form a unique identifier for your table.

That's what I was looking for. I was so close! Thanks! – Paul Nov 30 '11 at 22:16 — Paul, Nov 30 '11 at 22:16

score 0 · Answer 3 · answered Nov 30 '11 at 22:16

0

this will list all the problem combinations (if any) of xid,yid:

SELECT
    COUNT(*),xid,yid
    FROM YourTable
    GROUP BY xid,yid
    HAVING COUNT(*)>1

answered Nov 30 '11 at 22:16

KM.

101,727
34
178
212

As a general point of order, count(1) is preferable than count(*). Count omits rows where the named contents are null, so if you have rows where all cells are null, they get omitted. I believe you also get a minor performance improvement by using Count(1) – Jon Egerton Nov 30 '11 at 22:19
1

@jon, COUNT( * ) does *not* omit rows where every value is null. Results and performance should be just the same with COUNT(1) or COUNT( * ). Also if the columns in question permit nulls then they can't be part of a key. – nvogel Dec 01 '11 at 09:18

Logically determine a composite key in SQL

3 Answers3