如何使用外键将值插入现有SQL Server表中 [英] How to insert values into existing SQL Server tables with foreign keys

问题描述

我有一个数据库，我想向该表中插入新值，问题是我有两个彼此为FK的表.我知道问题出在Alter Table上，但我不知道是什么原因造成的.

I have a database and I want to insert new values to the table, the problem is that I have two tables with FK to each other. I know the problem my be something with the Alter Table, however I can't figure out what cause it.

第一张桌子:部门

 CREATE TABLE [dbo].[Department]
 (
    [DID] [int]  primary key,
    [Name] [varchar](255) ,
    [Description] [varchar](255) ,
    [ManagerId] [int]
);

第二张表:OfficialEmployee

CREATE TABLE [dbo].[OfficialEmployee]
(
    [EID] [int]  primary key,
    [StartDate] [date] ,
    [Degree] [varchar](255) ,
    [DepartmentId] [int] ,

    CONSTRAINT [FK_DIDOfficial] 
        FOREIGN KEY([EID]) REFERENCES [dbo].[Employee] ([EID]) 
                ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT [FK_EIDOfficial] 
        FOREIGN KEY([DepartmentId]) REFERENCES [dbo].[Department] ([DID]) 
                ON UPDATE CASCADE ON DELETE CASCADE
);

更改表的表达式:

ALTER TABLE [dbo].[Department] WITH CHECK 
    ADD CONSTRAINT [FK_DepMan] 
        FOREIGN KEY([ManagerId]) REFERENCES [dbo].[OfficialEmployee] ([EID]) 
                ON DELETE NO ACTION

我只需要有人告诉我如何将值插入其中一张表

I just need that some one will tell me how do I insert values to one of the tables

提前谢谢

推荐答案

给出结构，我认为您需要两个插入和一个更新:

Given the structure, I think you need two inserts and an update:

1. 插入一个具有NULL经理的新部门.
2. 在员工表中插入经理.
3. 使用经理的ID更新部门.

1. Insert a new department with a NULL manager.
2. Insert the manager in the employees table.
3. Update the department with the manager's id.

这篇关于如何使用外键将值插入现有SQL Server表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！