/* lexicon */
CREATE TABLE lexicon (
	lexicon_id              key_t NOT NULL,
	term                    varchar(220) NOT NULL,
	node_type               varchar(10) NULL,
	differentiator          varchar(40) NULL,
	access_code             int NOT NULL,
	valid_catalog_term_flag bit,
	agent_id                int NOT NULL,
	date_entered            datetime NOT NULL,
	last_change_agent_id    int NOT NULL,
	last_change_date        datetime NOT NULL
)
go

CREATE UNIQUE CLUSTERED INDEX pk_lexicon ON lexicon (lexicon_id, term, node_type)
go

CREATE UNIQUE INDEX nc1_lexicon ON lexicon (term, lexicon_id, node_type)
go

CREATE UNIQUE INDEX lexicon_unc ON lexicon (lexicon_id)
go

CREATE INDEX if39_lexicon ON lexicon (node_type)
go

exec sp_primarykey lexicon, lexicon_id
go

exec sp_bindefault true_default, 'lexicon.valid_catalog_term_flag'
go

exec sp_bindefault zero_default, 'lexicon.access_code'
go

sp_bindefault zero_default, 'lexicon.agent_id'
go

sp_bindefault date_default, 'lexicon.date_entered'
go

sp_bindefault zero_default, 'lexicon.last_change_agent_id'
go

sp_bindefault date_default, 'lexicon.last_change_date'
go

sp_bindrule access_code_rule, 'lexicon.access_code'
go

exec sp_foreignkey lexicon_term_relation, lexicon, related_lexicon_id
go

exec sp_foreignkey lexicon_term_relation, lexicon, lexicon_id
go

exec sp_foreignkey plant_identity, lexicon, lexicon_id
go

exec sp_foreignkey lexicon_sort_order, lexicon, lexicon_id
go

exec sp_foreignkey scope_notes, lexicon, lexicon_id
go

exec sp_foreignkey lexicon_term_tokens, lexicon, lexicon_id
go

