mysql主外键关联失败,SQL Server由于主外键约束导致插入失败的记录方法

最新推荐文章于 2024-07-05 16:16:04 发布

weixin_39635432

最新推荐文章于 2024-07-05 16:16:04 发布

阅读量486

点赞数

文章标签： mysql主外键关联失败

你打算从 SQL Server 2000 数据库的客户表和国别表中导入客户数据到 SQL Server 2005 数据库中。你要确保在导入过程中，客户表中国家代码列的每个值在 SQL Server 2005 数据库中国别表内都有其相应的纪录。你在两个表之间定义一外键，这样，参照完整性将保证

你打算从 SQL Server 2000 数据库的客户表和国别表中导入客户数据到 SQL Server 2005 数据库中。你要确保在导入过程中，客户表中国家代码列的每个值在 SQL Server 2005 数据库中国别表内都有其相应的纪录。你在两个表之间定义一外键，这样，参照完整性将保证若国家代码值在客户表中存在，而在国别表中不存在时导入程序失败。你要确保国别表无记录时导入过程不失败。

--创建Department(deptID,deptName)和UserInfo(userID, userName, sex, loginDate,deptid)

--其中deptID,userID是自动增长的,并且是主键。deptid是外键。并且插入测试数据

--创建序列

CREATE SEQUENCE seq_Dep

START WITH 1

INCREMENT BY 1

NOMAXVALUE

NOCYCLE

CACHE 30;

--建表Department

CREATE TABLE Department

(

deptID VARCHAR2(10) PRIMARY KEY,

deptName VARCHAR2(20)

);

--插入测试数据

INSERT INTO Department VALUES ('D'||seq_Dep.NEXTVAL,'人事部');

INSERT INTO Department VALUES ('D'||seq_Dep.NEXTVAL,'技术部');

INSERT INTO Department VALUES ('D'||seq_Dep.NEXTVAL,'后勤部');

INSERT INTO Department VALUES ('D'||seq_Dep.NEXTVAL,'经理部');

INSERT INTO Department VALUES ('D'||seq_Dep.NEXTVAL,'销售部');

INSERT INTO Department VALUES ('D'||seq_Dep.NEXTVAL,'服务部');

INSERT INTO Department VALUES ('D'||seq_Dep.NEXTVAL,'公关部');

COMMIT;

--创建序列

CREATE SEQUENCE seq_User

START WITH 1

INCREMENT BY 1

NOMAXVALUE

NOCYCLE

CACHE 30;

--建表UserInfo

CREATE TABLE UserInfo

(

userID VARCHAR2(10) PRIMARY KEY,

userName VARCHAR2(20),

sex VARCHAR2(2),

loginDate DATE,

deptid VARCHAR2(10) REFERENCES Department (deptID)

);

--插入测试数据

INSERT INTO UserInfo VALUES ('U'||seq_User.NEXTVAL,'Eric Schmidt','男','12-9月-07','D1');

INSERT INTO UserInfo VALUES ('U'||seq_User.NEXTVAL,'Larry Page','男','12-10月-07','D3');

INSERT INTO UserInfo VALUES ('U'||seq_User.NEXTVAL,'Sergey Brin','男','12-11月-07','D5');

INSERT INTO UserInfo VALUES ('U'||seq_User.NEXTVAL,'W. M. Coughran, Jr.','男','1-12月-07','D4');

INSERT INTO UserInfo VALUES ('U'||seq_User.NEXTVAL,'David C. Drummond','女','12-12月-01','D2');

INSERT INTO UserInfo VALUES ('U'||seq_User.NEXTVAL,'Alan Eustace','男','12-9月-07','D1');

INSERT INTO UserInfo VALUES ('U'||seq_User.NEXTVAL,'Jeff Huber','男','12-10月-07','D3');

INSERT INTO UserInfo VALUES ('U'||seq_User.NEXTVAL,'George Reyes','男','12-11月-07','D5');

INSERT INTO UserInfo VALUES ('U'||seq_User.NEXTVAL,'Elliot Schrage','男','1-12月-07','D4');

INSERT INTO UserInfo VALUES ('U'||seq_User.NEXTVAL,'Tim Armstrong','女','12-12月-01','D2');

INSERT INTO UserInfo VALUES ('U'||seq_User.NEXTVAL,'Nikesh Arora','女','12-12月-01','D2');

INSERT INTO UserInfo VALUES ('U'||seq_User.NEXTVAL,'Sukhinder','男','12-9月-07','D1');

INSERT INTO UserInfo VALUES ('U'||seq_User.NEXTVAL,'Vinton G. Cerf','男','12-10月-07','D3');

INSERT INTO UserInfo VALUES ('U'||seq_User.NEXTVAL,'George Reyes','男','12-11月-07','D5');

INSERT INTO UserInfo VALUES ('U'||seq_User.NEXTVAL,'Dave Girouard','男','1-12月-07','D4');

INSERT INTO UserInfo VALUES ('U'||seq_User.NEXTVAL,'Singh Cassidy','女','12-12月-01','D2');

COMMIT;

--3. 在deptid建立索引

CREATE INDEX deptid_index on UserInfo(deptid);

--4. 给Department和UserInfo创建同义词，名称分别是dept, sy_user

CREATE OR REPLACE SYNONYM dept FOR Department;

CREATE OR REPLACE SYNONYM sy_user FOR UserInfo;

5. 通过同义词dept, user来创建视图，

视图要求能查询出“部门名称，部门编号，用户名，性别，注册时间”

CREATE OR REPLACE VIEW view_dept_user

("部门名称","部门编号","用户名","性别","注册时间")

AS SELECT d.deptName,d.deptID,u.userName,u.sex,u.loginDate

FROM dept d ,sy_user u

WHERE d.deptID=u.deptid;

--14. 使用Instead Of触发器，往第5题创建的视图中插入数据。

SET SERVEROUTPUT ON;

INSERT INTO view_dept_user ('部门名称','部门编号','用户名','性别','注册时间')

VALUES('仓储部','D6','Dejan Perkovic','男','1-1月-08');

CREATE OR REPLACE VIEW view_dept_user

("部门名称","部门编号","用户名","性别","注册时间")

AS SELECT d.deptName,d.deptID,u.userName,u.sex,u.loginDate

FROM dept d ,sy_user u

WHERE d.deptID=u.deptid;

CREATE OR REPLACE TRIGGER dept_user_insert

INSTEAD OF INSERT ON view_dept_user

FOR EACH ROW

DECLARE

CURSOR cur_dept IS SELECT * FROM Department

WHERE Department.deptID = :NEW.deptID;

CURSOR cur_user IS SELECT * FROM sy_user

WHERE sy_user.userName = :NEW.userName;

d cur_dept%rowtype;

u cur_user%rowtype;

did dept.deptID%TYPE;

uid sy_user.userID%TYPE;

BEGIN

OPEN cur_dept;

OPEN cur_user;

FETCH cur_user INTO u;

FETCH cur_dept INTO d;

如果插入的数据中部门ID不存在,则将 seq_Dep.NEXTVAL 产生的序号赋值给变量did,执行插入语句新增加一个部门。

IF cur_dept%NOTFOUND THEN

did := 'D'||seq_Dep.NEXTVAL;

INSERT INTO Department(deptID,deptName) VALUES(did,:NEW.deptName);

如果插入数据中的员工不存在，则执行插入语句增加一个新员工

IF cur_user%NOTFOUND THEN

uid := 'U'||seq_User.NEXTVAL;

INSERT INTO UserInfo(userID,userName,sex,loginDate,deptid) VALUES(uid,:NEW.userName,:NEW.sex,:NEW.loginDate,did);

END IF;

IF cur_dept%FOUND THEN

如果插入的数据中部门ID是已经存在的部门ID,则更新部门名称

did:=:NEW.deptID;

UPDATE Department SET Department.deptName WHERE Department.deptID = :NEW.deptID;

如果插入数据中的员工不存在，则将seq_User.NEXTVAL 产生的序号赋值给变量uid,执行插入语句增加一个新员工

IF cur_user%NOTFOUND THEN

uid := 'U'||seq_User.NEXTVAL;

INSERT INTO UserInfo VALUES(uid,:NEW.userName,:NEW.sex,:NEW.loginDate,:NEW.deptID);

ELSE

如果插入数据中的员工已存在，则根据部门ID、员工姓名来更新员工的其他字段的值，员工ID为主键，不用更新。

考虑到同名员工存在的可能，但员工的ID不可能相同的特点，需要根据插入数据中的部门ID、员工姓名来查询出

符合要求的员工的ID，用员工ID来进行后续的操作

SELECT userID INTO uid FROM UserInfo WHERE UserInfo.userName=:NEW.userName AND UserInfo.deptID=did;

UPDATE UserInfo SET UserInfo.sex = :NEW.sex,UserInfo.loginDate = :NEW.loginDate,UserInfo.deptID = :NEW.sex

WHERE UserInfo.userID=uid AND UserInfo.userName=:NEW.userName;

END IF;

CLOSE ecur;

CLOSE dcur;

END dept_user_insert;

show errors;

出现的错误提示:

警告: 创建的触发器带有编译错误。

SQL> show errors;

TRIGGER DEPT_USER_INSERT 出现错误:

LINE/COL ERROR

-------- -----------------------------------------------------------------

3/31 PLS-00049: 错误的赋值变量 'NEW.DEPTID'

5/30 PLS-00049: 错误的赋值变量 'NEW.USERNAME'

20/56 PLS-00049: 错误的赋值变量 'NEW.DEPTNAME'

26/75 PLS-00049: 错误的赋值变量 'NEW.USERNAME'

26/89 PLS-00049: 错误的赋值变量 'NEW.SEX'

26/98 PLS-00049: 错误的赋值变量 'NEW.LOGINDATE'

33/10 PLS-00049: 错误的赋值变量 'NEW.DEPTID'

34/4 PL/SQL: SQL Statement ignored

34/46 PL/SQL: ORA-00927: 缺少等号

34/72 PLS-00049: 错误的赋值变量 'NEW.DEPTID'

40/37 PLS-00049: 错误的赋值变量 'NEW.USERNAME'

LINE/COL ERROR

-------- -----------------------------------------------------------------

40/51 PLS-00049: 错误的赋值变量 'NEW.SEX'

40/60 PLS-00049: 错误的赋值变量 'NEW.LOGINDATE'

40/75 PLS-00049: 错误的赋值变量 'NEW.DEPTID'

47/66 PLS-00049: 错误的赋值变量 'NEW.USERNAME'

49/40 PLS-00049: 错误的赋值变量 'NEW.SEX'

49/70 PLS-00049: 错误的赋值变量 'NEW.LOGINDATE'

49/103 PLS-00049: 错误的赋值变量 'NEW.SEX'

50/53 PLS-00049: 错误的赋值变量 'NEW.USERNAME'

55/5 PLS-00103: 出现符号 "DEPT_USER_INSERT"在需要下列之一时：

本文原创发布php中文网，转载请注明出处，感谢您的尊重！