question

Asier-9635 avatar image
0 Votes"
Asier-9635 asked Cathyji-msft commented

problem while configuring many to many relationship in sql management

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)
143561-sinergias.jpg



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:

143534-foto.png

this is what I would like to configure:
143562-captura.png

How can I solve this problem? thanks


sql-server-general
sinergias.jpg (69.2 KiB)
foto.png (48.9 KiB)
captura.png (22.3 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered

Hi @Asier-9635,

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Asier-9635 avatar image
0 Votes"
Asier-9635 answered Cathyji-msft commented

thanks a lot

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Asier-9635

If the response helped, do "Accept Answer". By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

And If you have further questions or issues please let us know.

0 Votes 0 ·