SQL - 如何插入一个外键作为列的值 [英] SQL - How to INSERT a foreign key as a value for a column
问题描述
我知道这是相当基本的,我已经搜索了相当一段时间的答案,但我很困扰。
我不知道如何
以下是查询表:
<$> (20)不是NULL,
姓氏VARCHAR(20)不是NULL,$ b $ 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),
);
以下是问题代码:
- 填充客户表
插入客户
VALUES('Harold','Kumar','2010-07-07 14:03:54','3珀斯'Red Ln',0812391245,NULL)
插入客户
VALUES('Bingo','Washisnameoh','2010-09-21 12:30:07','3 Red St,Perth ',0858239471,NULL)
插入客户
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)
(特别是最后8位的那一行)
当执行第二个查询时,它会回应这个:
Msg 547,Level 16,State 0,Line 1
INSERT语句与FOREIGN KEY SAME TABLE约束冲突
FK_ 客户 _referr__5772F790\" 。数据库
master,表dbo.customer,列customer_id发生冲突。
已被终止。
感谢您的帮助。
你在customer_id上有一个主键 - 而你的插入语句对于客户id没有价值$ b $ =h2_lin>解决方案
b 2)
您有一个自引用的外键,以referrer_id的形式引用customer_id。
当你插入一个不是null的referrer_id的记录时,在你的情况下是'8',确保你已经插入了一个记录customer_id'8'$ /
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'
这篇关于SQL - 如何插入一个外键作为列的值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!