Cannot Insert Foreign Keys

Question

hello Everyone, please I have an issue with my foreign keys created a table called fact sales
CREATE TABLE [dbo].[FactSales](
[salesId] [int] IDENTITY(301,1) NOT NULL,
[SoldPrice] [float] NULL,
[Status] nvarchar NULL,
[UnitRate] [float] NULL,
[QTY] [float] NULL,
[MRP] [float] NULL,
[SalesDate] [date] NULL,
[ProductId] [int] not NULL,
[VendorId] [int] not NULL,
PRIMARY KEY CLUSTERED
(
[salesId] 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].[FactSales] WITH CHECK ADD CONSTRAINT [FK_FactSales_DimOpticProduct] FOREIGN KEY([ProductId])
REFERENCES [dbo].[DimOpticProduct] ([productId])
GO

ALTER TABLE [dbo].[FactSales] CHECK CONSTRAINT [FK_FactSales_DimOpticProduct]
GO

ALTER TABLE [dbo].[FactSales] WITH CHECK ADD CONSTRAINT [FK_FactSales_DimVendor] FOREIGN KEY([VendorId])
REFERENCES [dbo].[DimVendor] ([vendorId])
GO

ALTER TABLE [dbo].[FactSales] CHECK CONSTRAINT [FK_FactSales_DimVendor]
GO

but when I try to insert into the table
insert into [dbo].[FactSales](
[SoldPrice],
[Status],
[UnitRate],
[QTY],
[MRP],
[SalesDate]
)
select
[SoldPrice],
[Status],
[UnitRate],
[QTY],
[MRP],
[salesDate]
from [dbo].[opticstg]
it give me an error
Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'VendorId', table 'Parminder.dbo.FactSales'; column does not allow nulls. INSERT fails.
The statement has been terminated.
and I checked my vendorid it is a primary key so no nulls.
can any one help please.

Answer 1

Hi @Laura Ijewere ,

>Msg 515, Level 16, State 2, Line 1 Cannot insert the value NULL into column 'VendorId', table 'Parminder.dbo.FactSales'; column does not allow nulls. INSERT fails.

you didn't specify the [ProductId],[VendorId] column in your insert statement, the SQL Server will insert the value NULL into these columns, but you have defined these columns not nulll in the table [FactSales]), and that why you get this error.

>>>hi, so I tried allowing Nulls but it ended up not populating anything in the vendorid and productid columns,i mean those two col showed as nulls for all rows.i don't know why

This is normal behavior. You have allowed [ProductId], [VendorId] to be NULL, but you still have not specified [ProductId], [VendorId] in your insert statement. SQL Server will insert NULL values in these two columns by default. And the foreign key columns can be NULL or duplicate.
Check this post for more details, and please view the MS doc—Create Foreign Key Relationships for the Limits and restrictions.

Answer 2

Hello LauraIjewere-5120,

The select statement is missing the VendorId column and other columns, so SQL Server try to use NULLS for that fields.

Recommendation, add "" for varchar and 0 for integers in the fields that you don't have in the source table.

Let us know if that fix the problem,

Rafael Donado
Data Consultant