Can't add foreign key to table MariaDB/MySQL

Question

I'm creating tables in MariaDB 10.6 database for TPC-H benchmark. CREATE TABLE works ok, but adding FOREIGN KEY fails. I tried following mariadbtutorial and documentation but this doesn't work too.

I suspect:

• syntax of FOREIGN KEY is wrong
• Wrong datatype in column that reference foreign key.
• column refering to foreign key should be index
• there's something wrong with data generated by dbgen from TPC-H benchmark.

The errors that occured:

• warning 150: alter table bazatest2.nation with foreign key (N_REGIONKEY) constraint failed. field type or character set for column 'N_REGIONKEY' does not match referenced column 'R_REGIONKEY'. Tried changing BIGINT NOT NULL to BIGINT UNSIGNED NOT NULL but different error occurs:

• error 1452 when i tried adding UNSIGNED to BIGINT in column that should refer to foreign key.

Part of file containing creates:

DROP TABLE IF EXISTS NATION CASCADE;
CREATE TABLE NATION (
    N_NATIONKEY     SERIAL PRIMARY KEY,
    N_NAME          CHAR(25),
    N_REGIONKEY     BIGINT UNSIGNED NOT NULL,  -- references R_REGIONKEY
    N_COMMENT       VARCHAR(152)
);
DROP TABLE IF EXISTS REGION CASCADE;
CREATE TABLE REGION (
    R_REGIONKEY SERIAL PRIMARY KEY,
    R_NAME      CHAR(25),
    R_COMMENT   VARCHAR(152)
);

Part of file with foreign key constraints:

ALTER TABLE NATION ADD CONSTRAINT FOREIGN KEY (N_REGIONKEY) REFERENCES REGION(R_REGIONKEY);

I tried solving this by changing syntax of alter table add constraint foreign key and searching for solutions all yesterday and haven't found solution. Most likely is that column referencing to foreign key should be index, or multiple errors, but I don't know what should i change in my code.

Answer 1

syntax is wrong, use this

ALTER TABLE NATION ADD CONSTRAINT NATION_FK FOREIGN KEY (N_REGIONKEY) REFERENCES REGION(R_REGIONKEY);