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.
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'
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
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
