sql server 查询外键引用 sql如何引用外键

转载

编程艺术家 2023-12-12 16:28:05

文章标签 sql server 查询外键引用外键外键约束默认值 文章分类 SQL Server 数据库

先新建2个表，写入数据

-- 创建测试主表. ID 是主键.
CREATE TABLE test_main (
id      INT,
value   VARCHAR(10),
PRIMARY KEY(id) 
);
-- 创建测试子表. 
CREATE TABLE test_sub (
id      INT,
main_id INT,
value   VARCHAR(10),
PRIMARY KEY(id) 
);
-- 插入测试主表数据.
INSERT INTO test_main(id, value) VALUES (1, 'ONE');
INSERT INTO test_main(id, value) VALUES (2, 'TWO');
-- 插入测试子表数据.
INSERT INTO test_sub(id, main_id, value) VALUES (1, 1, 'ONEONE');
INSERT INTO test_sub(id, main_id, value) VALUES (2, 2, 'TWOTWO');

默认外键约束方式

ALTER TABLE test_sub ADD CONSTRAINT main_id_cons FOREIGN KEY (main_id) REFERENCES test_main;

添加外键后，在删除数据（运行结果会提示错误，因为test_mian的数据已经给test_sub使用）

DELETE test_main WHERE ID = 1;

测试完毕后，删除外键约束

ALTER TABLE test_sub DROP CONSTRAINT main_id_cons;

DELETE CASCADE 方式

-- 创建外键(使用 ON DELETE CASCADE 选项，删除主表的时候，同时删除子表)
ALTER TABLE test_sub ADD CONSTRAINT main_id_cons
FOREIGN KEY (main_id) REFERENCES test_main ON DELETE CASCADE;

测试删除(删除成功后select查看2表运行结果)

DELETE test_main WHERE ID = 1;

测试完毕后，删除外键约束

ALTER TABLE test_sub DROP CONSTRAINT main_id_cons;

UPDATE CASCADE方式

--更新主表的主键时候，同时更新子表外键
ALTER TABLE test_sub
ADD CONSTRAINT main_id_cons
FOREIGN KEY (main_id) REFERENCES test_main ON UPDATE CASCADE;

SET NULL方式

-- 创建外键(使用 ON DELETE SET NULL 选项，删除主表的时候，同时将子表的 main_id 设置为 NULL)
ALTER TABLE test_sub
ADD CONSTRAINT main_id_cons
FOREIGN KEY (main_id) REFERENCES test_main ON DELETE SET NULL;

SET DEFAULT 方式

--为了更容易理解， SET DEFAULT 方式 使用下面这样的 测试表 与 测试数据. 
-- 创建测试主表. ID 是主键.
-- 班级表.
CREATE TABLE test_main_class (
  id      INT   NOT NULL,
  value   VARCHAR(20),
  PRIMARY KEY(id)  
);


-- 创建测试子表. 
-- 学生表.
CREATE TABLE test_sub_student (
  id      INT  NOT NULL,
  main_id INT  DEFAULT 0,
  value   VARCHAR(10),
  PRIMARY KEY(id)  
);


-- 插入测试主表数据.
INSERT INTO test_main_class(id, value) VALUES (0, '暂无班级');
INSERT INTO test_main_class(id, value) VALUES (1, '2013级1班');
INSERT INTO test_main_class(id, value) VALUES (2, '2013级2班');


-- 插入测试子表数据.
INSERT INTO test_sub_student(id, main_id, value) VALUES (1, 1, '张三');
INSERT INTO test_sub_student(id, main_id, value) VALUES (2, 2, '李四');

--首先测试 ON DELETE SET DEFAULT
ALTER TABLE test_sub_student
ADD CONSTRAINT fk_main_class
FOREIGN KEY (main_id)  REFERENCES  test_main_class ON DELETE SET DEFAULT;
SELECT
test_main_class.value AS "班级",
test_sub_student.value AS "学生"
FROM
test_main_class
JOIN test_sub_student
ON(test_main_class.id = test_sub_student.main_id);

--班级                   学生
-------------------- ----------
--2013级1班              张三
--2013级2班              李四

DELETE FROM test_main_class WHERE ID = 1;

SELECT
test_main_class.value AS "班级",
test_sub_student.value AS "学生"
FROM
test_main_class
JOIN test_sub_student
ON(test_main_class.id = test_sub_student.main_id);

--班级                   学生
-------------------- ----------
--暂无班级               张三
--2013级2班              李四

ON DELETE SET DEFAULT
指定如果试图删除某一行，
而该行的键被其他表的现有行中的外键所引用，
则组成被引用行中的外键的所有值将被设置为它们的默认值。
为了执行此约束，目标表的所有外键列必须具有默认定义。
如果某个列可为空值，并且未设置显式的默认值，则将使用 NULL 作为该列的隐式默认值。
因 ON DELETE SET DEFAULT 而设置的任何非空值在主表中必须有对应的值，才能维护外键约束的有效性。

--然后测试 ON UPDATE SET DEFAULT
--删除前面的外键约束.
ALTER TABLE test_sub_student DROP CONSTRAINT fk_main_class; 
-- 创建外键约束.
ALTER TABLE test_sub_student
ADD CONSTRAINT fk_main_class
FOREIGN KEY (main_id)  REFERENCES  test_main_class ON UPDATE SET DEFAULT;

UPDATE test_main_class SET ID = 20 WHERE ID = 2;


SELECT
test_main_class.value AS "班级",
test_sub_student.value AS "学生"
FROM
test_main_class
JOIN test_sub_student
ON(test_main_class.id = test_sub_student.main_id);
--班级                     学生
-------------------- ----------
--暂无班级                 张三
--暂无班级                 李四

ON UPDATE SET DEFAULT
指定如果试图更新某一行，
而该行的键被其他表的现有行中的外键所引用，
则组成被引用行中的外键的所有值将被设置为它们的默认值。
为了执行此约束，目标表的所有外键列必须具有默认定义。
如果某个列可为空值，并且未设置显式的默认值，则将使用 NULL 作为该列的隐式默认值。
因 ON UPDATE SET DEFAULT 而设置的任何非空值在主表中必须有对应的值，才能维护外键约束的有效性。