Foreign key with UNIQUE

Question

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

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

In this picture the field User_idUser (UNIQUE) it is a foreign key of the Table User, the next image is of the user table with an Inner Join

1. I don’t know if that applies to normalization, but that’s not a way of not repeating the data in the table?

2. And about the data that is there but in the bank but was not consulted with the inner join what we do with him?

Answer 1

You can, yes, add the restriction UNIQUE to any column you wish, including columns of foreign keys.

This is one of the possible strategies to force type 1 to 1 relationships into separate tables.

Doing this is not so common as 1 to 1 related data is usually 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 Storage of Sparse Matrices (http://www.inf.ufes.br/~luciac/mn1/storage-matrix-sparse.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 table column 1_id is Foreign key with restriction UNIQUE). And since Table 2 is very extensive (1000 columns), you saved storage space.

Let’s estimate the storage space savings above: you have left to store 1000 columns for 99.999.999 - 3 records, ie, 99.999.996.000 Unless values were not stored in the database. Assuming that each stored value saves 1 bit of space, we would have a savings 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 matrices.