Super weird error with PS' new FKC feature

[205g0]

Posted this initially here, so just copy and pasting from there:

Not sure if this is the right place for the new beta feature:

Once I try to add an FCK to the child table I get:

sqlMessage: `target: mydb: vttablet: rpc error: code = Unknown desc = Failed to add the foreign key constraint. Missing index for constraint 'random_name' in the referenced table 'parents' (errno 1822) (sqlstate HY000) (CallerID: 54qabz7uuc5afdasdfasdx): Sql: "alter table children add constraint random_name foreign key (parent_id) references parents.(id) on delete no action on update no action", BindVars: {REDACTED}

This is a common error with mysql on SO, so I made sure that really everything is considered and the command should have be executed. Also, the operation worked only once, then never again (after deleting and recreating that table with a new random FKC name). I also ensured that the FCK name is always a new one. So the command must have been right.

Weirder, once I go the error, in PlanetScales schema overview, the new children table shows up (without the FCK) BUT all other tables (around a dozen) loose their PRIMARY KEY. When I remove table children again, all other tables get the PRIMARY KEY back

[shlomi-noach]

@205g0 a lot to unpack here, thanks for reporting. Let's please begin with your existing table definitions.

1. Failing to add foreign key constraint.

Can you please clarify what your parent/child table definitions are right now, and what you're trying to change them into?

The error you're seeing is basically that this type of schema is invalid:

create table parent (id int primary key, i int);
create table child (id int primary key, parent_i int, foreign key (parent_i) references parent (i));

This is invalid because parent table must have an index on column i.

So if your current schema is:

create table parent (id int primary key, i int);
create table child (id int primary key, parent_i int);

and you're trying to

alter table child add foreign key (parent_i) references parent (i));

then the error makes sense.

2. Trying to understand what was done

I'm afraid I find it a bit difficult to understand a few things here:

so I made sure that really everything is considered and the command should have be executed.

Can you please elaborate what was considered and exactly what command you were trying to execute?

Also, the operation worked only once, then never again

Can you please clarify what was the "operation"? I'm having a difficult time following.

3. PRIMARY KEY

Weirder, once I go the error, in PlanetScales schema overview, the new children table shows up (without the FCK) BUT all other tables (around a dozen) loose their PRIMARY KEY. When I remove table children again, all other tables get the PRIMARY KEY back

I can't see a situation where this is actually happening. I'm suspecting there may be a layer you're using (ORM?) that reads the information wrong. But you're saying it's the PlanetScale schema overview -- so I'm super curious and have to check.

4. Try directly

a. Could you please issue a SHOW CREATE TABLE <your-parent-table>\G and SHOW CREATE TABLE <your-child-table>?

b. Can you issue a SHOW CREATE TABLE <whatever-table> for one of those tables that seemingly lost their PRIMARY KEY?

Lastly, would you like to share your user name, database name & branch name, and we can investigate your schema?