In t-sql 2012, I have displayed 3 ddls for 3 tables that I want rows to be updated. Basically in the Locker table, I am going to update the data for preexisting data. However I want the lockID value in the locker table to be the same value in the Lock
and the LockCombination tables. Basically I will create rows in the locker table. Once the locker table is updated, I want the lockid value to refer to the same lockID value between the locker, lock, and LockCombination tables. Thus would you
show me the sql onhow to accomplish this goal?
CREATE TABLE [dbo].[Locker](
[lockerID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[schoolID] [int] NOT NULL,
[number] varchar NOT NULL,
[serialNumber] varchar NULL,
[type] varchar NULL,
[locationID] [int] NULL,
[grade] varchar NULL,
[reserved] [bit] NULL,
[lockID] [int] NULL,
[share] [bit] NULL,
[lockType] varchar NULL,
CONSTRAINT [PK_Locker] PRIMARY KEY NONCLUSTERED
(
[lockerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 97) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Locker] WITH NOCHECK ADD CONSTRAINT [FK_Locker_Lock] FOREIGN KEY([lockID])
REFERENCES [dbo].[Lock] ([lockID])
GO
ALTER TABLE [dbo].[Locker] CHECK CONSTRAINT [FK_Locker_Lock]
GO
ALTER TABLE [dbo].[Locker] WITH CHECK ADD CONSTRAINT [FK_Locker_LockerLocation] FOREIGN KEY([locationID])
REFERENCES [dbo].[LockerLocation] ([locationID])
GO
ALTER TABLE [dbo].[Locker] CHECK CONSTRAINT [FK_Locker_LockerLocation]
GO
ALTER TABLE [dbo].[Locker] WITH NOCHECK ADD CONSTRAINT [FK_Locker_School] FOREIGN KEY([schoolID])
REFERENCES [dbo].[School] ([schoolID])
GO
ALTER TABLE [dbo].[Locker] CHECK CONSTRAINT [FK_Locker_School]
GO
CREATE TABLE [dbo].[Lock](
[lockID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[schoolID] [int] NOT NULL,
[serialNumber] varchar NULL,
[type] varchar NULL,
[comboSeq] [tinyint] NOT NULL,
CONSTRAINT [PK_Lock] PRIMARY KEY NONCLUSTERED
(
[lockID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 97) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Lock] WITH NOCHECK ADD CONSTRAINT [FK_Lock_School] FOREIGN KEY([schoolID])
REFERENCES [dbo].[School] ([schoolID])
GO
ALTER TABLE [dbo].[Lock] CHECK CONSTRAINT [FK_Lock_School]
GO
CREATE TABLE [dbo].[LockCombination](
[comboID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[lockID] [int] NOT NULL,
[seq] [tinyint] NOT NULL,
[combo] varchar NOT NULL,
CONSTRAINT [PK_LockCombination] PRIMARY KEY NONCLUSTERED
(
[comboID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 97) ON [PRIMARY]
) ON [PRIMARY]