MySQL 的外键怎么设啊?
create table user(
userid int not null AUTO_INCREMENT,
username char(20) not null,
psw char(30) not null,
sex char(10) null,
addr char(50),
email char(30),
atg varchar(255),
primary key (userid,username)
)ENGINE=InnoDB;create table theme(
themeid int not null auto_increment,
userid int not null,
title char(30) not null,
content varchar(255),
time char(30),
rootid int,
primary key (themeid),
foreign key (userid) references user (userid) on delete restrict on update cascade
)ENGINE=InnoDB;
这样的话可以成功添加外键
但是修改了成
create table user(
userid int not null AUTO_INCREMENT,
username char(20) not null,
psw char(30) not null,
sex char(10) null,
addr char(50),
email char(30),
atg varchar(255),
primary key (userid,username)
)ENGINE=InnoDB;create table theme(
themeid int not null auto_increment,
username char(20) not null,
title char(30) not null,
content varchar(255),
time char(30),
rootid int,
primary key (themeid),
foreign key (username) references user (username) on delete restrict on update cascade
)ENGINE=InnoDB;
就是不能建立theme 表,请高手指教!!
create table user(
userid int not null AUTO_INCREMENT,
username char(20) not null,
psw char(30) not null,
sex char(10) null,
addr char(50),
email char(30),
atg varchar(255),
primary key (userid,username)
)ENGINE=InnoDB;create table theme(
themeid int not null auto_increment,
userid int not null,
title char(30) not null,
content varchar(255),
time char(30),
rootid int,
primary key (themeid),
foreign key (userid) references user (userid) on delete restrict on update cascade
)ENGINE=InnoDB;
这样的话可以成功添加外键
但是修改了成
create table user(
userid int not null AUTO_INCREMENT,
username char(20) not null,
psw char(30) not null,
sex char(10) null,
addr char(50),
email char(30),
atg varchar(255),
primary key (userid,username)
)ENGINE=InnoDB;create table theme(
themeid int not null auto_increment,
username char(20) not null,
title char(30) not null,
content varchar(255),
time char(30),
rootid int,
primary key (themeid),
foreign key (username) references user (username) on delete restrict on update cascade
)ENGINE=InnoDB;
就是不能建立theme 表,请高手指教!!
username 不是主键,只是主键的一部分。会有重复。
/*
SQLyog Enterprise - MySQL GUI v6.06
Host - 5.1.12-beta-community-nt : Database - hospital
*********************************************************************
Server version : 5.1.12-beta-community-nt
*/
/*!40101 SET NAMES utf8 */;/*!40101 SET SQL_MODE=''*/;create database if not exists `hospital`;USE `hospital`;/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;/*Table structure for table `employee` */DROP TABLE IF EXISTS `employee`;CREATE TABLE `employee` (
`name` varchar(20) NOT NULL,
`password` varchar(20) NOT NULL,
PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;/*Data for the table `employee` */insert into `employee`(`name`,`password`) values ('jhj','1234'),('sa','123');/*Table structure for table `owners` */DROP TABLE IF EXISTS `owners`;CREATE TABLE `owners` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(30) DEFAULT NULL,
`address` varchar(255) DEFAULT NULL,
`city` varchar(80) DEFAULT NULL,
`telephone` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;/*Data for the table `owners` *//*Table structure for table `pets` */DROP TABLE IF EXISTS `pets`;CREATE TABLE `pets` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`owner_id` int(11) NOT NULL,
`type_id` int(11) NOT NULL,
`name` varchar(30) DEFAULT NULL,
`birth_date` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `FK_pets` (`owner_id`),
KEY `FK_pets1` (`type_id`),
CONSTRAINT `FK_pets` FOREIGN KEY (`owner_id`) REFERENCES `owners` (`id`),
CONSTRAINT `FK_pets1` FOREIGN KEY (`type_id`) REFERENCES `types` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;/*Data for the table `pets` *//*Table structure for table `specialties` */DROP TABLE IF EXISTS `specialties`;CREATE TABLE `specialties` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(80) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;/*Data for the table `specialties` *//*Table structure for table `types` */DROP TABLE IF EXISTS `types`;CREATE TABLE `types` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(80) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;/*Data for the table `types` *//*Table structure for table `vet_specialties` */DROP TABLE IF EXISTS `vet_specialties`;CREATE TABLE `vet_specialties` (
`vet_id` int(11) NOT NULL,
`specialty_id` int(11) NOT NULL,
KEY `FK_vet_specialties` (`vet_id`),
KEY `FK_vet_specialties1` (`specialty_id`),
CONSTRAINT `FK_vet_specialties1` FOREIGN KEY (`specialty_id`) REFERENCES `specialties` (`id`),
CONSTRAINT `FK_vet_specialties` FOREIGN KEY (`vet_id`) REFERENCES `vets` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;/*Data for the table `vet_specialties` *//*Table structure for table `vets` */DROP TABLE IF EXISTS `vets`;CREATE TABLE `vets` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;/*Data for the table `vets` *//*Table structure for table `visits` */DROP TABLE IF EXISTS `visits`;CREATE TABLE `visits` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`pet_id` int(11) NOT NULL,
`visit_date` datetime DEFAULT NULL,
`description` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`,`pet_id`),
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;/*Data for the table `visits` *//*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;