用的mysql 4.1三张表:两个实体:user,role,一个联系:user_rolesql语句分别是:create table user
( id int not null AUTO_INCREMENT,
username varchar(50) not null,
password varchar(50) not null,
primary key(id,username))type=innodb
create table role
( id int not null AUTO_INCREMENT,
rolename varchar(50) not null,
roleinfo varchar(50) not null,
primary key(id,rolename))type=innodb
======================================================
上面两个表没有问题.下面这个表就建不起来,总是说1005错误.CREATE TABLE `user_role` (
`id` int(11) NOT NULL auto_increment,
username varchar(50) not null,
rolename varchar(50) not null,
PRIMARY KEY (id,username,rolename),
foreign key(username) REFERENCES user on delete RESTRICT,
foreign key(rolename) REFERENCES role on delete RESTRICT)type=innodb;请问大家我这外键约束有错误么,望大家不吝赐教.
( id int not null AUTO_INCREMENT,
username varchar(50) not null,
password varchar(50) not null,
primary key(id,username))type=innodb
create table role
( id int not null AUTO_INCREMENT,
rolename varchar(50) not null,
roleinfo varchar(50) not null,
primary key(id,rolename))type=innodb
======================================================
上面两个表没有问题.下面这个表就建不起来,总是说1005错误.CREATE TABLE `user_role` (
`id` int(11) NOT NULL auto_increment,
username varchar(50) not null,
rolename varchar(50) not null,
PRIMARY KEY (id,username,rolename),
foreign key(username) REFERENCES user on delete RESTRICT,
foreign key(rolename) REFERENCES role on delete RESTRICT)type=innodb;请问大家我这外键约束有错误么,望大家不吝赐教.
解决方案 »
- 这查询结果怎么少一个,怎么只有a记录
- 如果我想在数据库中修改某个用户的多个属性,该怎么用sql语句实现呢?
- 关于delete
- 一个sql如何将多列值串联
- linux下如何在另一个端口安装高版本mysql
- 如何给一个Mysql数据库加密码呢?就是说我建的一个mysql数据库test,别人即使是从mysql\data中拷过去test也不能用,这要如何加密呢?
- 请问auto_increament的字段怎么update和insert呢?
- 请问怎么改变mysql的系统变量?
- mysql循环插入数据
- mysql++使用的问题--求救各路大牛!!
- [求助]请教导出一条记录
- 〓〓【用group by 语句后,触发器写了出现的问题不解,100分请求帮助】〓〓
primary key(id,username)
......
primary key(id,rolename)
...
username,rolename两个分别是user,role表的主键,有错么
-----------------------------------------------
username,rolename两个分别是user,role表的主键列表成员..
如果ID是自增长型的,不需要做主键吧,unique就行了.另:REFERENCES user 后面都没指定列名.