MYSQl中定义外键出问题了

justforregister

原帖由 〇〇 于 2009-7-28 15:30 发表
对照ls的步骤
解决如下
mysql> create database Loan;
Query OK, 1 row affected (0.05 sec)

mysql> use Loan;
Database changed
mysql> create table student(number varchar(10) not null primary key,name varchar(10) not null,loanNumber varchar(10),
    -> ID varchar(20) not null,sex varchar(5) not null,birthday varchar(50) not null,enterSchoolTime varchar(50) not null,address varchar(50
),
    -> postID varchar(10) not null,telNumber varchar(15),class varchar(50),academy varchar(50),major varchar(20),
    -> loanType int,loanAmount int,loanState varchar(10))TYPE=INNODB;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> create table loan(loanNumber varchar(10) ,name varchar(10) not null,number varchar(10) not null ,
    -> applyLoanTime varchar(20) not null,
    -> class varchar(50),academy varchar(50),major varchar(20),
    -> loanType int,loanAmount int,loanState varchar(10),FOREIGN KEY (loanNumber,name,number,class,academy,major,loanType,loanAmount,loanSta
te)
    -> REFERENCES student(loanNumber,name,number,class,academy,major,loanType,loanAmount,loanState),primary key(number,loanNumber))TYPE=INNO
DB;
ERROR 1005 (HY000): Can't create table '.\loan\loan.frm' (errno: 150)
mysql> create table loan(loanNumber varchar(10) ,name varchar(10) not null,number varchar(10) not null ,
    -> applyLoanTime varchar(20) not null,
    -> class varchar(50),academy varchar(50),major varchar(20),
    -> loanType int,loanAmount int,loanState varchar(10),primary key(number,loanNumber))TYPE=INNODB;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create unique index index_loan on loan(loanNumber,name,number,class,academy,major,loanType,loanAmount,loanState);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table loan add FOREIGN KEY (loanNumber,name,number,class,academy,major,loanType,loanAmount,loanState)
    -> REFERENCES student(loanNumber,name,number,class,academy,major,loanType,loanAmount,loanState);
ERROR 1005 (HY000): Can't create table '.\loan\#sql-c54_1.frm' (errno: 150)
mysql> desc student
    -> ;
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| number          | varchar(10) | NO   | PRI |         |       |
| name            | varchar(10) | NO   |     |         |       |
| loanNumber      | varchar(10) | YES  |     | NULL    |       |
| ID              | varchar(20) | NO   |     |         |       |
| sex             | varchar(5)  | NO   |     |         |       |
| birthday        | varchar(50) | NO   |     |         |       |
| enterSchoolTime | varchar(50) | NO   |     |         |       |
| address         | varchar(50) | YES  |     | NULL    |       |
| postID          | varchar(10) | NO   |     |         |       |
| telNumber       | varchar(15) | YES  |     | NULL    |       |
| class           | varchar(50) | YES  |     | NULL    |       |
| academy         | varchar(50) | YES  |     | NULL    |       |
| major           | varchar(20) | YES  |     | NULL    |       |
| loanType        | int(11)     | YES  |     | NULL    |       |
| loanAmount      | int(11)     | YES  |     | NULL    |       |
| loanState       | varchar(10) | YES  |     | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+
16 rows in set (0.02 sec)

mysql> desc loan;
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| loanNumber    | varchar(10) | NO   | PRI |         |       |
| name          | varchar(10) | NO   |     |         |       |
| number        | varchar(10) | NO   | PRI |         |       |
| applyLoanTime | varchar(20) | NO   |     |         |       |
| class         | varchar(50) | YES  |     | NULL    |       |
| academy       | varchar(50) | YES  |     | NULL    |       |
| major         | varchar(20) | YES  |     | NULL    |       |
| loanType      | int(11)     | YES  |     | NULL    |       |
| loanAmount    | int(11)     | YES  |     | NULL    |       |
| loanState     | varchar(10) | YES  |     | NULL    |       |
+---------------+-------------+------+-----+---------+-------+
10 rows in set (0.00 sec)

mysql> create unique index index_student on student(loanNumber,name,number,class,academy,major,loanType,loanAmount,loanState);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table loan add FOREIGN KEY (loanNumber,name,number,class,academy,major,loanType,loanAmount,loanState)
    -> REFERENCES student(loanNumber,name,number,class,academy,major,loanType,loanAmount,loanState);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

david168520

原来是两个表中都要建立索引!谢谢你的加答!

david168520

我按你的要求做了,可以那样创建,但进行修改loan表时候,却发生了异常, Cannot add or update a child row: a foreign key constraint fails (`loan/loan`, CONSTRAINT `loan_ibfk_1` FOREIGN KEY (`loanNumber`, `name`, `number`, `class`, `academy`, `major`, `loanType`, `loanAmount`, `loanState`) REFERENCES `student` (`loanNumber`, `name);这是异常信息.
对loan表进行删除时候,student表中的没有及时更新,请问是什么原因啊,谢谢赐教!
我对你那个创建索引稍微加了点东西(后面加了级联操作,但为何还会出这样的问题啊!):alter table loan add FOREIGN KEY (loanNumber,name,number,class,academy,major,loanType,loanAmount,loanState)
REFERENCES student(loanNumber,name,number,class,academy,major,loanType,loanAmount,loanState)ON UPDATE CASCADE ON DELETE CASCADE;