Foreign key with UNIQUE

Question

Foreign key with UNIQUE

What happens if I put a foreign key as UNIQUE in a table?

I tested this possibility but maybe I got it wrong, if we apply a UNIQUE in a foreign key and insert the wrong value in this table, this same value that was inserted will not be queried in inner join, because the id of Table 1 does not match the idof Table 2 (which would be the foreign key)

In this image the field User_idUser (UNIQUE) it is a foreign key of the table User, the next image is from the user table with an inner join

I don't know if this applies to normalization, but isn't this a way of not repeating the data in the table?
What about the data that is there present but in the bank but was not consulted with inner join What do we do with it?

2

mysql sql banco-de-dados chave-estrangeira tabela-banco-de-dados

Author: pagliuca, 2019-08-09

Source

1 answers

score 0 · Accepted Answer

You can instead add the constraint UNIQUE to any column you want, including columns of foreign keys.

This is one of the possible strategies for forcing relationships of type 1 to 1 in separate tables.

Doing this is not so common, as 1-to-1 related data is typically stored in the same database table.

But there are, yes, some real use cases, the main one being the case where the second table is very rarely populated. This scenario is related to the topic sparse array storage ( http://www.inf.ufes.br / ~ luciac / mn1 / sparse storage-arrays. pdf ), and is exemplified below:

Tabela 1
+------------------------------+
| id email                     |
+------------------------------+
| 1 [email protected]           |
| 2 [email protected]           |
| 3 [email protected]           |
| 4 [email protected]           |
| 5 [email protected]           |
| ...                          |
| 99999999 [email protected] |
+------------------------------+

Tabela 2
+----------------------------------------------------------------------+
| id tabela1_id coluna1 coluna2 coluna3 coluna4 coluna5 ... coluna1000 |
+----------------------------------------------------------------------+
| 1 5 1 0 1 0 1 ... 1                                                  |
| 2 9999 1 1 1 1 1 ... 0                                               |
| 3 999999 1 1 1 1 1 ... 0                                             |
+----------------------------------------------------------------------+

In the above scenario, you have 99,999,999 records in Table 1, but only 3 records in Table 2 (in this example, the table1_id column is foreign key with constraint UNIQUE). And since Table 2 is very extensive (1000 columns), you saved storage space.

Let's go estimate storage space savings above: you have stopped storing 1000 columns for 99.999.999 - 3 records, i.e. 99.999.996.000 unless values were not stored in the bank. Assuming that each non-stored value saves 1 bit of space, we would have a saving of more than 11 gigabytes (1000*(99999999-3)/8/1024/1024/1024).

Of course this example is only illustrative, as databases may have some optimizations to save storage space with sparse arrays.