ora2pg参数fkey_deferrable引出创建索引NOT VALID方式

最新推荐文章于 2023-03-13 13:23:11 发布

蜡津

最新推荐文章于 2023-03-13 13:23:11 发布

阅读量723

点赞数

分类专栏： ora2pg 文章标签：数据库 ora2pg postgresql

本文链接：https://blog.csdn.net/weixin_43084715/article/details/128460189

版权

ora2pg 专栏收录该内容

4 篇文章

订阅专栏

先来看一下官网上对fkey_deferrable参数的解释

FKEY_DEFERRABLE

When exporting tables, Ora2Pg normally exports constraints as they are, if they are non-deferrable they are exported as non-deferrable. However, non-deferrable constraints will probably cause problems when attempting to import data to Pg. The FKEY_DEFERRABLE option set to 1 will cause all foreign key constraints to be exported as deferrable.

当导出表时，Ora2Pg通常按原样导出约束，如果它们不可延迟，则按不可延迟导出。然而，当试图将数据导入Pg时，不可延迟约束可能会导致问题。将fkey_deferable选项设置为1将导致所有外键约束都被导出为可延迟约束。

ora2pg导出外键，创建外键的方式如下

ALTER TABLE banksecuacctrelation ADD CONSTRAINT fk_banksecu_rel_reg FOREIGN KEY (exchid,regid) REFERENCES registration(exchid,regid) ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE NOT VALID;

ctsdb=# \d banksecuacctrelation
Table "xc_test.banksecuacctrelation"
Column | Type | Collation | Nullable | Default
---------------+-----------------------+-----------+----------+---------
bankid | character varying(4) | | not null |
currencyid | character varying(2) | | not null |
bankacctid | character varying(30) | | not null |
exchid | character varying(1) | | not null |
regid | character varying(10) | | not null |
custid | character varying(12) | | not null |
acctid | character varying(12) | | not null |
customerfeeid | character varying(3) | | not null |
linkstatus | smallint | | not null |
rightownflag | smallint | | not null |
Indexes:
"banksecuacctrelation_pkey" PRIMARY KEY, btree (bankid, exchid, regid)
"banksecuacctrelation_custid_idx" btree (custid)
Foreign-key constraints:
"fk_banksecu_rel_reg" FOREIGN KEY (exchid, regid) REFERENCES registration(exchid, regid) DEFERRABLE NOT VALID

经测试发现ALTER TABLE ADD CONSTRAINT的NOT VALID选项,它阻止了对现有数据的验证。NOT VALID选项主要是出于性能原因,以允许用户将验证推迟到以后。但它没有明确说明它不能用于保留旧(无效)数据。

postgres=# table client；
client_id | client_name | client_profession | client_qualification | client_salary
-----------+----------------+-------------------+----------------------+---------------
1 | Emma Hernandez | Web Designer | BTech | 25000
2 | Mia Clark | Software Engineer | BE | 20000
3 | Noah Rodriguez | Bussinessman | MBA | 50000
4 | Martha Brown | Doctor | MBBS | 75000
5 | James Luther | HR | MBA | 35000
6 | Maria Garcia | Astronaut | Msc | 100000
7 | Robert Smith | Software Tester | BTech | 30000
(7 rows)

postgres=# \d client
Table "public.client"
Column | Type | Collation | Nullable | Default
----------------------+-------------------+-----------+----------+---------
client_id | integer | | not null |
client_name | character varying | | not null |
client_profession | character varying | | not null |
client_qualification | character varying | | not null |
client_salary | integer | | |
Indexes:
"client_pkey" PRIMARY KEY, btree (client_id)
Statistics objects:
"public.statext_student" ON client_id, client_name, client_profession, client_qualification, client_salary FROM client

只有7条数据的时候可以执行非空约束

postgres=# alter table client ADD CONSTRAINT client_salary_valid check( client_salary is not null ) not valid;
ALTER TABLE

postgres=# \d client
Table "public.client"
Column | Type | Collation | Nullable | Default
----------------------+-------------------+-----------+----------+---------
client_id | integer | | not null |
client_name | character varying | | not null |
client_profession | character varying | | not null |
client_qualification | character varying | | not null |
client_salary | integer | | |
Indexes:
"client_pkey" PRIMARY KEY, btree (client_id)
Check constraints:
"client_salary_valid" CHECK (client_salary IS NOT NULL) NOT VALID
Statistics objects:
"public.statext_student" ON client_id, client_name, client_profession, client_qualification, client_salary FROM client

之后再插入数据，报错，提示client_salary列不能为null，也就是创建NOT VALID约束之后，对之后创建的不符合数据将报错

postgres=# insert into client values (8,'Hernandez','Designer','BTec',null);
ERROR: new row for relation "client" violates check constraint "client_salary_valid"
DETAIL: Failing row contains (8, Hernandez, Designer, BTec, null).

删掉约束后可执行数据插入

postgres=# ALTER TABLE client drop CONSTRAINT client_salary_valid;
ALTER TABLE
postgres=# insert into client values (8,'Hernandez','Designer','BTec',null);
INSERT 0 1

当前数据如下：client_id为8的数据client_salary列为null，但是插入再次创建not valid约束不会对之前的数据进行约束检查了

postgres=# table client ;
client_id | client_name | client_profession | client_qualification | client_salary
-----------+----------------+-------------------+----------------------+---------------
1 | Emma Hernandez | Web Designer | BTech | 25000
2 | Mia Clark | Software Engineer | BE | 20000
3 | Noah Rodriguez | Bussinessman | MBA | 50000
4 | Martha Brown | Doctor | MBBS | 75000
5 | James Luther | HR | MBA | 35000
6 | Maria Garcia | Astronaut | Msc | 100000
7 | Robert Smith | Software Tester | BTech | 30000
8 | Hernandez | Designer | BTec |
(8 rows)

postgres=# alter table client ADD CONSTRAINT client_salary_valid check( client_salary is not null ) not valid;
ALTER TABLE
postgres=# \d client
Table "public.client"
Column | Type | Collation | Nullable | Default
----------------------+-------------------+-----------+----------+---------
client_id | integer | | not null |
client_name | character varying | | not null |
client_profession | character varying | | not null |
client_qualification | character varying | | not null |
client_salary | integer | | |
Indexes:
"client_pkey" PRIMARY KEY, btree (client_id)
Check constraints:
"client_salary_valid" CHECK (client_salary IS NOT NULL) NOT VALID

接下来，您可以手动执行命令，告诉PostgreSQL验证约束，client_id为8的数据client_salary列为null再次违反约束。

postgres=# ALTER TABLE client VALIDATE CONSTRAINT client_salary_valid;
ERROR: check constraint "client_salary_valid" of relation "client" is violated by some row

结论：

1、执行not valid非空约束后，之后插入的数据不能为空，执行验证之后，之后再insert数据就会检查非空约束。

2、执行not valid非空约束后，如果之前存在违反约束的数据，不能验证约束，否则报错。

3、执行not valid非空约束后，不执行验证约束，对之前的数据无任何影响。（检查新行,但不保证现有数据）

使得PostgreSQL不会获得ACCESS EXCLUSIVE LOCK，而是SHARE UPDATE EXCLUSIVE，意味着读/写不受影响。