【 使用环境 】测试环境
【 OB or 其他组件 】OB
【 使用版本 】4.2.0.0 版本
【问题描述】使用mysql客户端访问ob数据库，更新某个库的一个外键约束，
alter table portbindingports add CONSTRAINT portbindingports_OBFK_1697945240192842 FOREIGN KEY (port_id) REFERENCES neutron.ports(id) ON DELETE SET DEFAULT;
报错：ERROR 1215 (HY000): Cannot add foreign key constraint
我的应用以前用的mysql数据库，在创建外键约束时，使用的是 ON DELETE SET NULL语法，但是oceanbase数据库不支持“SET NULL”，但是oceanbase文档中提到可以支持“SET DEFAULT”，但是并未执行成功。
【复现路径】问题出现前后相关操作：
ports表的格式如下，
| ports | CREATE TABLE ports (
tenant_id varchar(255) DEFAULT NULL,
id varchar(36) NOT NULL,
name varchar(255) DEFAULT NULL,
network_id varchar(36) NOT NULL,
mac_address varchar(32) NOT NULL,
admin_state_up tinyint(1) NOT NULL,
status varchar(16) NOT NULL,
device_id varchar(255) NOT NULL,
device_owner varchar(255) NOT NULL,
PRIMARY KEY (id),
CONSTRAINT ports_OBFK_1697945227263117 FOREIGN KEY (network_id) REFERENCES neutron.networks(id) ON UPDATE RESTRICT ON DELETE RESTRICT ,
UNIQUE KEY uniq_ports0network_id0mac_address (network_id, mac_address) BLOCK_SIZE 16384 LOCAL,
KEY ix_ports_network_id_device_owner (network_id, device_owner) BLOCK_SIZE 16384 LOCAL,
KEY ix_ports_tenant_id (tenant_id) BLOCK_SIZE 16384 LOCAL,
KEY ix_ports_network_id_mac_address (network_id, mac_address) BLOCK_SIZE 16384 LOCAL,
CONSTRAINT ports_OBCHECK_1697945227263152 CHECK ((admin_state_up in (0,1)))
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = ‘zstd_1.3.8’ REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
portbindingports表的格式如下，
| portbindingports | CREATE TABLE portbindingports (
port_id varchar(36) NOT NULL,
host varchar(255) NOT NULL,
PRIMARY KEY (port_id),
CONSTRAINT portbindingports_OBFK_1697945240192842 FOREIGN KEY (port_id) REFERENCES neutron.ports(id) ON UPDATE RESTRICT ON DELETE RESTRICT
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = ‘zstd_1.3.8’ REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
【问题现象及影响】
添加外键时，无法使用 ON DELETE SET DEFAULT 模式，可支持其它几种模式（CASCADE RESTRICT NO ACTION）
【附件】