USE [AIS_S3]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Vendors]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Vendors](
	[VendorNo] [int] IDENTITY(1,1) NOT NULL,
	[Active] [bit] NOT NULL CONSTRAINT [DF_Vendors_Active]  DEFAULT ((1)),
	[Name] [varchar](50) NOT NULL,
	[Phone] [varchar](50) NULL,
	[LastModified] [datetime] NOT NULL,
 CONSTRAINT [PK_Vendors] PRIMARY KEY CLUSTERED 
(
	[VendorNo] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Vendors]') AND name = N'UX_Name')
CREATE UNIQUE NONCLUSTERED INDEX [UX_Name] ON [dbo].[Vendors] 
(
	[Name] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Cards]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Cards](
	[CardNo] [int] IDENTITY(1,1) NOT NULL,
	[Active] [bit] NOT NULL CONSTRAINT [DF_Cards_Active]  DEFAULT ((1)),
	[Id] [varchar](50) NOT NULL,
	[PIN] [varchar](50) NULL,
	[LastModified] [datetime] NOT NULL,
 CONSTRAINT [PK_Cards] PRIMARY KEY CLUSTERED 
(
	[CardNo] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Cards]') AND name = N'UX_Id')
CREATE UNIQUE NONCLUSTERED INDEX [UX_Id] ON [dbo].[Cards] 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[EmailAddresses]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[EmailAddresses](
	[EmailAddressNo] [int] IDENTITY(1,1) NOT NULL,
	[Address] [varchar](50) NOT NULL,
	[LastModified] [datetime] NOT NULL,
 CONSTRAINT [PK_EmailAddresses] PRIMARY KEY CLUSTERED 
(
	[EmailAddressNo] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[EmailAddresses]') AND name = N'UX_Address')
CREATE UNIQUE NONCLUSTERED INDEX [UX_Address] ON [dbo].[EmailAddresses] 
(
	[Address] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Attributes]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Attributes](
	[AttributeNo] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](50) NOT NULL,
	[IsEditable] [bit] NOT NULL CONSTRAINT [DF_AttributeTypes_IsEditable]  DEFAULT ((1)),
	[LastModified] [datetime] NOT NULL,
 CONSTRAINT [PK_AttributeTypes] PRIMARY KEY CLUSTERED 
(
	[AttributeNo] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Attributes]') AND name = N'UX_Name')
CREATE UNIQUE NONCLUSTERED INDEX [UX_Name] ON [dbo].[Attributes] 
(
	[Name] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Products]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Products](
	[ProductNo] [int] IDENTITY(1,1) NOT NULL,
	[Active] [bit] NOT NULL CONSTRAINT [DF_Products_Active]  DEFAULT ((1)),
	[IsEditable] [bit] NOT NULL CONSTRAINT [DF_Products_IsEditable]  DEFAULT ((1)),
	[PartNum] [varchar](50) NOT NULL,
	[Description] [varchar](250) NOT NULL,
	[VendorNo] [int] NULL,
	[PackageQty] [int] NOT NULL CONSTRAINT [DF_Products_PackagingQuantity]  DEFAULT ((1)),
	[Price] [money] NOT NULL CONSTRAINT [DF_Products_Price]  DEFAULT ((0)),
	[CurrentQty] [int] NOT NULL CONSTRAINT [DF_Products_CurrentQty]  DEFAULT ((0)),
	[MinQty] [int] NOT NULL CONSTRAINT [DF_Products_MinQty]  DEFAULT ((0)),
	[OrderQty] [int] NOT NULL CONSTRAINT [DF_Products_OrderQty]  DEFAULT ((0)),
	[LastModified] [datetime] NOT NULL,
 CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED 
(
	[ProductNo] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Products]') AND name = N'UX_PartNum')
CREATE UNIQUE NONCLUSTERED INDEX [UX_PartNum] ON [dbo].[Products] 
(
	[PartNum] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Users]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Users](
	[UserNo] [int] IDENTITY(1,1) NOT NULL,
	[Active] [bit] NOT NULL CONSTRAINT [DF_Users_Active]  DEFAULT ((1)),
	[Name] [varchar](50) NOT NULL,
	[CardNo] [int] NOT NULL,
	[Id] [varchar](50) NULL,
	[DeptNo] [int] NULL,
	[LastModified] [datetime] NOT NULL,
 CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED 
(
	[UserNo] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Users]') AND name = N'UX_Name_CardNo')
CREATE UNIQUE NONCLUSTERED INDEX [UX_Name_CardNo] ON [dbo].[Users] 
(
	[Name] ASC,
	[CardNo] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MachineCards]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[MachineCards](
	[MachineCardNo] [int] IDENTITY(1,1) NOT NULL,
	[MachineNo] [int] NOT NULL,
	[CardNo] [int] NOT NULL,
	[LastModified] [datetime] NOT NULL,
 CONSTRAINT [PK_MachineCards] PRIMARY KEY CLUSTERED 
(
	[MachineCardNo] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[MachineCards]') AND name = N'UX_MachineNo_CardNo')
CREATE UNIQUE NONCLUSTERED INDEX [UX_MachineNo_CardNo] ON [dbo].[MachineCards] 
(
	[MachineNo] ASC,
	[CardNo] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Transactions]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Transactions](
	[TransactionNo] [int] IDENTITY(1,1) NOT NULL,
	[TransactionDT] [datetime] NOT NULL,
	[MachineNo] [int] NOT NULL,
	[CardNo] [int] NOT NULL,
	[Bin] [int] NOT NULL,
	[ProductNo] [int] NOT NULL,
	[Quantity] [int] NOT NULL,
	[LastModified] [datetime] NOT NULL,
 CONSTRAINT [PK_Transactions] PRIMARY KEY CLUSTERED 
(
	[TransactionNo] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TransactionJobs]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[TransactionJobs](
	[TransactionJobNo] [int] IDENTITY(1,1) NOT NULL,
	[TransactionNo] [int] NOT NULL,
	[JobNo] [int] NOT NULL,
	[LastModified] [datetime] NOT NULL,
 CONSTRAINT [PK_TransactionJobs] PRIMARY KEY CLUSTERED 
(
	[TransactionJobNo] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ReportEmails]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ReportEmails](
	[ReportEmailNo] [int] IDENTITY(1,1) NOT NULL,
	[ReportNo] [int] NOT NULL,
	[EmailAddressNo] [int] NOT NULL,
	[LastModified] [datetime] NOT NULL,
 CONSTRAINT [PK_ReportEmails] PRIMARY KEY CLUSTERED 
(
	[ReportEmailNo] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ReportEmails]') AND name = N'UX_ReportNo_EmailAddressNo')
CREATE UNIQUE NONCLUSTERED INDEX [UX_ReportNo_EmailAddressNo] ON [dbo].[ReportEmails] 
(
	[ReportNo] ASC,
	[EmailAddressNo] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MachineBins]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[MachineBins](
	[MachineBinNo] [int] IDENTITY(1,1) NOT NULL,
	[MachineNo] [int] NOT NULL,
	[Bin] [int] NOT NULL,
	[ProductNo] [int] NOT NULL,
	[Capacity] [int] NOT NULL CONSTRAINT [DF_MachineBins_Capacity]  DEFAULT ((0)),
	[CurrentQty] [int] NOT NULL CONSTRAINT [DF_MachineBins_CurrentQty]  DEFAULT ((0)),
	[MinQty] [int] NOT NULL CONSTRAINT [DF_MachineBins_MinQty]  DEFAULT ((0)),
	[LastModified] [datetime] NOT NULL,
 CONSTRAINT [PK_MachineBins] PRIMARY KEY CLUSTERED 
(
	[MachineBinNo] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[MachineBins]') AND name = N'UX_MachineNo_Bin')
CREATE UNIQUE NONCLUSTERED INDEX [UX_MachineNo_Bin] ON [dbo].[MachineBins] 
(
	[MachineNo] ASC,
	[Bin] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MachineSettings]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[MachineSettings](
	[MachineSettingNo] [int] IDENTITY(1,1) NOT NULL,
	[MachineNo] [int] NOT NULL,
	[AttributeValueNo] [int] NOT NULL,
	[Value] [varchar](50) NULL,
	[LastModified] [datetime] NOT NULL,
 CONSTRAINT [PK_MachineSettings] PRIMARY KEY CLUSTERED 
(
	[MachineSettingNo] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[MachineSettings]') AND name = N'UX_MachineNo_AttributeValueNo')
CREATE UNIQUE NONCLUSTERED INDEX [UX_MachineNo_AttributeValueNo] ON [dbo].[MachineSettings] 
(
	[MachineNo] ASC,
	[AttributeValueNo] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Reports]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Reports](
	[ReportNo] [int] IDENTITY(1,1) NOT NULL,
	[AutoEnable] [bit] NOT NULL CONSTRAINT [DF_Reports_AutoEnable]  DEFAULT ((0)),
	[Name] [varchar](50) NOT NULL,
	[attachmentCSV] [bit] NOT NULL DEFAULT ((0)),
	[attachmentXLS] [bit] NOT NULL DEFAULT ((0)),
	[FrequencyNo] [int] NULL,
	[NextRunDT] [datetime] NULL,
	[LastModified] [datetime] NOT NULL,
 CONSTRAINT [PK_Reports] PRIMARY KEY CLUSTERED 
(
	[ReportNo] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Reports]') AND name = N'UX_Name')
CREATE UNIQUE NONCLUSTERED INDEX [UX_Name] ON [dbo].[Reports] 
(
	[Name] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Security]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Security](
	[SecurityNo] [int] IDENTITY(1,1) NOT NULL,
	[Active] [bit] NOT NULL CONSTRAINT [DF_Security_Active]  DEFAULT ((1)),
	[RoleNo] [int] NOT NULL,
	[Account] [varchar](50) NOT NULL,
	[Password] [varchar](50) NOT NULL,
	[FirstName] [varchar](50) NULL,
	[LastName] [varchar](50) NULL,
	[LastModified] [datetime] NOT NULL,
 CONSTRAINT [PK_Security] PRIMARY KEY CLUSTERED 
(
	[SecurityNo] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Security]') AND name = N'UX_Account')
CREATE UNIQUE NONCLUSTERED INDEX [UX_Account] ON [dbo].[Security] 
(
	[Account] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Machines]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Machines](
	[MachineNo] [int] IDENTITY(1,1) NOT NULL,
	[Active] [bit] NOT NULL CONSTRAINT [DF_Machines_Active]  DEFAULT ((1)),
	[Id] [varchar](50) NOT NULL,
	[IP] [varchar](50) NOT NULL CONSTRAINT [DF_Machines_IP]  DEFAULT ('127.0.0.1'),
	[Port] [int] NOT NULL CONSTRAINT [DF_Machines_Port]  DEFAULT ((1001)),
	[LocationNo] [int] NULL,
	[StatusNo] [int] NULL,
	[Firmware] [varchar](50) NULL,
	[LastModified] [datetime] NOT NULL,
 CONSTRAINT [PK_Machines] PRIMARY KEY CLUSTERED 
(
	[MachineNo] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Machines]') AND name = N'UX_IP')
CREATE UNIQUE NONCLUSTERED INDEX [UX_IP] ON [dbo].[Machines] 
(
	[IP] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Jobs]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Jobs](
	[JobNo] [int] IDENTITY(1,1) NOT NULL,
	[Active] [bit] NOT NULL CONSTRAINT [DF_Jobs_Active]  DEFAULT ((1)),
	[LocationNo] [int] NULL,
	[Job] [varchar](50) NOT NULL,
	[Description] [varchar](50) NULL,
	[LastModified] [datetime] NOT NULL,
 CONSTRAINT [PK_Jobs] PRIMARY KEY CLUSTERED 
(
	[JobNo] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Jobs]') AND name = N'UX_Location_Job')
CREATE UNIQUE NONCLUSTERED INDEX [UX_Location_Job] ON [dbo].[Jobs] 
(
	[LocationNo] ASC,
	[Job] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AttributeValues]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[AttributeValues](
	[AttributeValueNo] [int] IDENTITY(1,1) NOT NULL,
	[AttributeNo] [int] NOT NULL,
	[Value] [varchar](50) NOT NULL,
	[ValueType] [varchar](50) NOT NULL CONSTRAINT [DF_AttributeValues_ValueType]  DEFAULT ('string'),
	[ParentAttributeValueNo] [int] NULL,
	[ParentEligible] [bit] NOT NULL CONSTRAINT [DF_Attributes_ParentEligible]  DEFAULT ((0)),
	[LastModified] [datetime] NOT NULL,
 CONSTRAINT [PK_AttributeValues] PRIMARY KEY CLUSTERED 
(
	[AttributeValueNo] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[AttributeValues]') AND name = N'UX_AttributeNo_Value')
CREATE UNIQUE NONCLUSTERED INDEX [UX_AttributeNo_Value] ON [dbo].[AttributeValues] 
(
	[AttributeNo] ASC,
	[Value] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Vendors_Products]') AND parent_object_id = OBJECT_ID(N'[dbo].[Products]'))
ALTER TABLE [dbo].[Products]  WITH CHECK ADD  CONSTRAINT [FK_Vendors_Products] FOREIGN KEY([VendorNo])
REFERENCES [dbo].[Vendors] ([VendorNo])
GO
ALTER TABLE [dbo].[Products] CHECK CONSTRAINT [FK_Vendors_Products]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_AttributeValues_Users_DeptNo]') AND parent_object_id = OBJECT_ID(N'[dbo].[Users]'))
ALTER TABLE [dbo].[Users]  WITH CHECK ADD  CONSTRAINT [FK_AttributeValues_Users_DeptNo] FOREIGN KEY([DeptNo])
REFERENCES [dbo].[AttributeValues] ([AttributeValueNo])
GO
ALTER TABLE [dbo].[Users] CHECK CONSTRAINT [FK_AttributeValues_Users_DeptNo]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Cards_Users]') AND parent_object_id = OBJECT_ID(N'[dbo].[Users]'))
ALTER TABLE [dbo].[Users]  WITH CHECK ADD  CONSTRAINT [FK_Cards_Users] FOREIGN KEY([CardNo])
REFERENCES [dbo].[Cards] ([CardNo])
GO
ALTER TABLE [dbo].[Users] CHECK CONSTRAINT [FK_Cards_Users]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Cards_MachineCards]') AND parent_object_id = OBJECT_ID(N'[dbo].[MachineCards]'))
ALTER TABLE [dbo].[MachineCards]  WITH CHECK ADD  CONSTRAINT [FK_Cards_MachineCards] FOREIGN KEY([CardNo])
REFERENCES [dbo].[Cards] ([CardNo])
GO
ALTER TABLE [dbo].[MachineCards] CHECK CONSTRAINT [FK_Cards_MachineCards]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Machines_MachineCards]') AND parent_object_id = OBJECT_ID(N'[dbo].[MachineCards]'))
ALTER TABLE [dbo].[MachineCards]  WITH CHECK ADD  CONSTRAINT [FK_Machines_MachineCards] FOREIGN KEY([MachineNo])
REFERENCES [dbo].[Machines] ([MachineNo])
GO
ALTER TABLE [dbo].[MachineCards] CHECK CONSTRAINT [FK_Machines_MachineCards]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Cards_Transactions]') AND parent_object_id = OBJECT_ID(N'[dbo].[Transactions]'))
ALTER TABLE [dbo].[Transactions]  WITH CHECK ADD  CONSTRAINT [FK_Cards_Transactions] FOREIGN KEY([CardNo])
REFERENCES [dbo].[Cards] ([CardNo])
GO
ALTER TABLE [dbo].[Transactions] CHECK CONSTRAINT [FK_Cards_Transactions]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Machines_Transactions]') AND parent_object_id = OBJECT_ID(N'[dbo].[Transactions]'))
ALTER TABLE [dbo].[Transactions]  WITH CHECK ADD  CONSTRAINT [FK_Machines_Transactions] FOREIGN KEY([MachineNo])
REFERENCES [dbo].[Machines] ([MachineNo])
GO
ALTER TABLE [dbo].[Transactions] CHECK CONSTRAINT [FK_Machines_Transactions]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Products_Transactions]') AND parent_object_id = OBJECT_ID(N'[dbo].[Transactions]'))
ALTER TABLE [dbo].[Transactions]  WITH CHECK ADD  CONSTRAINT [FK_Products_Transactions] FOREIGN KEY([ProductNo])
REFERENCES [dbo].[Products] ([ProductNo])
GO
ALTER TABLE [dbo].[Transactions] CHECK CONSTRAINT [FK_Products_Transactions]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Jobs_TransactionJobs]') AND parent_object_id = OBJECT_ID(N'[dbo].[TransactionJobs]'))
ALTER TABLE [dbo].[TransactionJobs]  WITH CHECK ADD  CONSTRAINT [FK_Jobs_TransactionJobs] FOREIGN KEY([JobNo])
REFERENCES [dbo].[Jobs] ([JobNo])
GO
ALTER TABLE [dbo].[TransactionJobs] CHECK CONSTRAINT [FK_Jobs_TransactionJobs]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Transactions_TransactionJobs]') AND parent_object_id = OBJECT_ID(N'[dbo].[TransactionJobs]'))
ALTER TABLE [dbo].[TransactionJobs]  WITH CHECK ADD  CONSTRAINT [FK_Transactions_TransactionJobs] FOREIGN KEY([TransactionNo])
REFERENCES [dbo].[Transactions] ([TransactionNo])
GO
ALTER TABLE [dbo].[TransactionJobs] CHECK CONSTRAINT [FK_Transactions_TransactionJobs]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_EmailAddresses_ReportEmails]') AND parent_object_id = OBJECT_ID(N'[dbo].[ReportEmails]'))
ALTER TABLE [dbo].[ReportEmails]  WITH CHECK ADD  CONSTRAINT [FK_EmailAddresses_ReportEmails] FOREIGN KEY([EmailAddressNo])
REFERENCES [dbo].[EmailAddresses] ([EmailAddressNo])
GO
ALTER TABLE [dbo].[ReportEmails] CHECK CONSTRAINT [FK_EmailAddresses_ReportEmails]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Reports_ReportEmails]') AND parent_object_id = OBJECT_ID(N'[dbo].[ReportEmails]'))
ALTER TABLE [dbo].[ReportEmails]  WITH CHECK ADD  CONSTRAINT [FK_Reports_ReportEmails] FOREIGN KEY([ReportNo])
REFERENCES [dbo].[Reports] ([ReportNo])
GO
ALTER TABLE [dbo].[ReportEmails] CHECK CONSTRAINT [FK_Reports_ReportEmails]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Machine_MachineBins]') AND parent_object_id = OBJECT_ID(N'[dbo].[MachineBins]'))
ALTER TABLE [dbo].[MachineBins]  WITH CHECK ADD  CONSTRAINT [FK_Machine_MachineBins] FOREIGN KEY([MachineNo])
REFERENCES [dbo].[Machines] ([MachineNo])
GO
ALTER TABLE [dbo].[MachineBins] CHECK CONSTRAINT [FK_Machine_MachineBins]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Products_MachineBins]') AND parent_object_id = OBJECT_ID(N'[dbo].[MachineBins]'))
ALTER TABLE [dbo].[MachineBins]  WITH CHECK ADD  CONSTRAINT [FK_Products_MachineBins] FOREIGN KEY([ProductNo])
REFERENCES [dbo].[Products] ([ProductNo])
GO
ALTER TABLE [dbo].[MachineBins] CHECK CONSTRAINT [FK_Products_MachineBins]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_AttributeValues_MachineSettings]') AND parent_object_id = OBJECT_ID(N'[dbo].[MachineSettings]'))
ALTER TABLE [dbo].[MachineSettings]  WITH CHECK ADD  CONSTRAINT [FK_AttributeValues_MachineSettings] FOREIGN KEY([AttributeValueNo])
REFERENCES [dbo].[AttributeValues] ([AttributeValueNo])
GO
ALTER TABLE [dbo].[MachineSettings] CHECK CONSTRAINT [FK_AttributeValues_MachineSettings]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Machines_MachineSettings]') AND parent_object_id = OBJECT_ID(N'[dbo].[MachineSettings]'))
ALTER TABLE [dbo].[MachineSettings]  WITH CHECK ADD  CONSTRAINT [FK_Machines_MachineSettings] FOREIGN KEY([MachineNo])
REFERENCES [dbo].[Machines] ([MachineNo])
GO
ALTER TABLE [dbo].[MachineSettings] CHECK CONSTRAINT [FK_Machines_MachineSettings]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_AttributeValues_Reports]') AND parent_object_id = OBJECT_ID(N'[dbo].[Reports]'))
ALTER TABLE [dbo].[Reports]  WITH CHECK ADD  CONSTRAINT [FK_AttributeValues_Reports] FOREIGN KEY([FrequencyNo])
REFERENCES [dbo].[AttributeValues] ([AttributeValueNo])
GO
ALTER TABLE [dbo].[Reports] CHECK CONSTRAINT [FK_AttributeValues_Reports]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_AttributeValues_Security]') AND parent_object_id = OBJECT_ID(N'[dbo].[Security]'))
ALTER TABLE [dbo].[Security]  WITH CHECK ADD  CONSTRAINT [FK_AttributeValues_Security] FOREIGN KEY([RoleNo])
REFERENCES [dbo].[AttributeValues] ([AttributeValueNo])
GO
ALTER TABLE [dbo].[Security] CHECK CONSTRAINT [FK_AttributeValues_Security]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_AttributeValues_Machines_LocationNo]') AND parent_object_id = OBJECT_ID(N'[dbo].[Machines]'))
ALTER TABLE [dbo].[Machines]  WITH CHECK ADD  CONSTRAINT [FK_AttributeValues_Machines_LocationNo] FOREIGN KEY([LocationNo])
REFERENCES [dbo].[AttributeValues] ([AttributeValueNo])
GO
ALTER TABLE [dbo].[Machines] CHECK CONSTRAINT [FK_AttributeValues_Machines_LocationNo]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_AttributeValues_Machines_StatusNo]') AND parent_object_id = OBJECT_ID(N'[dbo].[Machines]'))
ALTER TABLE [dbo].[Machines]  WITH CHECK ADD  CONSTRAINT [FK_AttributeValues_Machines_StatusNo] FOREIGN KEY([StatusNo])
REFERENCES [dbo].[AttributeValues] ([AttributeValueNo])
GO
ALTER TABLE [dbo].[Machines] CHECK CONSTRAINT [FK_AttributeValues_Machines_StatusNo]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_AttributeValues_Jobs_LocationNo]') AND parent_object_id = OBJECT_ID(N'[dbo].[Jobs]'))
ALTER TABLE [dbo].[Jobs]  WITH CHECK ADD  CONSTRAINT [FK_AttributeValues_Jobs_LocationNo] FOREIGN KEY([LocationNo])
REFERENCES [dbo].[AttributeValues] ([AttributeValueNo])
GO
ALTER TABLE [dbo].[Jobs] CHECK CONSTRAINT [FK_AttributeValues_Jobs_LocationNo]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Jobs_Jobs]') AND parent_object_id = OBJECT_ID(N'[dbo].[Jobs]'))
ALTER TABLE [dbo].[Jobs]  WITH CHECK ADD  CONSTRAINT [FK_Jobs_Jobs] FOREIGN KEY([JobNo])
REFERENCES [dbo].[Jobs] ([JobNo])
GO
ALTER TABLE [dbo].[Jobs] CHECK CONSTRAINT [FK_Jobs_Jobs]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Attributes_AttributeValues]') AND parent_object_id = OBJECT_ID(N'[dbo].[AttributeValues]'))
ALTER TABLE [dbo].[AttributeValues]  WITH CHECK ADD  CONSTRAINT [FK_Attributes_AttributeValues] FOREIGN KEY([AttributeNo])
REFERENCES [dbo].[Attributes] ([AttributeNo])
GO
ALTER TABLE [dbo].[AttributeValues] CHECK CONSTRAINT [FK_Attributes_AttributeValues]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_AttributeValues_AttributeValues_ParentAttributeNo]') AND parent_object_id = OBJECT_ID(N'[dbo].[AttributeValues]'))
ALTER TABLE [dbo].[AttributeValues]  WITH CHECK ADD  CONSTRAINT [FK_AttributeValues_AttributeValues_ParentAttributeNo] FOREIGN KEY([ParentAttributeValueNo])
REFERENCES [dbo].[AttributeValues] ([AttributeValueNo])
GO
ALTER TABLE [dbo].[AttributeValues] CHECK CONSTRAINT [FK_AttributeValues_AttributeValues_ParentAttributeNo]
