How to connect two attributes without a PK (MySQL)

Posted on

Question :

I’m a new programmer and I’m doing a database on MySQL with phpmyadmin. I have this doubt – I know that if you want to link two different tables you need an attribute that is a Primary Key, and another in the other table that is a Foreign Key. In this particular case I want to link a normal attribute from one table to another, something like this:

Table 1: id (PK), age, birthday, name, Charge;

Table 2: id( PK), department, name, Charge (Reference);

So I can, for example, introduce the “charge” value to the first table, and then, when I visit the table 2, it would have that value as it was a FK calling a PK, or in other case, a way where I can link the PK of the table 1 with the table 2, so I can bring any attribute to it that I want like this:

Table 1: id1 (PK), age, birthday, name, Charge;

Table 2: id2, id2 (FK), department, name, Charge (Reference);

Is there any way to make this?

Answer :

You’re correct in that the most common way to join two tables is to use a foreign key referencing a primary key. But it is not a requirement. The SQL will work (i.e. will by syntactically correct) for any two columns you wish to use in your JOIN clause, as long as the columns’ types can be coerced into something comparable. The question is, will the comparison be meaningful (i.e. semantically correct)?

As an example you may choose to store shoe size and IQ for various employees. Then the query .. inner join .. on employee.shoesize = manager.IQ is a syntactically correct statement but is (likely!) meaningless.

Also think about what it means to join two tables. Conceptually it means you take each row from the first table in turn and combine it with every row from the second table. You retain those combined rows for which the join condition (the “ON” clause) is true and discard those for which it is false.

Back to your particular example. Do the meanings of Table1.Charge and Table2.Charge allow them to be compared and still satisfy the business rules of the system? Will the values in Table1.Charge be sufficiently unique to ensure only those rows which are meant to be matched will be matched? As a counter-example, if all charges were a standard amount then every row from Table1 would match every row from Table2.

If your can answer both questions as “yes” then go for it. If not you’ll have to re-design.

Leave a Reply

Your email address will not be published. Required fields are marked *