获取插入外键SQLite c#的值的最佳方法

whitzsjs 于 2023-01-31 发布在 SQLite

关注(0)|答案(1)|浏览(156)

例如，当您有两个带有外键的表时

CREATE TABLE departments
( department_id INTEGER PRIMARY KEY AUTOINCREMENT,
  department_name VARCHAR
);

CREATE TABLE employees
( employee_id INTEGER PRIMARY KEY AUTOINCREMENT,
  last_name VARCHAR NOT NULL,
  first_name VARCHAR,
  department_id INTEGER,
  CONSTRAINT fk_departments
    FOREIGN KEY (department_id)
    REFERENCES departments(department_id)
);

然后，当您像这样插入数据时：

INSERT INTO departments VALUES (30, 'HR');
INSERT INTO departments VALUES (999, 'Sales');

INSERT INTO employees VALUES (10000, 'Smith', 'John', 30);
INSERT INTO employees VALUES (10001, 'Anderson', 'Dave', 999);

当插入emplyees表时，查找值30和999的最佳方法是什么？

sqlite

来源：https://stackoverflow.com/questions/75271758/best-way-to-get-value-for-inserting-into-foreign-key-sqlite-c-sharp

1条答案

按热度按时间

2w2cym1i1#

下面是一个使用sqlite last_insert_id()函数的示例（我将对其进行清理，以显示它实际上是一个自动编号值，而不是您已知的部门编号）：

begin transaction;

insert into departments (department_name) values ('HR');
insert into employees values (10000, 'Smith','John',last_insert_rowid());

insert into departments (department_name) values ('Sales');
insert into employees values (10001, 'Anderson','Dave',last_insert_rowid());

commit;

另一个策略是使用部门名称，这样就可以完全避免担心ID（我假设您添加了一个唯一约束，以便部门名称必须是唯一的-这是明智的）：

insert into departments values (30, 'HR');
insert into departments values (999, 'Sales');

insert into employees 
    select 
        10000,
        'Smith',
        'John',
        (select department_id 
            from departments 
            where department_name = 'HR');

insert into employees 
    select 
        10001,
        'Anderson',
        'Dave',
        (select department_id 
            from departments where department_name = 'Sales');

赞(0）回复(0）举报 2023-01-31

我来回答

获取插入外键SQLite c#的值的最佳方法

1条答案

相关问题

热门标签

最新问答