【SQL开源代码栏目提醒】:以下是网学会员为您推荐的SQL开源代码-Cmmemo.sql,希望本篇文章对您学习有所帮助。
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'company_member_tab')
DROP TABLE company_member_tab
--创建企业成员基本信息表
CREATE TABLE company_member_tab(
member_id INT IDENTITY(1,1) PRIMARY KEY ,
member_name VARCHAR(24) NOT NULL,
member_position VARCHAR(10),
birth_date DATETIME NOT NULL,
member_gender VARCHAR(5),
member_hometown VARCHAR(20)
);
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'memo_type_tab')
DROP TABLE memo_type_tab
--创建备忘录类型信息表
CREATE TABLE memo_type_tab(
type_id INT PRIMARY KEY ,
type_name VARCHAR(24) NOT NULL
);
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'memo_info_tab')
DROP TABLE memo_info_tab
--创建备忘录类型信息表
CREATE TABLE memo_info_tab(
memo_id INT NOT NULL PRIMARY KEY ,
member_id INT NOT NULL,
oper_date DATETIME NOT NULL,
happen_date DATETIME NOT NULL,
is_bigevent INT NOT NULL
CHECK (is_bigevent IN (0, 1)),
event_cost VARCHAR(20),
type_id INT NOT NULL,
memo_text VARCHAR(1000) NULL,
FOREIGN KEY (member_id) REFERENCES company_member_tab ,
FOREIGN KEY (type_id) REFERENCES memo_type_tab
);