1

I am working with Node.js and MySQL and I am pretty new at this. I tried setting up a table with one column that accepts JSON data. I did this because I have a parent table and child table type of format and the parents would be able to hold multiple child_ids.

CREATE TABLE IF NOT EXISTS parents(
  parent_id int PRIMARY KEY auto_increment,
  first_name VARCHAR(255),
  middle_name VARCHAR(255),
  last_name VARCHAR(255),
  child_ids JSON
);

I use this query to insert data into the parents table:

connection.query(`INSERT INTO parents (first_name, middle_name, last_name, child_ids) VALUES ('John', 'Malcom', 'Smith', '{"child_1": 129314812, "child_2": 18436987}');`, function(err, results, fields){
  if (err) console.log(err.message);
});

However, I get this error, which I can't seem to find anything about:

ER_JSON_USED_AS_KEY: JSON column 'parent_ids' supports indexing only via generated columns on a specified JSON path.

If anyone knows about this please help me out!

EDIT: I commented this command out and I don't get the error anymore:

ALTER TABLE childs ADD FOREIGN KEY(parent_ids) REFERENCES parents(parent_id);

But how would I make it so that i can add the foreign key?

Ethan Tom
  • 35
  • 2
  • 7
  • That error message would happen if you tried to create an index on the `child_ids` column. I don't think it can come from just doing an `INSERT`. – Barmar Jul 19 '18 at 03:47
  • I would gues you set your primary key (parent_ids) as JSON and since primary keys have an implicit index it fails. In any case your example code and the error message doesn't match up. – felixbuenemann Jul 19 '18 at 03:58
  • `... JSON columns cannot be indexed directly.`, check [13.1.18.9 Secondary Indexes and Generated Columns](https://dev.mysql.com/doc/refman/8.0/en/create-table-secondary-indexes.html). See comment in [Indexing JSON documents via Virtual Columns](https://mysqlserverteam.com/indexing-json-documents-via-virtual-columns/#comment-16450). – wchiquito Jul 19 '18 at 05:47

1 Answers1

0

Thanks for the help everyone! The error was fixed by removing the foreign key binding between the tables. I had a foreign key in each table referencing each other which did not work correctly.

Ethan Tom
  • 35
  • 2
  • 7