Creating a table that joins items from the same table in SQL

Question

I have an SQL database with a table with some static values for use in things like dropdowns. The table (value_options) contains different collections of data differentiated by a DTYPE column. Some of the items in this table need to have a many-to-many relationship with other items in the same table.

To accomplish this I created a join table like this:

--add some po types
insert into value_options (DTYPE, value, detail)
values(
'PO_TYPE','INITIAL', 'INITIAL'
),
(
'PO_TYPE','INITIAL_WITH_TOOLING', 'INITIAL WITH TOOLING'
),
(
'PO_TYPE','SALES_SET', 'SALES SET'
);

--add some po groups
insert into value_options (DTYPE, value, detail)
values(
'PO_GROUP','01', 'National Group'
),
(
'PO_GROUP','02', 'Retail Group'
);

--create the join table
create table po_types_to_groups(
po_type_id int not null,
group_id int not null,
PRIMARY KEY(po_type_id, group_id)
);

Now that I have the join table, I need to figure out how to add the constraints to establish the actual relationships. I tried setting up the relationship between the po_type_id of the join table and the id column of the value_options table like this:

ALTER TABLE value_options
ADD CONSTRAINT fk_valueoptions_group
FOREIGN KEY(id)
REFERENCES po_types_to_groups(po_type_id);

and I get the error:

There are no primary or candidate keys in the referenced table 'po_types_to_groups' that match the referencing column list in the foreign key 'fk_valueoptions_group'.

I'm not sure what I'm doing wrong here. How to I write an SQL statement to add this constraint?

Answer 1

First you must return a value from your async function called getprefix. Secondly you must console.log the result of the promise returned by getprefix function instead of the promise itself :

const getprefix = async (id) => {
  const guildConfigs = await GuildSchema.findOne({GuildID: id});
  
  if (!guildConfigs || !guildConfigs.Prefix) {
    return DEFAULT;
  }

  return guildConfigs.Prefix;
};

getprefix(message.guild.id).then(prefix => console.log(prefix));