This is a read-only copy of the MariaDB Knowledgebase generated on 2024-11-27. For the latest, interactive version please visit https://mariadb.com/kb/.

MariaDB 10.5.8 Foreign Key Constraint Error

Dear Sir,

I have a problem about MariaDB 10.5.8 for setting foreign key constraint using ALTER TABLE command. In my case, if the table names and field names are in Japanese, the ALTER TABLE ... ADD FOREIGN KEY ... cannot work. But, if the table names and field names are in English, there is no problem.

However, when using MariaDB 10.4.12, both Japanese and English for table name and fields can work.

I have surfed internet and no information about why Maria 10.5.8 cannot work.

My environment is Windows 10 Pro. I have confirmed the database engine is InnoDB, the charset is utf-8. The program is as follow. The final line about ALTER TABLE cannot work.

Hope you can answer my question.

Thank you!

#顧客表と販売表を作成(主キーを定義)
create database if not exists DB21;
use DB21;

CREATE TABLE 顧客表 (
    顧客番号    char(4) PRIMARY KEY,
    顧客氏名    char(16),
    住所           char(32),
    電話番号    char(16)
);
CREATE TABLE 販売表 (
    販売日      char(6),
    顧客番号    char(4),
    商品1       char(16),
    商品2       char(16),
    PRIMARY KEY(販売日)
);

INSERT INTO 顧客表 (顧客番号, 顧客氏名, 住所, 電話番号) VALUES(
'0001','相田孝之', '東京都新宿区','03-xxxx-xxxx');

INSERT INTO 顧客表 (顧客番号, 顧客氏名, 住所, 電話番号) VALUES(
'0002','伊藤美香', '神奈川県横浜市','045-xxxx-xxxx');

INSERT INTO 販売表 (販売日, 顧客番号, 商品1, 商品2)VALUES
('210115','0002','吟醸灘一本', '特選おつまみ');

INSERT INTO 販売表 (販売日, 顧客番号, 商品1, 商品2) VALUES
('210116','0001','神戸和牛セット', default);

SELECT * FROM 顧客表;
SELECT * FROM 販売表;

#追加:外部キーを定義: This cannot work.
ALTER TABLE 販売表 ADD FOREIGN KEY (顧客番号) REFERENCES 顧客表(顧客番号);
 
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.