我在做学校的软件工程作业,需要利用mysql数据库,于是设计了以下内容,本以为外键建立成功了.结果运行后发现没有起到作用。(可插入外键以外的数据、无法防止意外的删除)
不知道是哪里出了问题,正确的方式应该是如何写呢?drop database student_score;
create database student_score;
use student_score;create table best_power
(
id nchar(50) primary key
);insert into best_power (id) values('true');
insert into best_power (id) values('false');create table class
(
name nchar(50) primary key
);create table student
(
id nchar(50) primary key,
name nchar(50) not null,
class nchar(50) not null references class(name)
);create table teacher
(
id nchar(50) primary key,
name nchar(50) not null,
password nchar(50) not null,
best_power nchar(50) not null references best_power(id)
);create table course
(
id nchar(50) primary key,
name nchar(50) not null,
teacher nchar(50) not null references teacher(id)
);create table score
(
student_id nchar(50) not null references student(id),
course_id nchar(50) not null references course(id),
score int not null,
primary key(student_id,course_id)
);insert into teacher (id,name,password,best_power) values('root','root','123456','true');
不知道是哪里出了问题,正确的方式应该是如何写呢?drop database student_score;
create database student_score;
use student_score;create table best_power
(
id nchar(50) primary key
);insert into best_power (id) values('true');
insert into best_power (id) values('false');create table class
(
name nchar(50) primary key
);create table student
(
id nchar(50) primary key,
name nchar(50) not null,
class nchar(50) not null references class(name)
);create table teacher
(
id nchar(50) primary key,
name nchar(50) not null,
password nchar(50) not null,
best_power nchar(50) not null references best_power(id)
);create table course
(
id nchar(50) primary key,
name nchar(50) not null,
teacher nchar(50) not null references teacher(id)
);create table score
(
student_id nchar(50) not null references student(id),
course_id nchar(50) not null references course(id),
score int not null,
primary key(student_id,course_id)
);insert into teacher (id,name,password,best_power) values('root','root','123456','true');
建议不要一次列出这么多表,找出其中有代表性的两个表就可以测试外键是否作用了。多了反而让大家看不清你的问题在哪儿。
mysql> create table t1 (id int primary key) engine=innodb;
Query OK, 0 rows affected (0.08 sec)mysql> create table t2 (
-> id int primary key,
-> t1 int ,
-> foreign key (t1) references t1(id)
-> ) engine=innodb;
Query OK, 0 rows affected (0.09 sec)mysql> insert into t1 values (1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0mysql> select * from t1;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
+----+
3 rows in set (0.00 sec)mysql> insert into t2 values (1,4);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f
ails (`csdn`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t1`) REFERENCES `t1` (`i
d`))
mysql> insert into t2 values (1,1);
Query OK, 1 row affected (0.06 sec)mysql>
mysql> show variables like 'foreign_key_checks';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| foreign_key_checks | ON |
+--------------------+-------+
1 row in set (0.00 sec)mysql>
MySQL版本也很新
mysql> show create table student;
+---------+------------------------------
-----------------------------------------
------------------+
| Table | Create Table |
+---------+------------------------------
-----------------------------------------
------------------+
| student | CREATE TABLE `student` (
`id` char(50) NOT NULL,
`name` char(50) NOT NULL,
`class` char(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+------------------------------
-----------------------------------------
------------------+
1 row in set (0.07 sec)