Oracle操作练习一建表

CREATE TABLE work_basic_table
(institution_id CHAR(6) NOT NULL,
institution_name CHAR(10) NOT NULL,
workplace_id CHAR(20) NOT NULL,
workplace_name CHAR(100) NOT NULL,
workplace_shortboard CHAR(20) NOT NULL,
channel CHAR(10) NOT NULL)

--要求 workplace_id，workplace_shortboard 不能存在相同的记录，即创建联合索引
EXEC sp_helpindex work_basic_table; 
--ALTER TABLE work_basic_table WITH NOCHECK ADD
--CONSTRAINT [PK_A] PRIMARY KEY NONCLUSTERED 
--(workplace_id,
--workplace_shortboard); 
SELECT * FROM work_basic_table;


--为now_work_day 字段设置默认值，并且要求该字段的值在1-14之间
--设置record_time 字段为日期时间格式，并且默认值为插入时时间
CREATE TABLE calendar_table
(id INT PRIMARY KEY NOT NULL,
workplace_id CHAR(20) NOT NULL,
work_date DATETIME NOT NULL,
now_work_day INT NOT NULL CHECK(now_work_day BETWEEN 1 AND 14),
item1 INT NOT NULL CHECK(item1=0 OR item1=1) DEFAULT 0,
item1_img TEXT NULL,
item2 INT NOT NULL CHECK(item2=0 OR item2=1) DEFAULT 0,
item2_img TEXT NULL,
item3 INT NOT NULL CHECK(item3=0 OR item3=1) DEFAULT 0,
item3_img TEXT NULL,
record_id CHAR(20) NOT NULL,
record_time datetime NULL DEFAULT (getdate()))

SELECT * FROM calendar_table;
--CREATE INDEX workplace_id ON calendar_table(workplace_id);
EXEC sp_helpindex calendar_table; 


CREATE TABLE details_work
(now_work_day INT NOT NULL CHECK(now_work_day BETWEEN 1 AND 14),
now_item INT NOT NULL,
title_item CHAR(100) NOT NULL,
description_item TEXT NOT NULL)

SELECT * FROM details_work;


CREATE TABLE leaders_table
(workplace_id CHAR(20) NOT NULL,
leaderd_id CHAR(8) NOT NULL,
record_id CHAR(20) NOT NULL,
record_time datetime NULL DEFAULT (getdate()))
SELECT * FROM leaders_table;


CREATE TABLE members_table
(workplace_id CHAR(20) NOT NULL,
now_work_day INT NOT NULL CHECK(now_work_day BETWEEN 1 AND 14),
member_id CHAR(20) NOT NULL,
record_id CHAR(20) NOT NULL,
record_time datetime NULL DEFAULT (getdate()))
SELECT * FROM members_table;

ALTER TABLE members_table WITH NOCHECK ADD 
CONSTRAINT [PK_B] PRIMARY KEY NONCLUSTERED 
(workplace_id,
now_work_day,
member_id
); 
EXEC sp_helpindex members_table;