更改表格中的列类型 [英] Change column type in table

问题描述

我在Oracle中有一个名为Deal的数据表，其中有四列:
DealID:(PK)
LegID
OrigID
说明

I have a datatable called deal in Oracle with four columns:
DealID: (PK)
LegID
OrigID
Description

问题是，如果我想插入一个带有description = A的交易，则LegID和OrigID属性必须是唯一的，否则就没有问题.我怎样做这张支票?插入后，我曾想到一个触发器.还有更多解决方案吗?

The problem is that if I want to insert a deal with description = A, the attributes LegID and OrigID must be unique, otherwise, there is not problem. How can i make this check? I had thought a trigger after insert. There are more solutions?

提前谢谢！

推荐答案

您需要基于函数的唯一索引:

You need a function based unique index :

create table tt (
  DealID number(10) primary key,
  LegID number(10),
  OrigID number(10),
  Description varchar2(200 char)
);
create unique index tt_leg_orig_dscr_uk on tt (
  case when description = 'A' then description end,
  case when description = 'A' then legid end,
  case when description = 'A' then origid end
);

insert into tt values (1, 1, 1, 'A');
1 row(s) inserted.

insert into tt values (2, 1, 1, 'A');
ORA-00001: unique constraint (XXXXX.TT_LEG_ORIG_DSCR_UK) violated

insert into tt values (2, 1, 2, 'A');
1 row(s) inserted.

select * from tt;
DEALID  LEGID   ORIGID  DESCRIPTION
-----------------------------------
    1       1        1           A
    2       1        2           A
2 rows returned in 0.01 seconds

insert into tt values (3, 1, 1, 'B');
1 row(s) inserted.

insert into tt values (4, 1, 1, 'B');
1 row(s) inserted.

select * from tt order by 1;

DEALID  LEGID   ORIGID  DESCRIPTION
-----------------------------------
     1      1        1           A
     2      1        2           A
     3      1        1           B
     4      1        1           B
4 rows returned in 0.01 seconds 

如您所见，唯一索引仅适用于描述='A'的记录，它允许具有用于不同描述的非唯一记录.

As you can see, the unique index work only with records with description = 'A', It allows to have non-unique records for different descriptions.

这篇关于更改表格中的列类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！