How to implement an 'outer join' relationship in SQL Server?

This is a question that's probably going to incur the wrath of some DBA types but I'm gonna ask it anyway!!

I have a SQL Server DB and we are adding a new table which is basically going to act as a lookup table. We'll call it tblLookup. There is another table called tblMain.

Every row in tblMain will either have none or one related row(s) in tblLookup. So tblLookup contains the "primary key" (although it's not actually the primary key of the table) and tblMain contains the "foreign key".

SQL Server won't let me add this relationship even if I set enforce foreign key constraint to no.

What am I best off doing? Obviou开发者_如何学Pythonsly I can define this relationship when SELECTing by doing a LEFT OUTER JOIN - but I would prefer it if there was something in the schema itself (I'm not sure what benefit I'm hoping to get? Maybe someone can tell me!)

---

I think you're asking, how can I implement a foreign key constraint, where the referenced values are not unique? In other words all non-NULL values in TableA.ColumnA must already be present in TableB.ColumnB, but the values in TableB.ColumnB are not unique.

If so, a trigger could easily enforce this, but you should consider your design as well before making that decision. Why are the values you want to reference not unique? Perhaps there's a genuine reason for it, but it's also possible that you should extract the distinct values into a third table and then have foreign keys from both your other tables. Without more information about your database and what you're modelling it's impossible to say what the best approach here is.

Finally, and for what it's worth, "lookup tables" are often understood to be tables that have a well-defined primary key and are referenced by it and only by it in foreign keys e.g. countries, currencies, products etc. To avoid confusion when asking your question, you should probably choose different table names or - even better - provide a self-contained SQL example that illustrates your problem unambiguously.

---

It sounds like you're trying to create the foreign key relationship in the wrong direction. You'd want to create a relationship from tblLookup to tblMain. Something like:

ALTER TABLE tblLookup
    ADD CONSTRAINT FK_tblLookup_tblMain FOREIGN KEY (tblMain_id)
    REFERENCES tblMain (id);