4
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: id_tabela2 (primary key in Table 2), id_2 (from Table 1), name
4
This could become one key candidate, but primary key not.
Keys candidate
They occur when in a relation there is more than one combination of attributes possessing the unique identification property. The candidate key is only conceptual, that is, it is not implemented. What happens is that the attributes with this characteristics could be primary since they possess by nature the unique identification.
Some examples: Registration, CPF, RG, Voter Registration, among others.
Source: second link.
Your reply was very helpful Andrey. Thank you.
2
Create a unique key (Unique key) in the field id_2 of table 1. This in addition to ensuring the integrity of your key, also allows reference to it.
1
Yes, but if your id_2 is null or obtains items in duplicity, you can have an N-N relationship, if it does not have the UNIQUE attribute, which prevents its duplicity and null insertion, you end up having a non-standard database.
If you need to list two tables where there may be many to many relationships (I don’t know if this is your case) you can use an auxiliary table for this relationship, and in this new table you can use the two columns as the primary key and you wouldn’t need to reference the column id_2 in table 1.
See some references on bank normalization that can clarify you on this subject. In case you do not need this rigor, especially in extremely large tables, requiring greater bank performance, the pdonatilio response can be your solution
1
Imagining a scenario where:
Table1: id (primary key of table 1), id_2, name, CATEGORY (new field)
Table 2: id_table 2 (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 relations between primary and foreign keys in an SQL query.
But, if you remove the foreign key would look like this:
Table1: id (primary key of table 1), id_2, name, CATEGORY (new field)
Table2: id_table2 (primary key of table 2), 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 guess I could tell.
Abs.
Browser other questions tagged sql sql-server
You are not signed in. Login or sign up in order to post.
Why are they negatively?
– user7261
Just an @Andrey guess, maybe there are colleagues who unfortunately think that a basic question does not deserve recognition, even if it is such a common one. It is possible to reference almost any column (not blob), even if it is not unique! But if you are referencing an ID that is not PK revise your schema, if you are referencing one that is not even single review three times and call colleagues for a second opinion. Tip: Don’t forget to cover the column referenced with an index if there is no index
– jean