Can't add foreign key to table MariaDB/MySQL-CodePudding

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.

CodePudding user response：

syntax is wrong, use this

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

CodePudding user response：

Thanks for all answers, I added UNSIGNED to BIGINT, but it wasn't main source of my problem. The problem was that I imported data from file where primary key values started at 0. Here I found that in MariaDB/MySQL if 0 is given for primary key, it will automatically assign first value. It changed 0, 1, 2 to 1, 1, 2 and error occured. There are two options:

changing settings of dbgen (program generating data for TPC-H benchmark). Possible options are: INFORMIX, DB2, TDAT (Teradata), SQLSERVER, SYBASE, ORACLE, VECTORWISE. Options stand for database syntax, by there is no MYSQL option. dbgen creates '|' separated file (.tbl) with records for this tables. I used ORACLE and it generated file with primary keys starting at 0. You can comment if you know which option would be most similar to MySQL syntax and contain keys starting at 0.
Or is there any automatic software which will add 1 to primary keys in this file. Although NATION and REGION tables are not so big (20, 5), some other tables (which I removed from this question for clarity) will have over 100 000 records.