0

A section of my database includes a table saving information about blocks:

CREATE TABLE blocks (
    id_block INTEGER NOT NULL,
    id_type INTEGER NOT NULL,
    id_parent_block INTEGER NOT NULL,
    PRIMARY KEY (id_block),
    FOREIGN KEY (id_type) REFERENCES block_types (id_block_type),
    FOREIGN KEY (id_parent_block) REFERENCES blocks (id_block)
) WITHOUT ROWID;

1st. I'm not sure about the id_parent_block being a foreign key, since it's a key present in the current table.

It is supposed to reflect some kind of hierarchy where blocks have blocks inside. Blocks that are on top have their id_parent_block = -1.

Blocks also have words in them:

CREATE TABLE words(
    id_word INTEGER NOT NULL,
    str_word TEXT NOT NULL,
    PRIMARY KEY (id_word),
    FOREIGN KEY (id_block) REFERENCES blocks (id_block),
) WITHOUT ROWID;

One block may have words (or not) and blocks (or not) in it.

2nd. How do I get all the id_words associated to every block in a hierarchy of blocks where the block on top does not necessarily have id_parent_block = -1?

Atua Mae
  • 1
  • 1
    Look at CTE to implement recursive queries. – tonypdmtr Jul 26 '19 at 18:32
  • https://stackoverflow.com/questions/7456957/basic-recursive-query-on-sqlite3 – Error_2646 Jul 26 '19 at 18:32
  • 2
    It's fine to have a foreign key refer to the same table. But it's more usual to have null values for top level rows. – Shawn Jul 26 '19 at 19:14
  • When your PK is a single integer column you should be using a normal table, not a `WITHOUT ROWID` one, btw. See section 4 of https://www.sqlite.org/withoutrowid.html – Shawn Jul 26 '19 at 19:17

0 Answers0