/* voucher */
create table voucher (
	voucher_id           key_t       not null,
	accession_number     char(10)    not null,
	voucher_number       varchar(8)  not null,
	h_w_code             char(1)     not null,
	vouchered_date       varchar(60) not null,
	voucher_early_date   julianday   not null,
	voucher_late_date    julianday   not null,
	vouchered_by         int         not null,
	garden_loc_id        int         not null,
	label_count          tinyint     not null,
	smasch_flag          tinyint     not null,
	flowering_flag       tinyint     not null,
	fruiting_flag        tinyint     not null,
	sterile_flag         tinyint     not null,
	entered_staff_id     int         not null,
	date_entered         datetime    not null,
	last_change_staff_id int         not null,
	last_change_date     datetime    not null
)
go

sp_primarykey voucher, voucher_id
go

sp_bindrule hort_wild_rule, 'voucher.h_w_code'
go

sp_bindrule boolean_rule, 'voucher.smasch_flag'
go

sp_bindrule boolean_rule, 'voucher.flowering_flag'
go

sp_bindrule boolean_rule, 'voucher.fruiting_flag'
go

sp_bindrule boolean_rule, 'voucher.sterile_flag'
go

sp_bindefault key_t_default, 'voucher.voucher_id'
go

sp_bindefault zero_default, 'voucher.vouchered_by'
go

sp_bindefault zero_default, 'voucher.garden_loc_id'
go

sp_bindefault zero_default, 'voucher.smasch_flag'
go

sp_bindefault zero_default, 'voucher.flowering_flag'
go

sp_bindefault zero_default, 'voucher.fruiting_flag'
go

sp_bindefault zero_default, 'voucher.sterile_flag'
go

sp_bindefault true_default, 'voucher.label_count'
go

sp_bindefault zero_default, 'voucher.entered_staff_id'
go

sp_bindefault zero_default, 'voucher.last_change_staff_id'
go

sp_bindefault date_default, 'voucher.date_entered'
go

sp_bindefault date_default, 'voucher.last_change_date'
go

sp_foreignkey voucher, accession, accession_number
go

sp_foreignkey voucher, voucher_number_code, voucher_number
go

sp_foreignkey voucher, person, vouchered_by
go

sp_foreignkey voucher, staff, entered_staff_id
go

sp_foreignkey voucher, staff, last_change_staff_id
go

sp_foreignkey voucher, garden_location, garden_loc_id
go

sp_foreignkey smasch_link, voucher, voucher_id
go

sp_foreignkey smasch_sage, voucher, voucher_id
go

sp_foreignkey voucher, voucher_label, voucher_id
go

/*insert key_sequence values('voucher', 0) */
/*go*/

create unique clustered index pk_voucher on voucher (voucher_id)
go

create index IF5voucher on voucher (
	accession_number, voucher_number, h_w_code, vouchered_date
)
go

create index IF1voucher on voucher (accession_number)
go

create index IF2voucher on voucher (vouchered_by)
go

create index IF3voucher on voucher (entered_staff_id)
go

create index IF4voucher on voucher (last_change_staff_id)
go

