3

I am trying to build a table which will hold the 'relationship' of a parent table and a child table. However each column in both tables are no keys or unique and there are duplicate values in each.

Example

Table A - Parent (Fact)

**CartNumber**

Table B - Child

**CartNumber** not unique
CartValue

CartNumber from table A links to CartNumber in B.

I have tried to implement a foreign key with NOCHECK but of course that will not work since the child column is not a primary key or unique. Bear in mind, I am ONLY trying to define that there is a link between the two columns/tables. Is there any way to define a 'loose' relationship between the two columns? Preferably a method where I can reference the sys views or information schema to extract this information

xblade
  • 55
  • 5
  • Not the most beautiful solution, but it might work to use a third table holding all unique used cardnumbers, then making both having a relationship to that table. – Samuel Kirschner Jan 05 '17 at 09:18
  • @SamuelKirschner the third table would be huge since its not just CartNumbers but a whole range of other columns, each used to link to another table. I guess the end goal is to almost have a table holding the path definition from one table to another – xblade Jan 05 '17 at 09:22
  • 1
    Do I get this correctly? There is a table with let's say persons. There is no unique key, so you look at their first name. There is a Bob, a Tom and a Tim, and one more Bob and one more Tim. Then there is some related data. The data knows, that it is somehow related to Bob, but does not need to know **which** Bob? This smells... Might help to explain some more background: What are you trying to achieve? – Shnugo Jan 05 '17 at 09:25
  • If what you are referencing is not unique, you won't be able to retrieve data specifically.(i.e. You might not get what you want with those references). – katu Jan 05 '17 at 09:26
  • 1
    @xblade I just read your question once again and got the feeling, that you have a wrong concept of *relationship*. One Question: Is the **CartNumber** in `Table A` unique? – Shnugo Jan 05 '17 at 09:32
  • @Shnugo I have no knowledge about the data within the columns, but all I do know is that both the parent and child column is not unique. All I am looking to do is say "To get result Y you need to join column X from Table A onto Column Z in Table B" and that join is the path from the parent to the child – xblade Jan 05 '17 at 09:38
  • @Shnugo not a unique column in either table – xblade Jan 05 '17 at 09:39
  • @xblade why do you want to define such a relationship, for what purpose? is it merely to serve as a "comment" for other devs? – Stephen Jan 05 '17 at 10:16
  • @Stephen its not a relationship by the usual definition. I do not want a physical link between the tables but something that can just be referred to that will tell a user or query that Column A from Table A is used to join onto Column B from Table B – xblade Jan 05 '17 at 11:17

1 Answers1

0

To be honest: This design smells and - if possible - you should think about changing this...

There is no chance to define a FOREIGN KEY CONSTRAINT on non-unique columns the way you describe it.

But: To define a JOIN there is no need for a FK!

My suggestion:

Create a VIEW like this:

CREATE VIEW dbo.MyView
AS
SELECT a.Col1,a.Col2,...
      ,b.Col1,b.Col2,...
FROM TableA AS a
[INNER/LEFT/RIGHT/FULL OUTER] JOIN TableB AS b ON a.RelField=b.RelField;

With such a VIEW you will get the data joined on this non-unique information.

UPDATE

Taken form your comment:

the end goal is just to provide an external web service with information that says Column A from Table A is used to join onto Column B from Table B

You can create a meta-table like this:

CREATE TABLE dbo.ColumnReference
(
 ColumnReferenceID INT IDENTITY
,TABLE_NAME_A VARCHAR(255) NOT NULL
,COLUMN_NAME_A VARCHAR(255) NOT NULL
,TABLE_NAME_B VARCHAR(255) NOT NULL
,COLUMN_NAME_B VARCHAR(255) NOT NULL
);
--inlcude SCHEMA if needed...

In this case you can maintain these relations in your own structure..., you might even add details, rules, what ever...

The web service will call this directly. You might use a VIEW to combine existing relations (defined as FK CONSTRAINT) with your own meta table (simply with UNION ALL).

Community
  • 1
  • 1
Shnugo
  • 66,100
  • 9
  • 53
  • 114
  • Thanks @Schnugo, apologies if i worded it incorrectly but the end goal is not to extract data or worry about the data at all. Instead, the end goal is just to provide an external web service with information that says Column A from Table A is used to join onto Column B from Table B – xblade Jan 05 '17 at 11:15
  • Thanks Schnugo, something like this was actually my last resort as I assume the filling in of this table would be manual? There will be hundreds, nearing to the thousands, of columns so of course a lot of room for human error and maintenance. However if there is no other way then I guess this is the only approach – xblade Jan 05 '17 at 11:45
  • @xblade, what you describe is more **business rule** than **relationship**, at least in terms of database people... I doubt, that a user would be able to create a query out of this later... Well, define all **real relations** as `FK CONSTRAINT` and *relations by rule* as a meta-table. I would be really astonished, if this concept turns out as a wise and working design... – Shnugo Jan 05 '17 at 12:06