For example, I want to reference the id_2 of Table 1 in Table 2, I know I can do with primary keys but with other columns I do not know if it is possible.
Table 1: id (primary key of table 1), id_2, name
Table 2: table_id2 (primary key of table 2), id_2 (from table 1), name
Candidate Keys
Occurs when in a relation there is more than one combination of attributes having the unique identification property. The candidate key is only conceptual, that is, it is not implemented. What happens is that attributes with these characteristics could be primary since they are by nature unique identification.
Some examples: Registration, CPF, RG, Electoral Title, among others.
Source: second link.
Create a unique key ( Unique key ) in the id_2 field of the table 1 . This in addition to ensuring the integrity of your key, also allows reference to it.
Imagining a scenario where:
Table1: id (primary key of table 1), id_2, name, CATEGORY (new field)
Table2: table_id2 (primary key of table 2), id_2 (of table 1), name, CATEGORY (new field)
A query in these two tables using the category for comparison would look like this:
SELECT *
FROM Tabela1 T1, Tabela2 T2
WHERE T1.id_2 = T2.id_2
AND T1.CATEGORIA = T2.CATEGORIA
You always have to treat the relationships between primary and foreign keys in a SQL query.
But if you remove the foreign key it would look like this:
Table1: id (primary key of table 1), id_2, name, CATEGORY (new field)
Table2: table_id2 (table 2 primary key), name, CATEGORY (new field)
SELECT *
FROM Tabela1 T1, Tabela2 T2
WHERE T1.CATEGORIA = T2.CATEGORIA
Just remembering that if the fields allow null values your query would change. But I think you understood.
Abs.
Yes, but if your id_2 is null or you get duplicate items, you can have a NN relationship, if it does not have the UNIQUE attribute, which prevents its duplication and insertion of nulls, you end up with a non-normalized bank.
If you need to relate two tables where there may be many-to-many relationship (I do not know if this is your case) you can use an auxiliary table for this relationship, and in this new table you can use both columns as the primary key and would not need to reference column id_2 in table 1.
Consult some references on bank normalization that can explain you about this. If you do not need this rigor, especially in extremely large tables, requiring greater bank performance, the pdonatilio response can be your solution.