Foreign key with UNIQUE

Asked

Viewed 289 times

2

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)

inserir a descrição da imagem aqui

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

inserir a descrição da imagem aqui

  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?

mysql sql database foreign-key
  • 2

    Boy who asks crazy, what kind of relationship should these two tables?

  • I think I will remove it as it will be out of scope, but the central question is: Would it be a problem if I insert a UNIQUE restriction into my foreign key?

  • 2

    Putting UNIQUE as a constraint has no influence on the junction, what happens is that you leave the relationship as being (0.1):1. In your case the meaning is that each user can only be related to a maximum of 1 cell phone.

  • 2

    It depends on the purpose ... because unique ensures that the relationship has only one code and cannot duplicate ... !!!

  • This model is strange because if the relation is 1:1 in theory should be all in the same table , but only understanding the problem to opine , but a field can be fk and Unique.

  • @Did my answer help you? If so, please mark the answer as "accept", so that your question exits the Stack Overflow unanswered question filters.

Show 1 more comment

1 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.

Browser other questions tagged mysql sql database foreign-key

You are not signed in. Login or sign up in order to post.