接下来完整数据库表的设计,对表设置主键,表与表的外键,唯一约束,查询约束。插入记录
1)对各个表创建主键约束;
<?xml:namespace prefix = o />
MYSQL>ALTER TABLE DEPARTMENT ADD CONTRAINT PRIMARY KEY(DEPARTMENT_NAME);
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
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;
如果有什么不足,请指正。顺便给点意见或建议。
转载于:https://blog.51cto.com/chenboqiang/279044