我想问如何通过PHP或者mysql获得一个表的外键参照情况,desc tablename、show columns from talbename只能知道该列是主键,外键,我想获得具体的情况。比方:
CREATE TABLE Users(
ID int UNSIGNED NOT NULL auto_increment,
RoleID int UNSIGNED NOT NULL,
DepartmentID int UNSIGNED NOT NULL, foreign key (RoleID) references Roles(ID),
foreign key (DepartmentID) references Departments(ID),
PRIMARY KEY (ID)
);
我想获得Users表的外键情况,想知道本表有哪此字段具有参照关系,参照哪个表的哪个字段。
CREATE TABLE Users(
ID int UNSIGNED NOT NULL auto_increment,
RoleID int UNSIGNED NOT NULL,
DepartmentID int UNSIGNED NOT NULL, foreign key (RoleID) references Roles(ID),
foreign key (DepartmentID) references Departments(ID),
PRIMARY KEY (ID)
);
我想获得Users表的外键情况,想知道本表有哪此字段具有参照关系,参照哪个表的哪个字段。
ADD CONSTRAINT `users_profile_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`);
show create table `Users`; 只是一个表结构,同样没有参照情况。To Exbox:
导出,能否具体讲一下,你是说导出到一个文件,再从导出文件里得到参照情况?
可以得到
CREATE TABLE Users(
ID int UNSIGNED NOT NULL auto_increment,
RoleID int UNSIGNED NOT NULL,
DepartmentID int UNSIGNED NOT NULL, foreign key (RoleID) references Roles(ID),
foreign key (DepartmentID) references Departments(ID),
PRIMARY KEY (ID)
);
这个建表串,你可以利用正则解析foreign key那两行。
CREATE TABLE Users(
ID int UNSIGNED NOT NULL auto_increment,
RoleID int UNSIGNED NOT NULL,
DepartmentID int UNSIGNED NOT NULL, PRIMARY KEY (`ID`),
KEY `RoleID` (`RoleID`),
KEY `DepartmentID` (`DepartmentID`)
); 这种情况你根本就不知道他是参照什么表哪个字段的,我比较了一下,当表是myisam引擎是会是这样,如果是innodb则会完全显示出来,这是为什么?又如何解决呢?
CREATE TABLE Users(
ID int UNSIGNED NOT NULL auto_increment,
RoleID int UNSIGNED NOT NULL,
DepartmentID int UNSIGNED NOT NULL, PRIMARY KEY (`ID`),
KEY `RoleID` (`RoleID`),
KEY `DepartmentID` (`DepartmentID`)
)engine=myisam;
一样能创建表啊,myisam是怎么处理外键的,约束关系还存在吗?