One-many-one relationships (foreign keys from 2 parents)


i have yet to even install cockroachDB, but it does look extraordinarily promising. before moving forward, we have a lot of mariadb(MYSQL) parent-child foreign-key relationships that could be summed up like so:

parent–>child<–parent or 1:M:1 (mariadb example below)

indeed, some relationships even have three parents.

my question: i read in the FAQ about current difficulty supporting “joins”. is this type of relationship below something considered a “join” in cockroachDB terminology?

would i have difficulty with the example below?

here is my example mariaDB select:

SELECT fatherTable.fatherName, motherTable.motherName, childrenTable.childrenName
FROM fatherTable, motherTable, childrenTable
WHERE fatherTable.father_id = childrenTable.father_id
AND motherTable.mother_id = childrenTable.mother_id ;

sql to simulate this below:

CREATE TABLE fatherTable (
fatherName varchar(20) NOT NULL,
father_id int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (father_id);

CREATE TABLE motherTable (
motherName varchar(20) NOT NULL,
mother_id int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (mother_id);

CREATE TABLE childrenTable (
childrenName varchar(20) NOT NULL,
father_id int(11) NOT NULL,
mother_id int(11) NOT NULL,
children_id int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (children_id),
KEY father_id (father_id),
KEY mother_id (mother_id),
CONSTRAINT childrenTable_ibfk_1 FOREIGN KEY (father_id) REFERENCES fatherTable (father_id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT childrenTable_ibfk_2 FOREIGN KEY (mother_id) REFERENCES motherTable (mother_id) ON DELETE CASCADE ON UPDATE CASCADE;

INSERT INTO childrenTable (childrenName, father_id, mother_id) VALUES (‘kids name’, 1,1) ;

re: one-many-one relationships (foreign keys from 2 parents)

it appears the same question might be here.

i found an example here.

In your message there are two questions embedded:

  1. how to guarantee the relationship between the tables, and whether foreign keys are appropriate for this
  2. how to look up values across these relationships

Regarding point #1 yes you can use foreign keys for this purpose, however I am not 100% sure we support the CASCADE parameters yet.

Regarding point #2 yes this is definitely a join and is likely to become inefficient as soon as either of the “mother” and “father” tables become large. We plan to optimize this but in the meantime you may get better performance by issuing the 3 queries (to the node and parent tables) separately.

We don’t currently support CASCADE behaviors for update or delete – you’ll need to issue those deletes yourself for now.
We do support a child table referencing more than one parent table, though a given field can only participate in at-most-one FK relationship (if I recall correctly, this is it can be ambiguous how to handle conflicting ON UPDATE behaviors if/when we do implement them) – but that should be fine in your example.

Unrelated, note that we don’t support AUTO_INCREMENT but you can use the SERIAL type to get an increasing, unique integer.