实际情况是:一个基础数据表,如用户表。为了防止表过大而造成的麻烦,我想把这个表分成两张表,一张正常用户表,一张历史用户表。把那些超过规定时间无任何操作的用户放在历史用户表里面。这样,正常用户表,就会比总用户表小几十倍,甚至几百倍,我的日常数据库操作,就只针对正常用户表进行,效率应该会提升不少。问题是:由于用户表是基础表,其他的表肯定会引用用户表的关键字段作为外键,这样一个表分成两个表后,怎样处理这个外键问题?如:
create table CM_UserRecord (
UR_Name varchar(20) not null,
constraint PK_CM_USERRECORD primary key (UR_Name)
)create table CM_OldUserRecord (
UR_Name varchar(20) not null,
constraint PK_CM_USERRECORD primary key (UR_Name)
)create table CM_UserWorkRecord (
UR_Name varchar(20) not null,
UWR_Work varchar(20) not null
)
=======怎样使CM_UserWorkRecord 的UR_Name字段成为CM_OldUserRecord和CM_UserRecord的共同外键。
create table CM_UserRecord (
UR_Name varchar(20) not null,
constraint PK_CM_USERRECORD primary key (UR_Name)
)create table CM_OldUserRecord (
UR_Name varchar(20) not null,
constraint PK_CM_USERRECORD primary key (UR_Name)
)create table CM_UserWorkRecord (
UR_Name varchar(20) not null,
UWR_Work varchar(20) not null
)
=======怎样使CM_UserWorkRecord 的UR_Name字段成为CM_OldUserRecord和CM_UserRecord的共同外键。
add constraint fk_CM_OldUserRecord_CM_UserRecord foreign key (UR_Name) references CM_UserRecord(UR_Name)alter table CM_UserWorkRecord
add constraint fk_CM_UserWorkRecord_CM_UserRecord foreign key (UR_Name) references CM_UserRecord(UR_Name)
create table CM_UserRecord (
UR_Name varchar(20) not null,
constraint PK_CM_USERRECORD_2 primary key (UR_Name)
)create table CM_OldUserRecord (
UR_Name varchar(20) not null,
constraint PK_CM_USERRECORD_1 primary key (UR_Name)
)create table CM_UserWorkRecord (
UR_Name varchar(20) not null,
UWR_Work varchar(20) not nullalter table CM_UserWorkRecord add constraint FK_S1 foreign key (UR_Name) references CM_UserRecord(UR_Name )
alter table CM_UserWorkRecord add constraint FK_S2 foreign key (UR_Name) references CM_OldUserRecord (UR_Name )