作者TeemingVoid (TeemingVoid)
看板Database
标题Re: [SQL ] 请问MySQL上资料表参照自己的做法...
时间Wed Jul 4 15:23:52 2012
※ 引述《spiderman007 (千里之外)》之铭言：
: 小弟想练习题目...资料表如下...
: ...
: (1) 新增资料时...会出现错误??
: http://ppt.cc/O0sV
OK, 以您的例子来说:
create table employee
(
ename char(10),
ssn int,
sex char(1),
salary int,
super_ssn int,
dno int,
primary key (ssn),
foreign key (super_ssn) references employee (ssn)
) engine = innodb;
-- 这样子新增资料会有错误:
insert into employee (ename, ssn, sex, salary, super_ssn, dno)
values ('Jeter', 1, 'M', 50000, null, 1);
-- 可以考虑改成这样: (主管的资料新增时先参考自己, 再修改成 null)
insert into employee (ename, ssn, sex, salary, super_ssn, dno)
values ('Jeter', 1, 'M', 50000, 1, 1);
update employee set super_ssn = null where ssn = 1;
-- 然後，其他的资料可以正常参用 ssn = 1 主管的那笔:
insert into employee (ename, ssn, sex, salary, super_ssn, dno)
values ('Kathy', 2, 'F', 50000, 1, 1);
insert into employee (ename, ssn, sex, salary, super_ssn, dno)
values ('Carl', 3, 'M', 50000, 1, 1);
select * from employee;
: (2)对於参照的疑问??
: 由於我的dno是部门的primary key，但我并没有先建立department这个资料表
: 那该如何修改employee的dno去参照department呢??
: 我试过
: alter table employee
: alter foreign key(dno) references department(dnumber);
: 但似乎错很大....
-- 继续以我们的例子来说明:
create table department
(
departmentID int,
deptName varchar(20),
primary key (departmentID)
) engine = InnoDB;
insert into department values (1, 'Accounting');
insert into department values (2, 'R&D');
alter table employee
add constraint FK_dept_employee
foreign key (dno) references department (departmentID);
--
※ 发信站: 批踢踢实业坊(ptt.cc)
◆ From: 112.104.67.251
1F：推 spiderman007:谢谢大大... 07/04 20:51