CODEWITHSUNDEEP
mysqlsql
Ph33ly
Ph33ly

Reputation: 693

MySQL check if parent and child field matches before inserting foreign key

Is it possible to enforce a check in a child table where a field must have the same value as the parent to allow the foreign key insertion?

For example, say I have the table below

Parent Table
-------------
id
field1
field7

Child Table
-------------
id
field1
field2
parent_id (foreign key to parent table column "id")
field3

I only want to allow a parent_id to be inserted in child table if "field1" in both Child and Parent tables have the same value.

I was thinking of just handling this constraint in the application logic, but I was curious if MySQL had a way to enforce this.

EDIT: In this scenario, the Child table has a one-to-many foreign key relationship with the Parent table. The Child table column parent_id references the Parent table column id. So in addition to this, I'd only want to insert child table values where the given Child table "field1" value match the given "parent_id" referenced value "field1"

Upvotes: 2

Views: 642

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269703

This is called a foreign key constraint. The best approach is to define the id in the parent table as the primary key:

create table parent (
    id <whatever type> primary key,
    . . .
);

Most databases require that the referred to key be a primary key or unique. Although MySQL extends this to any key (i.e. any column(s) with an index), stick with primary/unique keys.

In the child table, you can then declare the foreign key reference:

alter table child add constraint fk_child_parent
    foreign key (parent_id) references parent (id);

EDIT:

I don't think it is a good idea to repeat values across tables. It is better to look them up as necessary. You can, however, do what you want with a foreign key:

create table parent (
    id <whatever type> primary key,
    field1 . . .,
    . . .
    unique (field1, id)
);

alter table child add constraint fk_child_parent
    foreign key (field1, parent_id) references parent (field1, id);

Upvotes: 1

Related Questions