当前位置：文江博客话题详情

自动索引的 SQL Server 2008 外键

发布于 2025-01-07 16:20:22 字数 104 浏览 0 评论 0原文

SQL Server 2008 中的外键是否会自动使用值进行索引？例如。如果我在可能的父表中的主键（或自动递增）中添加一个值，具有引用该键的外键的表将自动具有相同的值吗？或者我必须明确地这样做？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

饮湿 2025-01-14 16:20:22

不，如果您在子表中创建外键，则插入父行时它不会自动填充。如果你想一想，这是有道理的。假设您有一个如下表：

CREATE TABLE dbo.Students
(
  StudentID INT IDENTITY(1,1) PRIMARY KEY,
  Name SYSNAME
);

CREATE TABLE dbo.StudentLoans
(
  LoanID INT IDENTITY(1,1) PRIMARY KEY,
  StudentID INT FOREIGN KEY REFERENCES dbo.Students(StudentID),
  Amount BIGINT -- just being funny
);

您建议的是，当您向 Students 添加一行时，系统应自动向 StudentLoans 添加一行 - 但如果那样呢？学生没有贷款？如果学生确实有贷款，金额应该是多少？系统应该选择一个随机数吗？

通常，在这种情况下会发生的情况是您将同时添加学生及其贷款。因此，如果您知道贷款金额和学生姓名，您可以说：

DECLARE 
  @Name       SYSNAME = N'user962206', 
  @LoanAmount BIGINT = 50000,
  @StudentID  INT;

INSERT dbo.Students(Name) 
  SELECT @Name;

SELECT @StudentID = SCOPE_IDENTITY();

INSERT dbo.StudentLoans(StudentID, Amount)
  SELECT @StudentID, @LoanAmount;

No, if you create a foreign key in a child table, it will not automatically get populated when a parent row gets inserted. If you think about this it makes sense. Let's say you have a table like:

CREATE TABLE dbo.Students
(
  StudentID INT IDENTITY(1,1) PRIMARY KEY,
  Name SYSNAME
);

CREATE TABLE dbo.StudentLoans
(
  LoanID INT IDENTITY(1,1) PRIMARY KEY,
  StudentID INT FOREIGN KEY REFERENCES dbo.Students(StudentID),
  Amount BIGINT -- just being funny
);

What you are suggesting is that when you add a row to Students, the system should automatically add a row to StudentLoans - but what if that student doesn't have a loan? If the student does have a loan, what should the amount be? Should the system pick a random number?

Typically what will happen in this scenario is that you'll be adding a student and their loan at the same time. So if you know the loan amount and the student's name, you can say:

DECLARE 
  @Name       SYSNAME = N'user962206', 
  @LoanAmount BIGINT = 50000,
  @StudentID  INT;

INSERT dbo.Students(Name) 
  SELECT @Name;

SELECT @StudentID = SCOPE_IDENTITY();

INSERT dbo.StudentLoans(StudentID, Amount)
  SELECT @StudentID, @LoanAmount;

回复收藏 0 原文

~没有更多了~