/* T-SQL Script for creating the tables for DiversityTaxonNames */
/* on Microsoft-SQL-Server 2000 */

CREATE TABLE [dbo].[TaxonAcceptedName] (
	[Project] [nvarchar] (50) NOT NULL ,
	[NameID] [int] NOT NULL ,
	[RefID] [nvarchar] (20) NULL ,
	[RefText] [nvarchar] (255) NULL ,
	[RefDetail] [nvarchar] (255) NULL ,
	[TypistsNotes] [nvarchar] (255) NULL ,
	[IgnoreButKeepForReference] [bit] NULL ,
	[LogInsertedBy] [nvarchar] (50) NULL ,
	[LogInsertedWhen] [smalldatetime] NULL ,
	[LogUpdatedBy] [nvarchar] (50) NULL ,
	[LogUpdatedWhen] [smalldatetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TaxonHierarchy] (
	[Project] [nvarchar] (50) NOT NULL ,
	[NameID] [int] NOT NULL ,
	[NameParentID] [int] NULL ,
	[HierarchyRefID] [nvarchar] (20) NULL ,
	[HierarchyRefText] [nvarchar] (255) NULL ,
	[HierarchyRefDetail] [nvarchar] (255) NULL ,
	[HierarchyPositionIsUncertain] [bit] NULL ,
	[HierarchyListCache] [nvarchar] (1000) NULL ,
	[LogInsertedBy] [nvarchar] (50) NULL ,
	[LogInsertedWhen] [smalldatetime] NULL ,
	[LogUpdatedBy] [nvarchar] (50) NULL ,
	[LogUpdatedWhen] [smalldatetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TaxonName] (
	[NameID] [int] NOT NULL ,
	[TaxonNameCache] [nvarchar] (255) NULL ,
	[BasedOnNameID] [int] NULL ,
	[CreationType] [nvarchar] (50) NULL ,
	[TaxonomicRank] [nvarchar] (50) NULL ,
	[GenusOrSupragenericName] [nvarchar] (200) NULL ,
	[SpeciesGenusNameID] [int] NULL ,
	[InfragenericEpithet] [nvarchar] (200) NULL ,
	[SpeciesEpithet] [nvarchar] (255) NULL ,
	[InfraspecificEpithet] [nvarchar] (255) NULL ,
	[BasionymAuthors] [nvarchar] (255) NULL ,
	[CombiningAuthors] [nvarchar] (255) NULL ,
	[PublishingAuthors] [nvarchar] (255) NULL ,
	[SanctioningAuthor] [nvarchar] (255) NULL ,
	[IsHybrid] [bit] NULL ,
	[HybridNameID1] [int] NULL ,
	[HybridNameID2] [int] NULL ,
	[HybridNameID3] [int] NULL ,
	[HybridNameID4] [int] NULL ,
	[ReferenceTitle] [nvarchar] (255) NULL ,
	[ReferenceID] [int] NULL ,
	[Volume] [nvarchar] (20) NULL ,
	[Issue] [nvarchar] (255) NULL ,
	[Page] [nvarchar] (10) NULL ,
	[YearOfPubl] [smallint] NULL ,
	[YearOnPubl] [smallint] NULL ,
	[IndexingReference] [nvarchar] (255) NULL ,
	[OriginalOrthography] [nvarchar] (255) NULL ,
	[NomenclaturalCode] [tinyint] NULL ,
	[NomenclaturalStatus] [nvarchar] (50) NULL ,
	[NomenclaturalComment] [nvarchar] (1000) NULL ,
	[Typification] [nvarchar] (50) NULL ,
	[TypificationDetails] [nvarchar] (255) NULL ,
	[TypeSpecimenSubstrate] [nvarchar] (255) NULL ,
	[TypeSpecimenLocality] [nvarchar] (255) NULL ,
	[AnamorphTeleomorph] [nvarchar] (255) NULL ,
	[TypistNotes] [nvarchar] (200) NULL ,
	[IgnoreButKeepForReference] [bit] NULL ,
	[LogInsertedBy] [nvarchar] (50) NULL ,
	[LogInsertedWhen] [smalldatetime] NULL ,
	[LogUpdatedBy] [nvarchar] (50) NULL ,
	[LogUpdatedWhen] [smalldatetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TaxonNameCreationType_Enum] (
	[Code] [nvarchar] (50) NOT NULL ,
	[Description] [nvarchar] (800) NULL ,
	[DisplayText] [nvarchar] (50) NULL ,
	[DisplayOrder] [smallint] NULL ,
	[DisplayEnable] [bit] NULL ,
	[InternalNotes] [nvarchar] (500) NULL ,
	[ParentCode] [nvarchar] (50) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TaxonNameExternalDatabase] (
	[ExternalDatabaseID] [int] IDENTITY (1, 1) NOT NULL ,
	[ExternalDatabaseName] [nvarchar] (100) NULL ,
	[ExternalDatabaseVersion] [nvarchar] (255) NULL ,
	[Rights] [nvarchar] (500) NULL ,
	[ExternalDatabaseAuthors] [nvarchar] (200) NULL ,
	[ExternalDatabaseURI] [nvarchar] (300) NULL ,
	[ExternalDatabaseInstitution] [nvarchar] (300) NULL ,
	[InternalNotes] [nvarchar] (1500) NULL ,
	[ExternalAttribute_NameID] [nvarchar] (255) NULL ,
	[PreferredSequence] [tinyint] NULL ,
	[Disabled] [bit] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TaxonNameExternalID] (
	[NameID] [int] NOT NULL ,
	[ExternalDatabaseID] [int] NOT NULL ,
	[ExternalNameID] [nvarchar] (50) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TaxonNameNomenclaturalCode_Enum] (
	[Code] [tinyint] NOT NULL ,
	[Description] [nvarchar] (500) NULL ,
	[DisplayText] [nvarchar] (50) NULL ,
	[DisplayOrder] [smallint] NULL ,
	[DisplayEnable] [bit] NULL ,
	[InternalNotes] [nvarchar] (500) NULL ,
	[ParentCode] [nvarchar] (50) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TaxonNameNomenclaturalStatus_Enum] (
	[Code] [nvarchar] (50) NOT NULL ,
	[Description] [nvarchar] (500) NULL ,
	[DisplayText] [nvarchar] (50) NULL ,
	[DisplayOrder] [smallint] NULL ,
	[DisplayEnable] [bit] NULL ,
	[InternalNotes] [nvarchar] (500) NULL ,
	[ParentCode] [nvarchar] (50) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TaxonNameSynonymisationType_Enum] (
	[Code] [nvarchar] (50) NOT NULL ,
	[Description] [nvarchar] (500) NULL ,
	[DisplayText] [nvarchar] (50) NULL ,
	[DisplayOrder] [smallint] NULL ,
	[DisplayEnable] [bit] NULL ,
	[InternalNotes] [nvarchar] (500) NULL ,
	[ParentCode] [nvarchar] (50) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TaxonNameTaxonomicRank_Enum] (
	[Code] [nvarchar] (50) NOT NULL ,
	[Description] [nvarchar] (500) NULL ,
	[DisplayText] [nvarchar] (50) NULL ,
	[DisplayOrder] [smallint] NULL ,
	[DisplayEnable] [bit] NULL ,
	[InternalNotes] [nvarchar] (500) NULL ,
	[ParentCode] [nvarchar] (50) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TaxonSynonymy] (
	[Project] [nvarchar] (50) NOT NULL ,
	[NameID] [int] NOT NULL ,
	[SynNameID] [int] NOT NULL ,
	[ConceptSuffix] [nvarchar] (200) NOT NULL ,
	[ConceptNotes] [nvarchar] (500) NULL ,
	[SynRefID] [nvarchar] (20) NULL ,
	[SynRefText] [nvarchar] (255) NULL ,
	[SynRefDetail] [nvarchar] (255) NULL ,
	[SynTypistsNotes] [nvarchar] (255) NULL ,
	[SynType] [nvarchar] (50) NULL ,
	[SynIsUncertain] [bit] NULL ,
	[IgnoreButKeepForReference] [bit] NULL ,
	[LogInsertedBy] [nvarchar] (50) NULL ,
	[LogInsertedWhen] [smalldatetime] NULL ,
	[LogUpdatedBy] [nvarchar] (50) NULL ,
	[LogUpdatedWhen] [smalldatetime] NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TaxonAcceptedName] ADD 
	CONSTRAINT [DF_TaxonAcceptedName_Project] DEFAULT (N'DiversityTaxonNames') FOR [Project],
	CONSTRAINT [DF_TaxonAcceptedName_IgnoreButKeepForReference] DEFAULT (0) FOR [IgnoreButKeepForReference],
	CONSTRAINT [DF_TaxonAcceptedName_LogInsertedBy] DEFAULT (user_name()) FOR [LogInsertedBy],
	CONSTRAINT [DF_TaxonAcceptedName_LogInsertedWhen] DEFAULT (getdate()) FOR [LogInsertedWhen],
	CONSTRAINT [DF_TaxonAcceptedName_LogUpdatedBy] DEFAULT (user_name()) FOR [LogUpdatedBy],
	CONSTRAINT [DF_TaxonAcceptedName_LogUpdatedWhen] DEFAULT (getdate()) FOR [LogUpdatedWhen],
	CONSTRAINT [PK_FungusAcceptedName] PRIMARY KEY  CLUSTERED 
	(
		[Project],
		[NameID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[TaxonHierarchy] ADD 
	CONSTRAINT [DF_TaxonHierarchy_Project] DEFAULT (N'DiversityTaxonNames') FOR [Project],
	CONSTRAINT [DF_TaxonHierarchy_HierarchyPositionUncertain_1] DEFAULT (0) FOR [HierarchyPositionIsUncertain],
	CONSTRAINT [DF_TaxonHierarchy_LogInsertedBy] DEFAULT (user_name()) FOR [LogInsertedBy],
	CONSTRAINT [DF_TaxonHierarchy_LogInsertedWhen] DEFAULT (getdate()) FOR [LogInsertedWhen],
	CONSTRAINT [DF_TaxonHierarchy_LogUpdatedBy] DEFAULT (user_name()) FOR [LogUpdatedBy],
	CONSTRAINT [DF_TaxonHierarchy_LogUpdatedWhen] DEFAULT (getdate()) FOR [LogUpdatedWhen],
	CONSTRAINT [PK_FungusHierarchy] PRIMARY KEY  CLUSTERED 
	(
		[Project],
		[NameID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[TaxonName] ADD 
	CONSTRAINT [DF_TaxonName_IsHybrid] DEFAULT (0) FOR [IsHybrid],
	CONSTRAINT [DF_TaxonName_IgnoreButKeepForReference] DEFAULT (0) FOR [IgnoreButKeepForReference],
	CONSTRAINT [DF_TaxonName_LogInsertedBy] DEFAULT (user_name()) FOR [LogInsertedBy],
	CONSTRAINT [DF_TaxonName_LogInsertedWhen] DEFAULT (getdate()) FOR [LogInsertedWhen],
	CONSTRAINT [DF_TaxonName_LogUpdatedBy] DEFAULT (user_name()) FOR [LogUpdatedBy],
	CONSTRAINT [DF_TaxonName_LogUpdatedWhen] DEFAULT (getdate()) FOR [LogUpdatedWhen],
	CONSTRAINT [PK_FungusName] PRIMARY KEY  CLUSTERED 
	(
		[NameID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[TaxonNameCreationType_Enum] ADD 
	CONSTRAINT [PK_TaxonNameCreationType_Enum] PRIMARY KEY  CLUSTERED 
	(
		[Code]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[TaxonNameExternalDatabase] ADD 
	CONSTRAINT [PK_FungusNameExternalDatabase] PRIMARY KEY  CLUSTERED 
	(
		[ExternalDatabaseID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[TaxonNameExternalID] ADD 
	CONSTRAINT [PK_FungusNameExternalID] PRIMARY KEY  CLUSTERED 
	(
		[NameID],
		[ExternalDatabaseID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[TaxonNameNomenclaturalCode_Enum] ADD 
	CONSTRAINT [PK_TaxonNameNomenclaturalCode_Enum] PRIMARY KEY  CLUSTERED 
	(
		[Code]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[TaxonNameNomenclaturalStatus_Enum] ADD 
	CONSTRAINT [PK_TaxonNameNomenclaturalStatus_Enum] PRIMARY KEY  CLUSTERED 
	(
		[Code]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[TaxonNameSynonymisationType_Enum] ADD 
	CONSTRAINT [PK_TaxonNameSynonymisationType_Enum] PRIMARY KEY  CLUSTERED 
	(
		[Code]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[TaxonNameTaxonomicRank_Enum] ADD 
	CONSTRAINT [PK_TaxonNameTaxonomicRank_Enum] PRIMARY KEY  CLUSTERED 
	(
		[Code]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[TaxonSynonymy] ADD 
	CONSTRAINT [DF_TaxonSynonymy_Project] DEFAULT (N'DiversityTaxonNames') FOR [Project],
	CONSTRAINT [DF_TaxonSynonymy_ConceptSuffix] DEFAULT ('') FOR [ConceptSuffix],
	CONSTRAINT [DF_TaxonSynonymy_SynType] DEFAULT ('unknown') FOR [SynType],
	CONSTRAINT [DF_TaxonSynonymy_SynIsUncertain] DEFAULT (0) FOR [SynIsUncertain],
	CONSTRAINT [DF_TaxonSynonymy_IgnoreButKeepForReference] DEFAULT (0) FOR [IgnoreButKeepForReference],
	CONSTRAINT [DF_TaxonSynonymy_LogInsertedBy] DEFAULT (user_name()) FOR [LogInsertedBy],
	CONSTRAINT [DF_TaxonSynonymy_LogInsertedWhen] DEFAULT (getdate()) FOR [LogInsertedWhen],
	CONSTRAINT [DF_TaxonSynonymy_LogUpdatedBy] DEFAULT (user_name()) FOR [LogUpdatedBy],
	CONSTRAINT [DF_TaxonSynonymy_LogUpdatedWhen] DEFAULT (getdate()) FOR [LogUpdatedWhen],
	CONSTRAINT [PK_FungusSynonymy] PRIMARY KEY  CLUSTERED 
	(
		[Project],
		[NameID],
		[SynNameID],
		[ConceptSuffix]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[TaxonAcceptedName] ADD 
	CONSTRAINT [FK_FungusAcceptedName_FungusName] FOREIGN KEY 
	(
		[NameID]
	) REFERENCES [dbo].[TaxonName] (
		[NameID]
	) ON DELETE CASCADE  ON UPDATE CASCADE 
GO

ALTER TABLE [dbo].[TaxonHierarchy] ADD 
	CONSTRAINT [FK_FungusHierarchy_FungusName] FOREIGN KEY 
	(
		[NameID]
	) REFERENCES [dbo].[TaxonName] (
		[NameID]
	) ON DELETE CASCADE  ON UPDATE CASCADE 
GO

ALTER TABLE [dbo].[TaxonName] ADD 
	CONSTRAINT [FK_FungusName_TaxonNameCreationType_Enum] FOREIGN KEY 
	(
		[CreationType]
	) REFERENCES [dbo].[TaxonNameCreationType_Enum] (
		[Code]
	) ON UPDATE CASCADE ,
	CONSTRAINT [FK_TaxonName_TaxonName] FOREIGN KEY 
	(
		[BasedOnNameID]
	) REFERENCES [dbo].[TaxonName] (
		[NameID]
	),
	CONSTRAINT [FK_TaxonName_TaxonName1] FOREIGN KEY 
	(
		[HybridNameID1]
	) REFERENCES [dbo].[TaxonName] (
		[NameID]
	),
	CONSTRAINT [FK_TaxonName_TaxonName2] FOREIGN KEY 
	(
		[HybridNameID2]
	) REFERENCES [dbo].[TaxonName] (
		[NameID]
	),
	CONSTRAINT [FK_TaxonName_TaxonName3] FOREIGN KEY 
	(
		[HybridNameID3]
	) REFERENCES [dbo].[TaxonName] (
		[NameID]
	),
	CONSTRAINT [FK_TaxonName_TaxonName4] FOREIGN KEY 
	(
		[HybridNameID4]
	) REFERENCES [dbo].[TaxonName] (
		[NameID]
	),
	CONSTRAINT [FK_TaxonName_TaxonName5] FOREIGN KEY 
	(
		[BasedOnNameID]
	) REFERENCES [dbo].[TaxonName] (
		[NameID]
	),
	CONSTRAINT [FK_TaxonName_TaxonNameNomenclaturalCode_Enum] FOREIGN KEY 
	(
		[NomenclaturalCode]
	) REFERENCES [dbo].[TaxonNameNomenclaturalCode_Enum] (
		[Code]
	) ON UPDATE CASCADE ,
	CONSTRAINT [FK_TaxonName_TaxonNameNomenclaturalStatus_Enum] FOREIGN KEY 
	(
		[NomenclaturalStatus]
	) REFERENCES [dbo].[TaxonNameNomenclaturalStatus_Enum] (
		[Code]
	) ON UPDATE CASCADE ,
	CONSTRAINT [FK_TaxonName_TaxonNameTaxonomicRank_Enum] FOREIGN KEY 
	(
		[TaxonomicRank]
	) REFERENCES [dbo].[TaxonNameTaxonomicRank_Enum] (
		[Code]
	) ON UPDATE CASCADE 
GO

ALTER TABLE [dbo].[TaxonNameExternalID] ADD 
	CONSTRAINT [FK_FungusNameExternalID_FungusName] FOREIGN KEY 
	(
		[NameID]
	) REFERENCES [dbo].[TaxonName] (
		[NameID]
	) ON DELETE CASCADE  ON UPDATE CASCADE ,
	CONSTRAINT [FK_FungusNameExternalID_FungusNameExternalDatabase] FOREIGN KEY 
	(
		[ExternalDatabaseID]
	) REFERENCES [dbo].[TaxonNameExternalDatabase] (
		[ExternalDatabaseID]
	) ON UPDATE CASCADE 
GO

ALTER TABLE [dbo].[TaxonSynonymy] ADD 
	CONSTRAINT [FK_FungusSynonymy_FungusName] FOREIGN KEY 
	(
		[NameID]
	) REFERENCES [dbo].[TaxonName] (
		[NameID]
	) ON DELETE CASCADE  ON UPDATE CASCADE ,
	CONSTRAINT [FK_TaxonSynonymy_TaxonName] FOREIGN KEY 
	(
		[SynNameID]
	) REFERENCES [dbo].[TaxonName] (
		[NameID]
	),
	CONSTRAINT [FK_TaxonSynonymy_TaxonNameSynonymisationType_Enum] FOREIGN KEY 
	(
		[SynType]
	) REFERENCES [dbo].[TaxonNameSynonymisationType_Enum] (
		[Code]
	) ON UPDATE CASCADE 
GO