create table A
(
rtuid varchar(10) not null ,
ctuip varchar(20) not null ,
tuid varchar(10) not null ,
slotid varchar(10) not null ,
portid varchar(10) not null ,
Btuip varchar(20) ,
Btuid varchar(10) ,
Bslotid varchar(10) ,
name varchar(100) ,
description varchar(255) ,
primary key (rtuid, ctuip, tuid, slotid, portid)
);
create table B
(
rtuid varchar(10) not null ,
ctuip varchar(20) not null ,
tuid varchar(10) not null ,
slotid varchar(10) not null ,
name varchar(100) comment ,
description varchar(255) comment ,
primary key (rtuid, ctuip, tuid, slotid)
);
-- 外键(执行到这里时候会报Can't create table 'xx.#sql-6a8_36f1' (errno: 150)),请问是怎么回事?
alter table A add constraint fk_A foreign key (rtuid, Bctuip, Btuid, Bslotid)
references B (rtuid, ctuip, tuid, slotid) on delete set null on update restrict;
(
rtuid VARCHAR(10) NOT NULL ,
ctuip VARCHAR(20) NOT NULL ,
tuid VARCHAR(10) NOT NULL ,
slotid VARCHAR(10) NOT NULL ,
portid VARCHAR(10) NOT NULL ,
Bctuip VARCHAR(20) ,
Btuid VARCHAR(10) ,
Bslotid VARCHAR(10) ,
NAME VARCHAR(100) ,
description VARCHAR(255) ,
PRIMARY KEY (rtuid, Bctuip, Btuid, Bslotid, portid)
);
CREATE TABLE B
(
rtuid VARCHAR(10) NOT NULL ,
ctuip VARCHAR(20) NOT NULL ,
tuid VARCHAR(10) NOT NULL ,
slotid VARCHAR(10) NOT NULL ,
NAME VARCHAR(100) ,
description VARCHAR(255) ,
PRIMARY KEY (rtuid, ctuip, tuid, slotid)
);
-- 外键(执行到这里时候会报Can't create table 'xx.#sql-6a8_36f1' (errno: 150)),请问是怎么回事?
ALTER TABLE A ADD CONSTRAINT fk_A_id FOREIGN KEY (rtuid, Bctuip, Btuid, Bslotid)
REFERENCES B (rtuid, ctuip, tuid, slotid) ON DELETE SET NULL ON UPDATE RESTRICT;
头晕了...
(
rtuid VARCHAR(10) NOT NULL ,
ctuip VARCHAR(20) NOT NULL ,
tuid VARCHAR(10) NOT NULL ,
slotid VARCHAR(10) NOT NULL ,
NAME VARCHAR(100) ,
description VARCHAR(255) ,
PRIMARY KEY (rtuid, ctuip, tuid, slotid),
) ;CREATE TABLE A
( rtuid VARCHAR(10) NOT NULL ,
ctuip VARCHAR(20) NOT NULL ,
tuid VARCHAR(10) NOT NULL ,
slotid VARCHAR(10) NOT NULL ,
portid VARCHAR(10) NOT NULL ,
Bctuip VARCHAR(20) ,
Btuid VARCHAR(10) ,
Bslotid VARCHAR(10) ,
`NAME` VARCHAR(100) ,
description VARCHAR(255) ,
PRIMARY KEY (rtuid, ctuip, tuid, slotid, portid),
CONSTRAINT fk_A_id FOREIGN KEY (rtuid, Bctuip, Btuid, Bslotid)
REFERENCES B (rtuid, ctuip, tuid, slotid) ON DELETE CASCADE ON UPDATE CASCADE);
(
rtuid VARCHAR(10) NOT NULL ,
ctuip VARCHAR(20) NOT NULL ,
tuid VARCHAR(10) NOT NULL ,
slotid VARCHAR(10) NOT NULL ,
NAME VARCHAR(100) ,
description VARCHAR(255) ,
PRIMARY KEY (rtuid, ctuip, tuid, slotid)) ;
首先非常感谢你的帮助.但你这是级联删除和更新,我想实现的是,当删除B时候,将A表中的Bctuip,Btuid,Bslotid这三个字段置为NULL.
其他有外键的表使用ON DELETE SET NULL ON UPDATE RESTRICT;都行,就这个每次都报错..
rutid是主键不能为空,所以外键那里我两边都去掉了rtuid,仍然还是报Can't create table 'xx.#sql-6a8_36f1' (errno: 150)..哎,受不了..
(
rtuid VARCHAR(10) NOT NULL ,
ctuip VARCHAR(20) NULL ,
tuid VARCHAR(10) NULL ,
slotid VARCHAR(10) NULL ,
`NAME` VARCHAR(100) ,
description VARCHAR(255) ,
PRIMARY KEY(rtuid),
KEY ( ctuip, tuid, slotid)) ;CREATE TABLE A
( rtuid VARCHAR(10) NOT NULL ,
ctuip VARCHAR(20) ,
tuid VARCHAR(10) ,
slotid VARCHAR(10) ,
portid VARCHAR(10) ,
Bctuip VARCHAR(20) NULL,
Btuid VARCHAR(10) NULL,
Bslotid VARCHAR(10) NULL,
`NAME` VARCHAR(100) ,
description VARCHAR(255) ,
PRIMARY KEY (rtuid),
KEY (Bctuip, Btuid, Bslotid),
CONSTRAINT fk_A_id FOREIGN KEY (Bctuip, Btuid, Bslotid)
REFERENCES B (ctuip, tuid, slotid) ON DELETE SET NULL ON UPDATE RESTRICT);