-1

When I add a primary key to a table that already has data, what does each of these database management systems do?

Do they analyze each and every value of the column to confirm it is unique ? Or do they have some other optimized mechanism ? And if that's the case, what is that mechanism ?

João Amorim
  • 3
  • 4
  • 1
    "Do they analyze each and every value of the column to confirm it is unique ?" Short answer yes they do. – Raymond Nijland Mar 17 '18 at 23:09
  • but what's the difference between them? do any of them have some optimize method ? @RaymondNijland – João Amorim Mar 17 '18 at 23:11
  • Why are you asking about three different databases? – Gordon Linoff Mar 18 '18 at 00:14
  • @GordonLinoff because I need to know this for a college paper and I've searched a lot and I haven't found almost anything – João Amorim Mar 18 '18 at 00:21
  • @JoãoAmorim . . . I admit the answer might be buried in the documentation (for example, search for "add primary key" in https://www.postgresql.org/docs/current/static/sql-altertable.html). But the primary key requires a unique index, so there has to be some sort of index creation. That would probably fall under the category of "analyze". – Gordon Linoff Mar 18 '18 at 00:29

1 Answers1

1

Yes, at least in PostgreSQL and MySQL (probably MonetDB too) the DBMS will first check if all values are unique (like when you use a "unique" parameter in your sql query). You can simulate it by counting all rows and then counting a "unique" select of the same rows. If the row numbers are not equal, you will not be able to create the primary key. An index really is created, but only to speed things up when you use the primary key after its created.

Dionei Miodutzki
  • 657
  • 7
  • 16
  • The index is created (generally) as part of the primary key creation process. It is not created "only to speed things up". It enables the functionality of validating uniqueness. Admittedly, that could be done without a unique index. I know of no database that does not require a unique index for this purpose. – Gordon Linoff Mar 18 '18 at 01:03
  • Exactly, what I was trying to say is that the index that is part of the primary key will only be used after the primary key is created, it will not be used during th primary key creating process. – Dionei Miodutzki Mar 18 '18 at 01:09
  • so basically, they literally have to compare each value with the remaining ones and none of these DMS use some kind of optimized process ? – João Amorim Mar 18 '18 at 01:11
  • 1
    There's a lot of ways of doing a unique check, comparing each row with all the others rows would be a pretty bad choice. But all the rows will be checked, yes. – Dionei Miodutzki Mar 18 '18 at 01:27