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?

  • Charge is not unique, which row would you like to reference? Second, I think you have a misunderstanding regarding constraints, there is no process, they do not call each other. In essence, they constrain what values you can use – Lennart Oct 19 at 9:35

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.

  • I think my problem is that in my data base i want to put 5 entities that connect themselves throught a minimun number of attributes, the data base im doing is not for me, it's for an organization that wants to re-build it's D.B. and the gestor, so the thing is, i want to make some tables where i can refference one or two attributes from another table, without catching all of those (theres 20 att. for table) and i wanted to look for a way to make this, so in that sense, i think you're right and i need to re-design from scratch the old D.B. – Squall32 Oct 19 at 16:00
  • Maybe in SQL can i extract the values from another table refferencing the PK, and just selecting the attributes i want to put in? or theres another thing in missing? Also, thanks for the answer, it was very complete :) – Squall32 Oct 19 at 16:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.