MYSQL人事工资管理系统--主键和外键、查询约束（二）

接下来完整数据库表的设计，对表设置主键，表与表的外键，唯一约束，查询约束。插入记录

1)对各个表创建主键约束；

MYSQL>ALTER TABLE DEPARTMENT ADD CONTRAINT PRIMARY KEY(DEPARTMENT_NAME);

MYSQL>DESC DEPARTMENT;

MYSQL>ALTER TABLE EMPLOYEE ADD CONSTRAINT PRIMARY KEY(EMPLOYEE_ID);

MYSQL>DESC EMPLOYEE;

MYSQL>ALTER TABLE ATTENDANCE ADD CONTRAINT PRIMARY KEY(EMPLOYEE_ID,DATE);

MYSQL>DESC ATTENDANCE;

MYSQL>ALTER TABLE WAGE ADD CONSTRAINT PRIMARY KEY(EMPLOYEE_ID);

MYSQL>DESC WAGE;

MYSQL>ALTER TABLE PWAGE ADD CONSTRAINT PRIMARY KEY(EMPLOYEE_ID,DATE);

MYSQL>DESC PWAGE;

MYSQL>ALTER TABLE RP ADD CONSTRAINT PRIMARY KEY(NUMBER);

MYSQL>DESC RP;

MYSQL>ALTER TABLE EMPLOYEE_CHANGES ADD CONSTRAINT PRIMARY KEY(NUMBER);

MYSQL>DESC EMPLOYEE_CHANGES;

2)对存在参照(即引用)关系的表创建外键约束（如部门表 -- >职工表；职工档案表 -- >工资档案表；职工档案表 -- >工资发放表；职工档案表 -- >考勤表；职工档案表 -- >奖惩表），建立各表之间的关系图如下图所示：

MYSQL>ALTER TABLE EMPLOYEE ADD CONSTRAINT FOREIGN KEY(DEPARTMENT_NAME) REFERENCES DEPARTMENT(DEPARTMENT_NAME);

MYSQL>ALTER TABLE ATTENDANCE ADD CONSTRAINT FOREIGN KEY(DEPARTMENT_ID) REFERENCES EMPLOYEE(DEPARTMENT_ID);

MYSQL>DESC ATTENDANCE;

MYSQL>ALTER TABLE WAGE ADD CONSTRAINT FOREIGN KEY(DEPARTMENT_ID) REFERENCES EMPLOYEE(DEPARTMENT_ID);

MYSQL>DESC WAGE;

MYSQL>ALTER TABLE PWAGE ADD CONSTRAINT FOREIGN KEY(DEPARTMENT_ID) REFERENCES EMPLOYEE(DEPARTMENT_ID);

MYSQL>DESC PWAGE;

MYSQL>ALTER TABLE RP ADD CONSTRAINT FOREIGN KEY(DEPARTMENT_ID) REFERENCES EMPLOYEE(DEPARTMENT_ID);

MYSQL>DESC RP;

MYSQL>ALTER TABLE EMPLOYEE_CHANGES ADD CONSTRAINT FOREIGN KEY(DEPARTMENT_ID) REFERENCES EMPLOYEE(DEPARTMENT_ID);

MYSQL>DESC EMPLOYEE_CHENGES;

3)对部门表的”部门号”创建唯一约束；

MYSQL>ALTER TABLE DEPARTMENT ADD CONSTRAINT UNIQUE(DEPARTMENT_ID);

MYSQL>DESC DEPARTMENT;

3)对工资档案表的”基本工资”创建检查约束，使其值大于等于0；对职工表的”出生日期”创建检查约束，使其取值小于系统日期；对职工表、工资表、出勤表和奖惩表的”职工编号” 创建检查约束，检查其长度是否为4；

MYSQL>ALTER TABLE WAGE ADD CONSTRAINT CHECK(BASIC_WAGE > 0);

MYSQL>DESC WAGE;

MYSQL>ALTER TABLE EMPLOYEE ADD CONSTRAINT CHE_BIRTH CHECK (BIRTH <GETDATE());

MYSQL>DESC EMPLOYEE;

MYSQL>ALTER TABLE EMPLOYEE ADD CONSTRAINT CHE_EMPLOYEE_ID CHECK(EMPLOYEE_ID=4);

MYSQL>DESC EMPLOYEE;

MYSQL>ALTER TABLE PWAGE ADD CONSTRAINT CHE_EMPLOYEE_ID CHECK(EMPLOYEE_ID=4);

MYSQL>DESC PWAGE;

MYSQL>ALTER TABLE ATTENDANCE ADD CONSTRAINT CHE_EMPLOYEE_ID CHECK(EMPLOYEE_ID=4);

MYSQL>DESC ATTENDANCE;

MYSQL>ALTER TABLE RP ADD CONSTRAINT CHE_EMPLOYEE_ID CHECK(EMPLOYEE_ID=4);

MYSQL>DESC RP;

如果有什么不足，请指正。顺便给点意见或建议。

本文转自 chen138 51CTO博客，原文链接：http://blog.51cto.com/chenboqiang/279044，如需转载请自行联系原作者