接下来完整数据库表的设计，对表设置主键，表与表的外键，唯一约束，查询约束。插入记录
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，如需转载请自行联系原作者
