数据库设计实现严格的一对一关系

目标：

某单位的职工的基本信息主要有：职工号、姓名、性别、出生日期、身份证号、家庭住址、联系电话，职工的登陆信息主要有：登陆账号、登陆密码和证书摘要。这些数据项都非空。其类图如下图所示：![类图](http://img.blog.csdn.net/20170622151709757?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvejY1NDkzODg3NA==/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast)请给出基于Oracle实现的相关SQL脚本，具体包括：     1. 建表语句     2. 保证主码职工号修改的一致性     3. 保证两个表的对应数据同时删除     4. 保证两个表的对应数据同时插入

实现：

1. 建表

   create table 职工信息表(       职工号    int primary key,       姓名      varchar2(30) not null,       性别      char(3)  not null,       出生日期   date  not null,       身份证     char(18) not null,       家庭住址   varchar2(300) not null,       联系电话   varchar2(20) not null   )   /*注意设置级联删除和延迟约束*/   create table 登陆信息表(     登陆账号    varchar2(20) primary key,     职工号      int unique not null,     登陆密码    varchar2(20) not null,     证书摘要    char(256) not null,     CONSTRAINT fk_login_info FOREIGN KEY (职工号) REFERENCES 职工信息表 (职工号) ON DELETE CASCADE DEFERRABLE   )

2. 设置触发器保证同时修改

      /*保证主码职工号修改的一致性*/  CREATE OR REPLACE TRIGGER BIF_UPDATE_LIF  AFTER UPDATE OF 职工号ON 职工信息表  FOR EACH ROW  BEGIN    UPDATE 登陆信息表    SET 职工号=:NEW.职工号    WHERE 职工号=:OLD.职工号;  END;

3. 设置触发器保证同时删除

   /*保证两个表的对应数据同时删除（已经设置级联，所以现在只用在删除登陆信息表记录时删除职工信息表对应记录）*/ CREATE OR REPLACE TRIGGER BIF_DELETE_LIF AFTER DELETE ON 登陆信息表 FOR EACH ROW BEGIN    DELETE FROM 职工信息表    WHERE  职工号=:OLD.职工号; END;

4. 设置触发器配合使用临时表实现同时插入（只允许调用存储过程插入）

   /保证两个表的对应数据同时插入/

   • 创建与 职工信息表 结构相同的临时表user_temp

     CREATE TABLE user_temp
     AS SELECT * FROM 职工信息表 WHERE 1=2;

   • 插入数据的存储过程

     CREATE OR REPLACE PROCEDURE PR_INSERT_TEA_SEC(
     职工号 int,
     姓名 varchar2(30),
     性别 char(3),
     出生日期 date,
     身份证 char(18),
     家庭住址 varchar2(300),
     联系电话 varchar2(20),
     登陆账号 varchar2(20),
     登陆密码 varchar2(20),
     证书摘要 char(256),
     P_RESULT OUT VARCHAR2(200)
     BEGIN
     P_RESULT:=”;
     –先插入 user_temp
     INSERT INTO user_temp
     VALUES(登陆账号,职工号 ,登陆密码 ,证书摘要);
     –再插入 职工信息表
     INSERT INTO 职工信息表VALUES
     (职工号,姓名,性别 ,出生日期 ,身份证 ,家庭住址,联系电话);
     COMMIT;
     –删除user_temp
     DELETE FROM user_temp
     WHERE 登陆账号=登陆账号;
     COMMIT;
     P_RESULT:=’插入成功！’;
     EXCEPTION
     WHEN OTHERS THEN
     ROLLBACK;
     P_RESULT:=’插入时报错:’||SUBSTR(SQLERRM,1,200);
     END;

   • 插入 职工信息表 数据的触发器
     ——保证同时插入到 登陆信息表 ，这样做的目的是防止绕过前台应用程序而直接在后台数据库操作时也能够保证同时插入。*/

     CREATE OR REPLACE TRIGGER BIF_INSERT_LIF
     AFTER INSERT ON 职工信息表
     FOR EACH ROW
     DECLARE
     V_CNT INT;
     BEGIN
     SELECT COUNT(*) INTO V_CNT
     FROM user_temp
     WHERE 职工号=:NEW.职工号;

     IF V_CNT=1 THEN
     INSERT INTO 登陆信息表
     SELECT * FROM user_temp
     WHERE 职工号=:NEW.职工号;
     ELSE
     RAISE_APPLICATION_ERROR(‘-20020’,’必须同时插入！’);
     END IF;
     END;