<p>Because you want to do this in PostgreSQL<sup>[which version?]</sup>, this is what should be working for you:</p>
<ul>
<li>Create the table.</li>
<li>Insert genesis as a self referencing block.</li>
<li>alter table block add constraint fk_prev_block_id foreign key (prev_block_id) references block(block_id)</li>
</ul>
<p>Other engine vendors may require more simple or more complex code.</p>

CC BY-SA 4.0
2022-11-23T15:21:34.107Z
2022-11-23T15:21:34.107Z
Mercedes
<p>I found this <a href="https://github.com/blockchain-etl/bitcoin-etl#blocksjson" rel="nofollow noreferrer">schema</a>.
I wonder how organize the relationship with prev_block_id(prev_hash)?</p>
<p>I think it would be like a below schema.(Here  limited to id(hash))</p>
<pre><code>CREATE TABLE block (
    id VARCHAR(8) PRIMARY KEY,
    prev_id VARCHAR(8) NOT NULL,
    FOREIGN KEY (prev_id) REFERENCES block (id),
);
</code></pre>
<p>However, this schema would not be able to be INSERT due to self-reference constraints.</p>

CC BY-SA 4.0
2022-11-22T12:47:03.530Z
Which database engine vendors do you intend this to be able to run?
Mercedes
CC BY-SA 4.0
2022-11-22T12:48:45.540Z
Sorry for the lack of explanation, I meant Postgres.
bunk bank
CC BY-SA 4.0
2022-11-22T12:51:57.643Z
It looks like in postgres this is done via ALTER?
Mercedes
CC BY-SA 4.0
2022-11-22T12:53:37.510Z
alter table block add column prev_id integer not null references block(id) - did you try this?
Mercedes
CC BY-SA 4.0
2022-11-22T13:00:59.647Z
Haven't run it, but good idea.

In other words, don't put the references in the schema at first, and then run the ALTER statement at the time the genesis block is born, and create the prev_hash - hash constraint, right?
bunk bank
CC BY-SA 4.0
2022-11-22T14:03:07.343Z
no, run the ALTER statement right after CREATE, and INSERT genesis as a self referencing block. Boom!!!
Mercedes
CC BY-SA 4.0
2022-11-22T17:06:52.567Z
I ran your suggestion as a test, but ```Error saving new block: DatabaseError(ForeignKeyViolation, "insert or update on table \"blocks\" violates foreign key constraint \"blocks_prev_block_id_fkey```.

As you suggested, CREATE(create id only) -> ALTER(ADD prev_block_id) is the same thing as the CREATE statement I posted in the beginning, what is the purpose?
bunk bank
CC BY-SA 4.0
2022-11-22T20:26:22.040Z
um you gotta try inserting genesis and then set the constraint afterwards, it'll surely work
Mercedes
CC BY-SA 4.0
2022-11-22T20:28:40.910Z
alter table block add constraint fk_prev_block_id foreign key (prev_block_id) references block(block_id)
Mercedes
CC BY-SA 4.0
2022-11-23T11:26:17.123Z
Your method finally solved the problem. Thank you！
bunk bank
CC BY-SA 4.0
2022-11-23T15:24:28.563Z
I’m voting to close this question because it's a database design/administration question, unrelated to Bitcoin.
Pieter Wuille
<blockchain>
How is the Prev_block_id managed?
bunk bank
# How is the Prev_block_id managed?

(Asked by: username_0 on 2022-11-22)

I found this [schema](https://github.com/blockchain-etl/bitcoin-etl#blocksjson).
I wonder how organize the relationship with prev\_block\_id(prev\_hash)?

I think it would be like a below schema.(Here limited to id(hash))

```
CREATE TABLE block (
    id VARCHAR(8) PRIMARY KEY,
    prev_id VARCHAR(8) NOT NULL,
    FOREIGN KEY (prev_id) REFERENCES block (id),
);

```

However, this schema would not be able to be INSERT due to self-reference constraints.

**Tags:** blockchain

> **Comments:**
> **username_1**: Which database engine vendors do you intend this to be able to run?  
> **username_0**: Sorry for the lack of explanation, I meant Postgres.  
> **username_1**: It looks like in postgres this is done via ALTER?  
> **username_1**: alter table block add column prev_id integer not null references block(id) - did you try this?  
> **username_0**: Haven't run it, but good idea.  In other words, don't put the references in the schema at first, and then run the ALTER statement at the time the genesis block is born, and create the prev_hash - hash constraint, right?  
> **username_1**: no, run the ALTER statement right after CREATE, and INSERT genesis as a self referencing block. Boom!!!  
> **username_0**: I ran your suggestion as a test, but ```Error saving new block: DatabaseError(ForeignKeyViolation, "insert or update on table \"blocks\" violates foreign key constraint \"blocks_prev_block_id_fkey```.  As you suggested, CREATE(create id only) -> ALTER(ADD prev_block_id) is the same thing as the CREATE statement I posted in the beginning, what is the purpose?  
> **username_1**: um you gotta try inserting genesis and then set the constraint afterwards, it'll surely work  
> **username_1**: alter table block add constraint fk_prev_block_id foreign key (prev_block_id) references block(block_id)  
> **username_0**: Your method finally solved the problem. Thank you！  
> **username_2**: I’m voting to close this question because it's a database design/administration question, unrelated to Bitcoin.  

---

## 1 answers

### Answer by username_1 on 2022-11-23. Score: 1

Because you want to do this in PostgreSQL[which version?], this is what should be working for you:

* Create the table.
* Insert genesis as a self referencing block.
* alter table block add constraint fk\_prev\_block\_id foreign key (prev\_block\_id) references block(block\_id)

Other engine vendors may require more simple or more complex code.


---

