Foreign Key constraint problem: SQL error 1750 and 1776

Nov 10, 2009 at 3:44 PM

I bet it’s something really simple I’m missing here.

I’ve built the diagram normally and when I save it, although it generates the database as expected, the toolbox can’t create the constraints and the compiler keeps giving me SQL errors 1776 (TSD4001: There are no primary or candidate keys in the referenced table ‘dbo.CategoriaSegmento’ that match the referencing column list in the foreign key ‘FK_CategoriaSegmento_Segmento’.) and 1750 (TSD4001: Could not create constraint.)

I’ve searched for it but my guess is that I’m such a newbie that I can’t realize a simple mistake!

I've had errors with foreign key constraints before and the main reason would be that the tables had no data so no corresponding keys could be found between tables. But in this case my tables are at the point of being created so they are supposed to be empty, right? 

Appreciate any help. Thanks.

Coordinator
Nov 11, 2009 at 12:00 PM

Marcos,

Can you post extracts from your DBML file and the SQL scripts for the two tables and the foreign key?

Thanks,
Oleg

Nov 11, 2009 at 12:40 PM

Hi mate, 

These errors are generated for a series of relationships. In the example I'm showing here, there's a simple relationship between two tables: Endereco (address) and Usuario (user). A user can have multiple addresses associated to him, hence the association below. Maybe I'm missing a trivial sql concept here. Hopefully. =)

This is the DBML specific area related to the problem

 

These are the properties for the relationship

This is the SQL Script generated by T4

This is the error generated in VS2008 (Sql)

 

Hope this helps figuring it out.

Thanks for the effort, Oleg. 

Coordinator
Nov 11, 2009 at 10:52 PM

I vaguely remember seeing this in the past when someone initially created the association going the wrong way. Try deleting the incorrect foreign key and regenereting it.

P.S. Love the pictures, but source code would be more helpful (dbml, tt, sql).

Nov 12, 2009 at 11:57 AM

I've given that a shot already. Deleted the problematic relationship and re-associated the tables. I even tried to associate them the other way around to see if there were any changes... No success so far.

Sorry for the images. I couldn't figure out a simple way of showing my code and not confusing it all. Should I zip all files and post them here? Would it be of any help to you?

Cheers,

Coordinator
Nov 12, 2009 at 12:30 PM

Yes, if you could create a new issue and attach your zip file to it, that would be best.

Thanks,
Oleg

Nov 12, 2009 at 12:31 PM

Guess what!? I've deleted the relationships that were giving me error BUT I've still got the errors for them!

Soooooooo... I've done what I should have done since the begginning: deleted all the generated .sql code from the Database project and wooohooo: no errors. =)

And I found out I was linking some tables the other way around. So, my bad.

But I would have never thought that previously generated files (and later unused) would still live inside the project. I'll keep that in mind.

No errors now. Thanks a lot, Oleg.