Jad Jad - 4 months ago 18
SQL Question

Junction table/many to many relationship

I have the following tables

Table1: machinelist

machinename (PK,ncarchar(10),not null)
machine_id (computed,nvarchar(2),null)


Table2: JDEPARTIMGLU

ndx_jde_part_img (pk,int,not null)
jde_part_num (varchar(20)null)


- ....(more columns)

I want to create many to many relationship between
machinename
and
jde_part_num
. I think I need to use a junction table to do this. I tried using
machinename
from table1 and
jde_part_num
from table2, but im having issues setting up the FK.

I'm not sure if I'm on the right track, can anyone help direct me in the right direction on how to do this?

I will have many parts that are used on many machines and many machines that use the same part

Answer

You have to use both PKs on the junction table

CREATE TABLE Junction
(
  machinename       nvarchar(10) not null,
  ndx_jde_part_img  int not null,
  PRIMARY KEY (machinename,ndx_jde_part_img),
  CONSTRAINT fk_machines FOREIGN KEY (machinename) REFERENCES machinelist(machinename),
  CONSTRAINT fk_parts FOREIGN KEY (ndx_jde_part_img) REFERENCES JDEPARTIMGLU(ndx_jde_part_img)
);

You can even add some new columns to this table, like number of parts needed on that machine.

Comments