CODEWITHSUNDEEP
c#sql-serversql-server-2008foreign-keysprimary-key
ajpr
ajpr

Reputation: 113

Why my foreign key is NULL on inserting values

I am sqlserver beginner and i had first created a table CUSTOMERS see below :

CREATE TABLE CUSTOMERS ( ID INT NOT NULL,
NAME VARCHAR(20) NOT NULL, 
AGE INT NOT NULL, 
ADDRESS CHAR (25) , 
SALARY DECIMAL (18,2) DEFAULT 70000, not null identity(1, 1) PRIMARY KEY (ID));

and then i inserted the value in table like this:

INSERT INTO [vims].[dbo].[CUSTOMERS] (NAME, AGE, ADDRESS, SALARY) 
VALUES('AbheshekKaBhai',21,'Agra',70000.00);

and its done succesfully please see this : http://prntscr.com/7w19cr\

After i created another table ORDERS like this:

CREATE TABLE [vims].[dbo].ORDERS (
  ID INT not null identity(1, 1) PRIMARY KEY,
DATE DATETIME,
Customer_ID  INT FOREIGN KEY (Customer_ID) REFERENCES CUSTOMERS (ID),
AMOUNT INT );

and i insert data inside it like this :

INSERT INTO [vims].[dbo].ORDERS(DATE,AMOUNT) VALUES(28,66000.00);

I get the table like this on repetitively inserting the record 6 times : http://prntscr.com/7w1a6j

My problem is when i try to insert data in Foreign key column then it gives error saying there is conflict of value of ID between CUSTOMERS and ORDERS and when i add nothing at foreign key column it shows null. Why it do so ? How to insert value on foreign key column ?

Upvotes: 1

Views: 1541

Answers (2)

Sayam Jain
Sayam Jain

Reputation: 56

You need to do the following while performing the insert:

Declare @CustomerID int
INSERT INTO [vims].[dbo].[CUSTOMERS](NAME,AGE,ADDRESS,SALARY)
    VALUES('AbheshekKaBhai',21,'Agra',70000.00);
@CustomerID = SCOPE_IDENTITY()
INSERT INTO [vims].[dbo].ORDERS(DATE,AMOUNT,Customer_ID
    VALUES(28,66000.00,@CustomerID);

You were not inserting the foreign key in the insert statement. There needs to be a value for a foreign key in the child table on insert. It will not take it automatically.

Upvotes: 1

Utkarsh
Utkarsh

Reputation: 589

Firstly, the Customer ID should exist in CUSTOMER table if you want to insert any records in ORDERS table referencing Customer_ID.

Secondly, If you don't want to add NULL in Foreign Key Column in ORDERS Table, then while declaration make that column as NOT NULL. So, any insert without having proper relationship won't get inserted(including null). 

 CREATE TABLE [vims].[dbo].ORDERS (
 ID INT not null identity(1, 1) PRIMARY KEY,
 DATE DATETIME,
 Customer_ID  INT NOT NULL FOREIGN KEY (Customer_ID) REFERENCES CUSTOMERS (ID),
 AMOUNT INT );

Hope that helps.

Upvotes: 1

Related Questions