SQL - How to INSERT a foreign key as a value for a

Question

I know this is rather basic, and i've searched for answers for quite some time, but I'm troubled.

I don't know how to make my coding readable on here but here it is.

Here's the query for making the table in question:

CREATE TABLE customer   
(   customer_id INT NOT NULL CONSTRAINT customer_pk PRIMARY KEY IDENTITY,
    first_name  VARCHAR(20) NOT NULL,       
    surname VARCHAR(20) NOT NULL,       
    dob DATETIME    NOT NULL,       
    home_address    VARCHAR(50) NOT NULL,       
    contact_number  VARCHAR(10) NOT NULL,       
    referrer_id INT NULL    FOREIGN KEY REFERENCES customer(customer_id),   
);

And here's the problem code:

--fill customer table
INSERT INTO customer
VALUES ( 'Harold', 'Kumar', '2010-07-07 14:03:54', '3 Blue Ln, Perth', 0812391245, NULL )
INSERT INTO customer 
VALUES ( 'Bingo', 'Washisnameoh', '2010-09-21 12:30:07', '3 Red St, Perth', 0858239471, NULL )
INSERT INTO customer
VALUES ( 'John', 'Green', '2010-11-07 14:13:34', '4 Blue St, Perth', 0423904823, NULL )
INSERT INTO customer
VALUES ( 'Amir', 'Blumenfeld', '2010-11-01 11:03:04', '166 Yellow Rd, Perth', 0432058323, NULL)

INSERT INTO customer
VALUES ( 'Hank', 'Green', '2010-07-07 16:04:24', '444 Orange Crs, Perth', 0898412429, 8)

(Specifically the line with the 8 value at the end.)

When executing the second query it responds with this:

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY SAME TABLE constraint "FK_customer_referr__5772F790". The conflict occurred in database "master", table "dbo.customer", column 'customer_id'. The statement has been terminated.

Appreciate your help with this.

Answer 1

1) You have a primary key on customer_id - and your insert statements do not have value for customer id

2) You have a self referencing foreign key in the form of referrer_id referring to customer_id. When you are inserting a record with referrer_id which is not null, in your case which is '8', make sure you already inserted a record with customer_id '8'

Answer 2

The only problem you have here is the identity must have a seed value which can be like Identity(1,1) where the first 1 is the starting point and the send 1 is the auto seed number...the re run your insert statement

Answer 3

How do you know that the referrer_id is supposed to be 8 ??

What you need to do is catch the value of the customer_id inserted, and then used that in your second query:

DECLARE @referToID INT

INSERT INTO dbo.Customer(first_name, surname, dob, home_address, contact_number, referrer_id)
VALUES ('Harold', 'Kumar', '2010-07-07 14:03:54', '3 Blue Ln, Perth', 0812391245, NULL)

SELECT @ReferToID = SCOPE_IDENTITY() ;  -- catch the newly given IDENTITY ID

INSERT INTO dbo.Customer(first_name, surname, dob, home_address, contact_number, referrer_id)
VALUES ('Hank', 'Green', '2010-07-07 16:04:24', '444 Orange Crs, Perth', 0898412429, @ReferToID)

I don't know which row you want to refer to (you didn't specify) - but I hope you understand the mechanism:

• insert the new row into your table
• get the newly inserted ID by using SCOPE_IDENTITY
• insert the next row which refers to that first row and use that value returned by SCOPE_IDENTITY

Update: if you really want to have a given row reference itself (strange concept.....), then you'd need to do it in two steps:

• insert the new row into your table
• get the newly inserted ID by using SCOPE_IDENTITY
• update that row to set the referrer_id

Something like this:

DECLARE @NewCustomerID INT

INSERT INTO dbo.Customer(first_name, surname, dob, home_address, contact_number)
VALUES ('Hank', 'Green', '2010-07-07 16:04:24', '444 Orange Crs, Perth', 0898412429)

SELECT @NewCustomerID = SCOPE_IDENTITY() ;  -- catch the newly given IDENTITY ID

UPDATE dbo.Customer
SET referrer_id = @NewCustomerID
WHERE customer_id = @NewCustomerID