#openGauss #入门 #安装 #数据库 #开源
知识来源：docs-opengauss.osinfra.cn/zh/
FOREIGN KEY
FOREIGN KEY即外键约束，指定列(或一组列)中的值必须匹配另一个表的某一行中出现的值。通常一个表中的FOREIGN KEY指向另一个表中的 UNIQUE KEY（唯一约束的键），即维护了两个相关表之间的引用完整性。
例如，创建表staff3，包含5个字段。
openGauss=# CREATE TABLE staff3(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
创建一张DEPARTMENT表，并添加3个字段，其中EMP_ID为外键，参照staff3的ID字段：
openGauss=# CREATE TABLE DEPARTMENT(
ID INT PRIMARY KEY NOT NULL,
DEPT CHAR(50) NOT NULL,
EMP_ID INT references staff3(ID)
);
FOREIGN Key在MySQL兼容性下，外键可以关联非唯一性索引。即一个表中的FOREIGN Key指向另一个表中的 Non-unique KEY（非唯一约束的键）。 注： 如果在MySQL兼容性下，定义外键指定ON UPDATE | DELETE CASCADE时，在非唯一性索引中，非唯一索引字段存在多个元组时，只要dml其中一行数据，则会触发外键表里关联的字段全部修改。但如果字段为NULL时，则不触发外键关联的字段做对应的修改。
MySQL兼容性需要安装dolphin插件才可生效。
openGauss=# create table t1(id int, name varchar);
CREATE TABLE
openGauss=# create table t2(id int, a_id int);
CREATE TABLE
-- create non-unique index on table t1.
openGauss=# create index a_index_1 on t1(id);
CREATE INDEX
-- create foreign key on non-unique index
openGauss=# alter table t2 add constraint t2_fk foreign key (a_id) references t1(id);
ALTER TABLE
openGauss=# \d t1
Table "public.t1"
Column | Type | Modifiers
--------+-------------------+-----------
id | integer |
name | character varying |
Indexes:
"a_index_1" btree (id) TABLESPACE pg_default
Referenced by:
TABLE "t2" CONSTRAINT "t2_fk" FOREIGN KEY (a_id) REFERENCES t1(id)
openGauss=# \d t2
Table "public.t2"
Column | Type | Modifiers
--------+---------+-----------
id | integer |
a_id | integer |
Foreign-key constraints:
"t2_fk" FOREIGN KEY (a_id) REFERENCES t1(id)
openGauss=# insert into t1 values(1,'a'),(2,'b');
INSERT 0 2
openGauss=# select * from t1;
id | name
----+------
1 | a
2 | b
(2 rows)
openGauss=# insert into t2 values(1,1);
INSERT 0 1
openGauss=# select * from t2;
id | a_id
----+------
1 | 1
(1 row)
openGauss=# insert into t2 values(1,3);
INSERT 0 1
ERROR: insert or update on table "t2" violates foreign key constraint "t2_fk"
DETAIL: Key (a_id)=(3) is not present in table "t1".
openGauss=# select * from t2;
id | a_id
----+------
1 | 1
(1 row)
openGauss=# alter table t2 drop constraint t2_fk;
ALTER TABLE
openGauss=# alter table t2 add constraint t2_fk foreign key (a_id) references t1(id) on update cascade;
ALTER TABLE
openGauss=# select * from t1;
id | name
----+------
1 | a
2 | b
(2 rows)
openGauss=# insert into t1 values(1,'s');
INSERT 0 1
openGauss=# select * from t1;
id | name
----+------
1 | a
2 | b
1 | s
(3 rows)
openGauss=# insert into t2 values(2,1);
INSERT 0 1
openGauss=# select * from t2;
id | a_id
----+------
1 | 1
2 | 1
(2 rows)
openGauss=# update t1 set id = 11 where name = 'a';
UPDATE 1
openGauss=# select * from t1;
id | name
----+------
2 | b
1 | s
11 | a
(3 rows)
openGauss=# select * from t2;
id | a_id
----+------
1 | 11
2 | 11
(2 rows)
openGauss=# update t1 set id =1 where name = 'a';
UPDATE 1
openGauss=# alter table t2 drop constraint t2_fk;
ALTER TABLE
openGauss=# alter table t2 add constraint t2_fk foreign key (a_id) references t1(id) on delete cascade;
ALTER TABLE
openGauss=# select * from t1;
id | name
----+------
2 | b
1 | s
1 | a
(3 rows)
openGauss=# select * from t2;
id | a_id
----+------
1 | 1
2 | 1
(2 rows)
openGauss=# delete from t1 where name = 's';
DELETE 1
openGauss=# select * from t1;
id | name
----+------
2 | b
1 | a
(2 rows)
openGauss=# select * from t2;
id | a_id
----+------
(0 rows)
#openGauss #入门 #安装 #数据库 #开源