problem while configuring many to many relationship in sql management

Question

Dear,

I have one problem with a foreign key.

First of all I am going to explain the situation:

My database stores plants properties.

one plant has name, description, photo, etc and also, is related with other plants that can enhance the effect of herself.

and those plants can me also related with other plants so that we have a relationship between many to many.
"Imagine that one plant has some friends and those friends are also friends or other plants".

thats why I have one table named "MATERIA" (means plants) other "SINERGIA_MATERIA (synergies) and SINERGIAS (synergies)

one MATERIAS has many nynergies and one synergy can be in a lot of MATERIAS.

The problem arrives while creating the foreign key between SINERGIAS.UID_SINERGIA and SINERGIA_MATERIA.UID_SINERGIA:

this is what I would like to configure:

How can I solve this problem? thanks

Answer 1

It is not clear to me why there are two tables, Sinergias and Singerias_Materias. If I understand it correctly, you want to have a many-to-many table for relations between plants.

So that would be

ALTER TABLE Sinergias ADD 
   CONSTRAINT fk_Sinergias_Materias1
       FOREIGN KEY (uid_materia) REFERENECS Matierias (uid_Materia),
   CONSTRAINT fk_Sinergias_Materias2
       FOREIGN KEY (uid_synergia) REFERENECS Matierias (uid_Materia)

You cannot have a foreign key from the Materias table to the junction table. A foreign key serves to enforce that a reference exists.

Answer 2

Hi @Asier ,

Make sure both sides should have equal number of columns when you create relationship using SSMS in relationship dialog box.

One more thing, try to create relationship using T-SQL instead of SSMS GUI. Refer to Create a foreign key using T-SQL.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

thanks a lot