Create Tables and Indexes and Constraints

Create Tables and Indexes and Constraints.sql

if (object_id('Mobility_In_Queue') IS NOT NULL)
  drop table [Mobility_In_Queue]
GO

-- ***********************************************
-- new table Mobility_In_Queue that holds the
--   data/messages uploaded from the mobile devices
-- ***********************************************
IF NOT EXISTS (select 1 from sysobjects where name = 'Mobility_In_Queue' and [type] = 'U')
create table [dbo].[Mobility_In_Queue] (
	ID int identity(1,1) not null,
	MSG		varchar(max),					-- the RAW data from the mobile device
	DeviceID	varchar(30),					-- required for uniqueness of email alert when status reaches 2
	UserID		varchar(30),					-- used as part of the email for alert
	Status		int 		NOT NULL default 0,		-- 0 = New - no fails yet, 1 = failed once, 2 = failed twice, retry for x days, 3 = stop trying
	MsgTime		datetime	NOT NULL CONSTRAINT [DF_MobInQMsgTime]  DEFAULT (getdate())
									-- the time the message was inserted - used to know when to give up trying to process status 2's
)

GO

if not exists(select * from sysindexes where name = 'idx_mob_in_queue_StatusMsgTime')
	create index [idx_mob_in_queue_StatusMsgTime] on [Mobility_In_Queue] (Status, MsgTime)
GO




--drop table [Mobility_Out_Queue]
-- ***********************************************
-- sample with a DEFAULT constraint created with a name
-- ***********************************************
IF NOT EXISTS (select 1 from sysobjects where name = 'Mobility_Out_Queue' and [type] = 'U')
CREATE TABLE [dbo].[Mobility_Out_Queue](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[MSG] [varchar](max) NOT NULL,
	[UserID] [varchar](30) NOT NULL,

	[JobID] [int] NULL,
	[MsgID] [int] NOT NULL,
	[MsgTime] [datetime] NOT NULL CONSTRAINT [DF_MsgTime]  DEFAULT (getdate()),
	[ExpiryTime] [datetime] NULL,
	[MsgType] [char](5) NOT NULL,
 CONSTRAINT [PK_Mobility_Out_Queue] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


/*======================================================================================
Add a new field, set its default, create a default constraint, hook up the foreign key
======================================================================================*/

IF NOT EXISTS( select * from syscolumns sc where id in (select id from sysobjects where name like 'Address') and sc.name = 'Master_Site_ID' )
BEGIN
	ALTER TABLE Address add [Master_Site_ID] int
END
GO


Update [Address] set Master_Site_ID = 0 where Master_Site_ID IS NULL
GO

-- Address default constraint
	
if not exists ( select 1 from sysconstraints where constid in 
			(select so.ID from sysobjects so 
			 where so.name = 'df_Address_MasterSite' and so.type = 'D')
		  )
	ALTER TABLE [dbo].[ADDRESS] ADD CONSTRAINT [df_Address_MasterSite] DEFAULT ((0)) FOR [Master_Site_ID]
GO

-- Foreign Key
if not exists ( select 1 from sysconstraints where constid in 
			(select so.ID from sysobjects so 
			 where so.name = 'FK_Address_MasterSite' and so.type = 'F')
		  )
	ALTER TABLE [dbo].[ADDRESS] ADD CONSTRAINT FK_Address_MasterSite FOREIGN KEY (Master_Site_ID) REFERENCES [dbo].MasterAddressSite(ID)
GO