2

suppose I faced a bad-designed SQL server Database where designer didn't create any primary key for tables. Is there any standard or well-performance algorithm to find and then suggest primary key for each table in database? I don't want to generate any column as key or sth else, I just want to suggest high possible key for a table(e.g national number for a person where not declared in that table as primary key but its unique in all row)

Milad
  • 21
  • 4
  • Actually I prefer surrogate key(just add another column like ID identity). Natural keys often involeves seceral columns and tend to increase size of table more then just adding one additional column. Creating foreign keys are also pain. You should add all columns in another table etc. – Giorgi Nakeuri Nov 23 '15 at 07:45

1 Answers1

0

Is there any standard or well-performance [sic] algorithm to find and then suggest primary key for each table in database?

There's nothing better than brute force for this kind of problem. In the absence of any primary key or not null unique constraint, any combination of columns could be a candidate key. And, of course, there might be no candidate key at all.

The distinguishing characteristic of a candidate key is that it's unique (distinct) within a table. So you can query every combination of columns, and stop when you find a combination that returns the same number of rows the table has.

For example, suppose we have this table.

create table no_key (
  col_1 integer,
  col_2 varchar(15),
  col_3 varchar(10)
);

insert into no_key values
(1, 'Eh?', 'Oh, no.'),
(2, 'Eh?', 'Oh, no.'),
(3, 'Oh.', 'Not good.');

select count(*) from no_key;  -- The table has 3 rows.

You'd need to use dynamic SQL to generate all the possible queries. I'm just going to write them out by hand for this example.

-- Three columns taken one at a time.
select count(distinct col_1) from no_key; -- 3, a possible key
select count(distinct col_2) from no_key; -- 2, can't be a key
select count(distinct col_3) from no_key; -- 2, can't be a key

-- Three columns taken two at a time.
select count(distinct (col_1, col_2)) from no_key; -- 3, a possible key
select count(distinct (col_2, col_3)) from no_key; -- 2, can't be a key
select count(distinct (col_1, col_3)) from no_key; -- 3, a possible key

-- Three columns taken three at a time.
select count(distinct (col_1, col_2, col_3)) from no_key; -- 3, a possible key

You'd probably stop after select count(distinct col_1) from no_key;, and offer "col_1" to the user. But the next row inserted might be {3, 'Oh.', 'Oh, no.'}, in which case only (col_1, col_2, col_3) could be a key.

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185