Link to home
Create AccountLog in
Avatar of SasDev
SasDevFlag for United States of America

asked on

Insert Column into Primary Table with Foreign Key Constraints

I'm looking to understand the most appropriate way to insert a colomn on a table that has FK constrants.
I would like to add the column PostDate, smalldatetime
and then SET the PostDate column to the value of EntryDate.

Table Def:
USE [PurchaseOrders]
GO
/****** Object:  Table [dbo].[tblPurchaseOrders]    Script Date: 12/24/2010 10:04:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblPurchaseOrders](
      [PONum] [int] IDENTITY(1,1) NOT NULL,
      [SupplierNum] [int] NOT NULL CONSTRAINT [DF_tblLYPurchaseOrders_SupNum]  DEFAULT (0),
      [WarehouseNum] [int] NOT NULL CONSTRAINT [DF_tblLYPurchaseOrders_WarehouseNum]  DEFAULT (0),
      [FreightCharge] [decimal](18, 2) NOT NULL CONSTRAINT [DF_tblLYPurchaseOrders_FreightCharge]  DEFAULT (0),
      [MiscSurchargePercent] [decimal](18, 2) NOT NULL CONSTRAINT [DF_tblLYPurchaseOrders_FuelSurcharge]  DEFAULT (0),
      [MiscSurchargeCurrency] [decimal](18, 2) NOT NULL CONSTRAINT [DF_tblLYPurchaseOrders_MiscSurchargeCurrently]  DEFAULT (0),
      [DiscountPercent] [decimal](18, 2) NOT NULL CONSTRAINT [DF_tblLYPurchaseOrders_SalesTax]  DEFAULT (0),
      [DiscountCurrency] [decimal](18, 2) NOT NULL CONSTRAINT [DF_tblLYPurchaseOrders_DiscountCurrency]  DEFAULT (0),
      [FrequencyType] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Comments] [varchar](5000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [EntryDate] [datetime] NULL CONSTRAINT [DF_tblLYPurchaseOrders_EntryDate]  DEFAULT (getdate()),
      [EmployeeNum] [int] NOT NULL CONSTRAINT [DF_tblLYPurchaseOrders_EmployeeNum]  DEFAULT (0),
      [ArrivalDate] [datetime] NULL,
      [DeptID] [int] NULL,
      [SalesTax] [decimal](18, 2) NOT NULL CONSTRAINT [DF_tblLYPurchaseOrders_SalesTax_1]  DEFAULT (0),
      [ShipToName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [ShipToAddress] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [ShipToAddress2] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [ShipToCity] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [ShipToState] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [ShipToZip] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_tblLYPurchaseOrders] PRIMARY KEY CLUSTERED
(
      [PONum] ASC
) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
USE [PurchaseOrders]
GO
ALTER TABLE [dbo].[tblPurchaseOrders]  WITH CHECK ADD  CONSTRAINT [FK_tblLYPurchaseOrders_lkpFrequency] FOREIGN KEY([FrequencyType])
REFERENCES [dbo].[lkpFrequency] ([FrequencyType])
GO
ALTER TABLE [dbo].[tblPurchaseOrders]  WITH CHECK ADD  CONSTRAINT [FK_tblLYPurchaseOrders_tblSuppliers] FOREIGN KEY([SupplierNum])
REFERENCES [dbo].[tblSuppliers] ([SupplierNum])
ASKER CERTIFIED SOLUTION
Avatar of SasDev
SasDev
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account