memo_createtable.sql

来自「VC6.0编写的SQL Server2000应用的企业备忘录」· SQL 代码 · 共 44 行

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 
);