-1

I am analyzing some csv files here and based on them I am trying to create the DB structure, but apparently, one column from table2 has data from two different columns from table1. Can someone help me how can I recreate the same logic in SQL ?

I tried:

CREATE TABLE table1
(
     field1 varchar(4),
     field2 varchat(5),

     PRIMARY KEY(field1, field2)
);

CREATE TABLE table2
(
      field3 varchar(5) REFERENCES table1(field1, field2)
)

That didn't work. Any ideas?

Thank you

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
victorb
  • 7
  • 2
  • 2
    That's not possible and it's a bad design to store two different things in a single column. You should split up the column in table2 (even if you were not using it as a foreign key) –  Mar 01 '19 at 17:49
  • This is not clear. Any PKs/UNIQUEs? Does "one column from table2 has data from two different columns from table1" mean table2 values are in both, or in exactly one, or in one or both? (A FK constraint says a column list's subrows appears elsewhere as a PK or UNIQUE. "In both" that are PK/UNIQUE is 2 FKs. Otherwise you can't enforce all of your constraint declaratively in your DBMS. You need triggers or a design change. Typically this is an anti-pattern for subtyping. [How can you represent inheritance in a database?](https://stackoverflow.com/q/3579079/340409)) – philipxy Mar 01 '19 at 23:20
  • @philipxy yes, the one column from table 2 has data from two different columns from table 1. so I wasn't sure if smth like that was possible. Do you think a good solution would be to split the table1 in another table, then I will have table1a table1b that provide data to table2? – victorb Mar 02 '19 at 15:48
  • You just repeated the same ambiguous statement "data from two different columns". And didn't give PKs/UNIQUEs. Does a given t2 value have to be in both or have to be in exactly one or have to be in one or both? (A FK to X implies must be in X; 2 FKs implies must be in both.) PS Please clarify via edits, not comments. PS Once clear, this will be a faq. Found by googling the clear statement of your question. Although you could probably find one among hits to your current phrasing. Also google re SQL/database subtyping/inheritance/polymorphism & re (anti-pattern) 2/many FKs to 2/many tables. – philipxy Mar 02 '19 at 19:18
  • My current generic comment re "better"/"best" etc: There's no such thing as "better"/"best" in engineering unless you define it. Also unfortunately all reasonable practical definitions require a ridiculous amount of experience with a ridiculous number of factors that interact with chaotic sensitivity to details. Make straightforward designs. When you demonstrate via measurement that a design and all alternatives you can think of have problems (whatever that means at the time), then ask a very specific question. Which should also define "better"/"best". https://meta.stackexchange.com/q/204461 – philipxy Mar 02 '19 at 19:19

1 Answers1

0

If I recall correctly, there can only be one primary key on a table; what if you did two separate inner joins on table 2 with table 1 to separate the two concepts, then join the two new tables to appropriately separate out the concepts in table 2?

Something like this:

Select Field3 
into #tmpkeyA 
from table2 
inner join table 1 on field3 = field 1

Select Field3 
into #tmpkeyB 
from table2 
inner join table 1 on field3 = field 2

Then build out the logic based on the two temp tables to appropriately get the data into its proper order.

Or am I misreading the nature of the problem? What is the nature of the fields?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jaso Lackey
  • 1
  • 1
    The first table in the question only has single primary key. –  Mar 01 '19 at 18:12
  • hi, are you saying that I need to split the table1 into two different tables then connect it to table2? – victorb Mar 02 '19 at 15:50