StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PODeletes in one-to-one relationships? Normal behavior?
text
Body
copied!<p>When working in Access, whenever I delete a record from one table - it's corresponding record in another table is also deleted when defined as a one-to-one relationship. This would be normal behavior when I tell it to enforce referential integrity with concerns to deletes and updates (how I understand it). However, it seems to also do it when I leave the enforce referential integrity option for deletes cleared, or when I leave the entire section on referential integrity cleared as well.</p> <p>Basically, I have one table which is a list of suppliers that is a source for another database application that wasn't written by myself. I am using this supplier table in my application, augmenting the information stored in the first program with information entered by the user in the second program in a separate table - but linked using a one-to-one relationship (relationship using primary keys in both tables).</p> <p>I don't want a delete of the secondary information record to result in the deletion of the data in the primary table - which would cause major issues (to put it mildly) in the first program. Is there a way to do this?</p> <p>--Edited to add on 5/27/2009 @ 1600--</p> <p>Here is the SQL script to create the Supplier_Master table from the first application:</p> <pre><code>USE [gtdata_test] GO /****** Object: Table [dbo].[Supplier_Master] Script Date: 05/27/2009 15:58:17 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Supplier_Master]( [Supplier_Code] [nvarchar](50) NOT NULL, [Supplier_Master_Name] [nvarchar](50) NULL, [Salutation] [nvarchar](50) NULL, [Contact] [nvarchar](50) NULL, [Phone] [nvarchar](50) NULL, [Fax] [nvarchar](50) NULL, [EMail] [nvarchar](50) NULL, [Address] [nvarchar](50) NULL, [City] [nvarchar](50) NULL, [State] [nvarchar](50) NULL, [Zip] [nvarchar](50) NULL, [Country] [nvarchar](50) NULL, [Last_Review] [datetime] NULL, [Last_Rating] [datetime] NULL, [Last_Received] [datetime] NULL, [Last_Reject] [datetime] NULL, [Enabled] [int] NULL, [User1] [nvarchar](50) NULL, [User2] [nvarchar](50) NULL, [SupType] [nvarchar](50) NULL, CONSTRAINT [Supplier_Master$PrimaryKey] PRIMARY KEY CLUSTERED ( [Supplier_Code] 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 ALTER TABLE [dbo].[Supplier_Master] WITH NOCHECK ADD CONSTRAINT [SSMA_CC$Supplier_Master$Address$disallow_zero_length] CHECK ((len([Address])>(0))) GO ALTER TABLE [dbo].[Supplier_Master] CHECK CONSTRAINT [SSMA_CC$Supplier_Master$Address$disallow_zero_length] GO ALTER TABLE [dbo].[Supplier_Master] WITH NOCHECK ADD CONSTRAINT [SSMA_CC$Supplier_Master$City$disallow_zero_length] CHECK ((len([City])>(0))) GO ALTER TABLE [dbo].[Supplier_Master] CHECK CONSTRAINT [SSMA_CC$Supplier_Master$City$disallow_zero_length] GO ALTER TABLE [dbo].[Supplier_Master] WITH NOCHECK ADD CONSTRAINT [SSMA_CC$Supplier_Master$Contact$disallow_zero_length] CHECK ((len([Contact])>(0))) GO ALTER TABLE [dbo].[Supplier_Master] CHECK CONSTRAINT [SSMA_CC$Supplier_Master$Contact$disallow_zero_length] GO ALTER TABLE [dbo].[Supplier_Master] WITH NOCHECK ADD CONSTRAINT [SSMA_CC$Supplier_Master$Country$disallow_zero_length] CHECK ((len([Country])>(0))) GO ALTER TABLE [dbo].[Supplier_Master] CHECK CONSTRAINT [SSMA_CC$Supplier_Master$Country$disallow_zero_length] GO ALTER TABLE [dbo].[Supplier_Master] WITH NOCHECK ADD CONSTRAINT [SSMA_CC$Supplier_Master$EMail$disallow_zero_length] CHECK ((len([EMail])>(0))) GO ALTER TABLE [dbo].[Supplier_Master] CHECK CONSTRAINT [SSMA_CC$Supplier_Master$EMail$disallow_zero_length] GO ALTER TABLE [dbo].[Supplier_Master] WITH NOCHECK ADD CONSTRAINT [SSMA_CC$Supplier_Master$Fax$disallow_zero_length] CHECK ((len([Fax])>(0))) GO ALTER TABLE [dbo].[Supplier_Master] CHECK CONSTRAINT [SSMA_CC$Supplier_Master$Fax$disallow_zero_length] GO ALTER TABLE [dbo].[Supplier_Master] WITH NOCHECK ADD CONSTRAINT [SSMA_CC$Supplier_Master$Phone$disallow_zero_length] CHECK ((len([Phone])>(0))) GO ALTER TABLE [dbo].[Supplier_Master] CHECK CONSTRAINT [SSMA_CC$Supplier_Master$Phone$disallow_zero_length] GO ALTER TABLE [dbo].[Supplier_Master] WITH NOCHECK ADD CONSTRAINT [SSMA_CC$Supplier_Master$Salutation$disallow_zero_length] CHECK ((len([Salutation])>(0))) GO ALTER TABLE [dbo].[Supplier_Master] CHECK CONSTRAINT [SSMA_CC$Supplier_Master$Salutation$disallow_zero_length] GO ALTER TABLE [dbo].[Supplier_Master] WITH NOCHECK ADD CONSTRAINT [SSMA_CC$Supplier_Master$State$disallow_zero_length] CHECK ((len([State])>(0))) GO ALTER TABLE [dbo].[Supplier_Master] CHECK CONSTRAINT [SSMA_CC$Supplier_Master$State$disallow_zero_length] GO ALTER TABLE [dbo].[Supplier_Master] WITH NOCHECK ADD CONSTRAINT [SSMA_CC$Supplier_Master$Supplier_Code$disallow_zero_length] CHECK ((len([Supplier_Code])>(0))) GO ALTER TABLE [dbo].[Supplier_Master] CHECK CONSTRAINT [SSMA_CC$Supplier_Master$Supplier_Code$disallow_zero_length] GO ALTER TABLE [dbo].[Supplier_Master] WITH NOCHECK ADD CONSTRAINT [SSMA_CC$Supplier_Master$Supplier_Master_Name$disallow_zero_length] CHECK ((len([Supplier_Master_Name])>(0))) GO ALTER TABLE [dbo].[Supplier_Master] CHECK CONSTRAINT [SSMA_CC$Supplier_Master$Supplier_Master_Name$disallow_zero_length] GO ALTER TABLE [dbo].[Supplier_Master] WITH NOCHECK ADD CONSTRAINT [SSMA_CC$Supplier_Master$SupType$disallow_zero_length] CHECK ((len([SupType])>(0))) GO ALTER TABLE [dbo].[Supplier_Master] CHECK CONSTRAINT [SSMA_CC$Supplier_Master$SupType$disallow_zero_length] GO ALTER TABLE [dbo].[Supplier_Master] WITH NOCHECK ADD CONSTRAINT [SSMA_CC$Supplier_Master$User1$disallow_zero_length] CHECK ((len([User1])>(0))) GO ALTER TABLE [dbo].[Supplier_Master] CHECK CONSTRAINT [SSMA_CC$Supplier_Master$User1$disallow_zero_length] GO ALTER TABLE [dbo].[Supplier_Master] WITH NOCHECK ADD CONSTRAINT [SSMA_CC$Supplier_Master$User2$disallow_zero_length] CHECK ((len([User2])>(0))) GO ALTER TABLE [dbo].[Supplier_Master] CHECK CONSTRAINT [SSMA_CC$Supplier_Master$User2$disallow_zero_length] GO ALTER TABLE [dbo].[Supplier_Master] WITH NOCHECK ADD CONSTRAINT [SSMA_CC$Supplier_Master$Zip$disallow_zero_length] CHECK ((len([Zip])>(0))) GO ALTER TABLE [dbo].[Supplier_Master] CHECK CONSTRAINT [SSMA_CC$Supplier_Master$Zip$disallow_zero_length] GO ALTER TABLE [dbo].[Supplier_Master] ADD DEFAULT ((0)) FOR [Enabled] GO </code></pre> <p>Here is the SQL script to create the tblSupplierInfo table from the second application:</p> <pre><code>USE [instkeeper_test] GO /****** Object: Table [dbo].[tblSupplierInfo] Script Date: 05/27/2009 15:57:30 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[tblSupplierInfo]( [strSupplierID] [nvarchar](50) NOT NULL, [bolSupAltShipAddyRep] [bit] NULL, [bolSupAltShipAddyCal] [bit] NULL, [bolSupInsistNet30] [bit] NULL, [bolRMARequireRepair] [bit] NULL, [bolRMARequireCalibration] [bit] NULL, [bolSupShipOrCourier] [bit] NULL, [bolSupRequireMSDS] [bit] NULL, [bolSupBlanketPO] [bit] NULL, [bolSupRequirePricing] [bit] NULL, [bolSupBlankPricing] [bit] NULL, [bolSupFaxPOSend] [bit] NULL, [bolAdditionalPaperworkRepair] [bit] NULL, [bolAdditionalPaperworkCalibration] [bit] NULL, [strRMARepairWordage] [nvarchar](100) NULL, [strRMACalibrationWordage] [nvarchar](100) NULL, [intBlanketPO] [int] NULL, [bolUseFedExNumber] [bit] NULL, [strFedExNumber] [nvarchar](150) NULL, [bolUseUPSNumber] [bit] NULL, [strUPSNumber] [nvarchar](150) NULL, [bolSupA2LAAccredited] [bit] NULL, [bolSupFreightAllow] [bit] NULL, [bolSupFreightOnly] [bit] NULL, [bolSupUseMiscNum] [bit] NULL, [strSupMiscFreightNum] [nvarchar](150) NULL, CONSTRAINT [tblSupplierInfo$PrimaryKey] PRIMARY KEY CLUSTERED ( [strSupplierID] 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 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Supplier Name' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblSupplierInfo', @level2type=N'COLUMN',@level2name=N'strSupplierID' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Alternate Shipping Address - Repairs?' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblSupplierInfo', @level2type=N'COLUMN',@level2name=N'bolSupAltShipAddyRep' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Alternate Shipping Address - Calibrations?' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblSupplierInfo', @level2type=N'COLUMN',@level2name=N'bolSupAltShipAddyCal' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Force Net 30 Wordage?' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblSupplierInfo', @level2type=N'COLUMN',@level2name=N'bolSupInsistNet30' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Do repairs require RMAs?' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblSupplierInfo', @level2type=N'COLUMN',@level2name=N'bolRMARequireRepair' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Do calibrations require RMAs?' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblSupplierInfo', @level2type=N'COLUMN',@level2name=N'bolRMARequireCalibration' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Use Courier?' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblSupplierInfo', @level2type=N'COLUMN',@level2name=N'bolSupShipOrCourier' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Requires MSDS(s)?' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblSupplierInfo', @level2type=N'COLUMN',@level2name=N'bolSupRequireMSDS' GO ALTER TABLE [dbo].[tblSupplierInfo] ADD DEFAULT ((0)) FOR [bolSupAltShipAddyRep] GO ALTER TABLE [dbo].[tblSupplierInfo] ADD DEFAULT ((0)) FOR [bolSupAltShipAddyCal] GO ALTER TABLE [dbo].[tblSupplierInfo] ADD DEFAULT ((0)) FOR [bolSupInsistNet30] GO ALTER TABLE [dbo].[tblSupplierInfo] ADD DEFAULT ((0)) FOR [bolRMARequireRepair] GO ALTER TABLE [dbo].[tblSupplierInfo] ADD DEFAULT ((0)) FOR [bolRMARequireCalibration] GO ALTER TABLE [dbo].[tblSupplierInfo] ADD DEFAULT ((0)) FOR [bolSupShipOrCourier] GO ALTER TABLE [dbo].[tblSupplierInfo] ADD DEFAULT ((0)) FOR [bolSupRequireMSDS] GO ALTER TABLE [dbo].[tblSupplierInfo] ADD DEFAULT ((0)) FOR [bolSupBlanketPO] GO ALTER TABLE [dbo].[tblSupplierInfo] ADD DEFAULT ((1)) FOR [bolSupRequirePricing] GO ALTER TABLE [dbo].[tblSupplierInfo] ADD DEFAULT ((0)) FOR [bolSupBlankPricing] GO ALTER TABLE [dbo].[tblSupplierInfo] ADD DEFAULT ((0)) FOR [bolSupFaxPOSend] GO ALTER TABLE [dbo].[tblSupplierInfo] ADD DEFAULT ((0)) FOR [bolAdditionalPaperworkRepair] GO ALTER TABLE [dbo].[tblSupplierInfo] ADD DEFAULT ((0)) FOR [bolAdditionalPaperworkCalibration] GO ALTER TABLE [dbo].[tblSupplierInfo] ADD DEFAULT ('RMA #') FOR [strRMARepairWordage] GO ALTER TABLE [dbo].[tblSupplierInfo] ADD DEFAULT ('RMA #') FOR [strRMACalibrationWordage] GO ALTER TABLE [dbo].[tblSupplierInfo] ADD DEFAULT ((0)) FOR [intBlanketPO] GO ALTER TABLE [dbo].[tblSupplierInfo] ADD DEFAULT ((0)) FOR [bolUseFedExNumber] GO ALTER TABLE [dbo].[tblSupplierInfo] ADD DEFAULT ((0)) FOR [bolUseUPSNumber] GO </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload