/* plant_location */
create table plant_location (
	accession_number        char(10)        not null,
	garden_loc_id           int             not null,
	action_date             datetime        not null,
	action_code             int             not null,
	label_req_flag          int             not null,
	comments                varchar(255)    null,
	label_size              char(5)         null,
	stand_type              char(5)         null,
	label_count             tinyint         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

create unique clustered index XPKplant_location on plant_location (
	accession_number, garden_loc_id, action_date, action_code
)
go

create index IF1plant_location on plant_location (entered_staff_id)
go

create index IF2plant_location on plant_location (last_change_staff_id)
go

create index IF3plant_location on plant_location (accession_number)
go

create index IF4plant_location on plant_location (garden_loc_id)
go

sp_primarykey plant_location, 
	accession_number,
	garden_loc_id,
	action_date,
	action_code
go

sp_bindrule action_code_rule, 'plant_location.action_code'
go

sp_bindrule label_size_rule, 'plant_location.label_size'
go

sp_bindrule stand_type_rule, 'plant_location.stand_type'
go

sp_bindefault zero_default, 'plant_location.entered_staff_id'
go

sp_bindefault zero_default, 'plant_location.last_change_staff_id'
go

sp_bindefault date_default, 'plant_location.date_entered'
go

sp_bindefault date_default, 'plant_location.last_change_date'
go

sp_bindefault zero_default, 'plant_location.label_req_flag'
go

sp_bindefault zero_default, 'plant_location.label_count'
go

sp_bindefault true_default, 'plant_location.garden_loc_id'
go

sp_foreignkey plant_location, garden_location, garden_loc_id
go

sp_foreignkey plant_location, accession, accession_number
go

sp_foreignkey plant_location, staff, entered_staff_id
go

sp_foreignkey plant_location, staff, last_change_staff_id
go
		

