如何设置“自动插入"通过使用SQL Server的外键值? [英] How to set "auto insert" foreign key value by using SQL Server?

问题描述

我已经创建了两个表，并且还创建了它们之间的关系.

I have created two tables and also created a relationship between them.

表students:

create table students
(
    [StudentId] NVARCHAR(50) NOT NULL PRIMARY KEY,
    [Name] NVARCHAR(50) NOT NULL
);

表studentprofile:

create table studentprofile
(
    [Id] INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
    [StudentId] NVARCHAR(50) NOT NULL,
    [Address] NVARCHAR(50) NOT NULL,
);

和关系:

alter table studentprofile
add constraint students_studentprofile_FK 
    foreign key (StudentId) 
    references students(StudentId) 
        on delete cascade on update cascade

但是，当我写此行时:

insert into students values('110111', 'Marik')

StudentId的值(在表studentprofile中)未自动更新.为什么?

the value of StudentId (in table studentprofile) wasn't updated automatically. Why?

你能告诉我如何设置StudentId的值(在表studentprofile中)，只要我将其插入表students即可自动插入?

Can you tell me how to set the value of StudentId (in table studentprofile) can be inserted automatically whenever I insert into table students?

推荐答案

没有insert cascade这样的东西.
您可以通过使用触发器在students表中插入，将默认值(或空值)插入studentprofile表中来实现这样的事情:

There is no such thing as insert cascade.
You can implement such a thing by using a trigger for insert on your students table, inserting default values (or nulls) into the studentprofile table:

CREATE TRIGGER students_insert ON students AFTER INSERT
AS
    INSERT INTO studentprofile(StudentId, Address)
    SELECT StudentId, 'NO ADDRESS'
    FROM inserted

请注意，您的地址"列定义为非null且没有默认值，这就是为什么我使用硬编码的'NO ADDRESS'的原因.

Note that your Address column is defined as not null and has no default value, this is why I've used the hard coded 'NO ADDRESS' for it.

但是，我同意对您的问题的评论:您最好使用其他插入语句将数据插入到学生个人资料中(最好是在向学生插入插入内容的事务中).

However, I agree with the comments on your question: you would be better off inserting the data to the student profile using a different insert statement (perhaps inside a transaction with the insert to students).

这篇关于如何设置“自动插入"通过使用SQL Server的外键值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！