时间戳

首页 > 解决方案 > Phantom Table being created in Teradata

sql - Phantom Table being created in Teradata

问题描述

I'm using Teradata 16.20.05.01 to run the following script:

create table t1(v int not null);
create table t2(w int null);
alter table t1 add constraint pk primary key (v);
alter table t2 add constraint t2_fk foreign key (w) references t1 (v);

After adding the foreign key, I suddenly get one excess table in my schema:

select TableName, RequestText
from "DBC".Tables
where DatabaseName = 'test'
and (TableName like 't1%' or TableName like 't2%')

Output:

TableName |RequestText                                                           |
----------|----------------------------------------------------------------------|
t1        |alter table t1 add constraint pk primary key (v)                      |
t2        |create table t2(w int null)                                           |
T2_0      |alter table t2 add constraint t2_fk foreign key (w) references t1 (v) |

This is especially annoying when re-creating that foreign key:

alter table t2 drop constraint t2_fk;
alter table t2 add constraint t2_fk foreign key (w) references t1 (v);

Which isn't possible because of:

SQL Error [5303] [HY000]: [Teradata Database] [TeraJDBC 15.00.00.33] [Error 5303] [SQLState HY000] Error table 'TEST.t2_0' already exists.

Workaround:

The problem does not appear when using inline constraint definitions

create table t1(v int not null, constraint pk primary key (v));
create table t2(w int null, constraint t2_fk foreign key (w) references t1 (v));

Is this a known issue? Is there a reliable workaround?

标签： sqlteradata

解决方案

这是记录在案的行为，当您将外键添加到现有表时，会创建一个错误表，并且所有违反约束的行都将复制到其中。并且它不会在 ALTER 之后自动删除。

解决方法很简单：不要使用标准外键，您几乎找不到任何使用它的网站。切换到 Batch FK，即REFERENCES WITH CHECK OPTION，对请求级别（不是逐行）应用检查，或者切换到 Soft/Dummy FK，REFERENCES WITH NO CHECK OPTION它只是定义了约束而不强制执行它（您必须检查您的 PK/FK 违规无论如何加载脚本）。

推荐阅读