ALTER TABLE query for foreign key constraint does not work

Question

I'm a beginner and creating my own simple blockchain app for fun. The blockchain itself is fully functional. Now I'm trying to implement a database to store the data of the blockchain (right now I'm writing it to a .txt file). So I want to create the following database schema in sqlite:

CREATE TABLE `Blockchain` 
(
    `previous_hash` string  NOT NULL ,
    `timestamp` float  NOT NULL ,
    `signature_of_transactions` string  NOT NULL ,
    `index` bigint  NOT NULL ,

    PRIMARY KEY (`previous_hash`)
);

CREATE TABLE `Wallet` 
(
    `public_key` string  NOT NULL ,

    PRIMARY KEY (`public_key`)
);

CREATE TABLE `Transactions` 
(
    `signature` string  NOT NULL ,
    `sender` string  NOT NULL ,
    `recipient` string  NOT NULL ,
    `amount` float  NOT NULL ,

    PRIMARY KEY (`signature`)
);

CREATE TABLE `Peer_nodes` 
(
    `id` string  NOT NULL ,
    `public_key` string  NOT NULL ,

    PRIMARY KEY (`id`)
);

ALTER TABLE `Wallet` 
    ADD CONSTRAINT `fk_Wallet_public_key` 
    FOREIGN KEY(`public_key`) REFERENCES `Peer_nodes` (`public_key`);

ALTER TABLE `Transactions` 
    ADD CONSTRAINT `fk_Transactions_signature` 
    FOREIGN KEY(`signature`) REFERENCES `Blockchain` (`signature_of_transactions`);

ALTER TABLE `Transactions` 
    ADD CONSTRAINT `fk_Transactions_sender` 
    FOREIGN KEY(`sender`) REFERENCES `Wallet` (`public_key`);

ALTER TABLE `Transactions` 
    ADD CONSTRAINT `fk_Transactions_recipient` 
    FOREIGN KEY(`recipient`) REFERENCES `Wallet` (`public_key`);

Creating the tables with the columns etc. works fine with the script above. The ALTER TABLE queries do not work somehow. This is the following error message I receive:

ALTER TABLE Wallet ADD CONSTRAINT fk_Wallet_public_key FOREIGN KEY(public_key) REFERENCES Peer_nodes (public_key)
ERROR:

As you can see, it has no real error message. I haven't found a possible error in the queries themselves after searching a lot on the internet. What am I doing wrong? I try to do this via phpLitedmin, so maybe the problem is there?

score 0 · Answer 1 · answered Jul 08 '18 at 11:15

Your foreign key reference is to the wrong column. It should be to the primary key, although it can be to a unique key.

As explained in the documentation:

Usually, the parent key of a foreign key constraint is the primary key of the parent table. If they are not the primary key, then the parent key columns must be collectively subject to a UNIQUE constraint or have a UNIQUE index. If the parent key columns have a UNIQUE index, then that index must use the collation sequences that are specified in the CREATE TABLE statement for the parent table.

You should fix the table definition and add the foreign key to use the primary key.

Thanks for the help Gordon, it's fixed now! – Robinj23 Jul 08 '18 at 15:40 — Robinj23, Jul 08 '18 at 15:40

score 0 · Accepted Answer · answered Jul 08 '18 at 13:53

0

SQLite's ALTER TABLE does not support adding constraints.

You have to include the constraints into the CREATE TABLE statements.

And as already noted by Gordon, foreign key constraints require the target to be a primary or candidate key.

answered Jul 08 '18 at 13:53

CL.

173,858
17
217
259

Thanks for the clarification of Gordon's answer, it works now! – Robinj23 Jul 08 '18 at 15:39

ALTER TABLE query for foreign key constraint does not work

2 Answers2