oracle sql中违反了PK [英] PK violated in oracle sql

问题描述

我想知道是否有人可以帮助我.我在sql中运行了整个程序，但它给了我这个错误:

I wonder if anyone can help me with this. I ran the whole thing in sql but its gives me this error:

错误报告:SQL错误:ORA-00001:违反了唯一约束(GAMES.ATHLETE_PK)00001. 00000-违反了唯一约束(％s.％s)"*原因:UPDATE或INSERT语句试图插入重复的键.对于以DBMS MAC模式配置的Trusted Oracle，您可能会看到如果重复的条目存在于不同级别，则显示此消息.*操作:取消唯一限制或不插入密钥.

Error report: SQL Error: ORA-00001: unique constraint (GAMES.ATHLETE_PK) violated 00001. 00000 - "unique constraint (%s.%s) violated" *Cause: An UPDATE or INSERT statement attempted to insert a duplicate key. For Trusted Oracle configured in DBMS MAC mode, you may see this message if a duplicate entry exists at a different level. *Action: Either remove the unique restriction or do not insert the key.

我想一次插入所有记录.并使用序列生成新的主键.我尝试一一插入，还可以.但是，如果我一次插入所有内容，这就是错误...

I want to insert all records at once in a single transaction. and also using sequences to generate new primary keys. I tried inserting one by one and its okay. But if i insert all at once, this is the error...

INSERT ALL 
    INTO athlete (athlete_no, athlete_name, athlete_birthdate, athlete_birthplace, athlete_born_country, athlete_gender, athlete_height, athlete_weight, athlete_team_country)
    VALUES (athlete_no_seq.nextval, 'Michael Phelps', to_date('1985-06-30','yyyy-mm-dd'), 'Towson', 'USA', 'M', 193, 88, 'USA')
    INTO athlete (athlete_no, athlete_name, athlete_birthdate, athlete_birthplace, athlete_born_country, athlete_gender, athlete_height, athlete_weight, athlete_team_country)
    VALUES (athlete_no_seq.nextval, 'Stephanie Rice', to_date('1988-06-17','yyyy-mm-dd'), 'Brisbane', 'AUS', 'F', 176, 67, 'AUS')
    INTO athlete (athlete_no, athlete_name, athlete_birthdate, athlete_birthplace, athlete_born_country, athlete_gender, athlete_height, athlete_weight, athlete_team_country)
    VALUES (athlete_no_seq.nextval, 'Rebecca Adlington', to_date('1989-02-17','yyyy-mm-dd'), 'Mansfield', 'GBR', 'F', 179, 870, 'GBR')
    INTO athlete (athlete_no, athlete_name, athlete_birthdate, athlete_birthplace, athlete_born_country, athlete_gender, athlete_height, athlete_weight, athlete_team_country)
    VALUES (athlete_no_seq.nextval, 'Lee Chong Wei', to_date('1982-10-21','yyyy-mm-dd'), 'Perak', 'MAS', 'M', 170, 60, 'MAS')
    INTO athlete (athlete_no, athlete_name, athlete_birthdate, athlete_birthplace, athlete_born_country, athlete_gender, athlete_height, athlete_weight, athlete_team_country)
    VALUES (athlete_no_seq.nextval, 'Lin Dan', to_date('1983-10-14','yyyy-mm-dd'), 'Fujian', 'CHN', 'M', 176, 68, 'CHN')
    INTO athlete (athlete_no, athlete_name, athlete_birthdate, athlete_birthplace, athlete_born_country, athlete_gender, athlete_height, athlete_weight, athlete_team_country)
    VALUES (athlete_no_seq.nextval, 'Peter Gade', to_date('1976-12-14','yyyy-mm-dd'), 'Aalborg', 'DEN', 'M', 183, 73, 'DEN')
INTO competes (athlete_no, discipline_code, sg_gameno)
    VALUES (athlete_no_seq.currval, (SELECT discipline_code FROM discipline where discipline_name = 'Swimming'), 30)
    INTO competes (athlete_no, discipline_code, sg_gameno)
    VALUES (athlete_no_seq.currval, (SELECT discipline_code FROM discipline where discipline_name = 'Swimming'), 30)
    INTO competes (athlete_no, discipline_code, sg_gameno)
    VALUES (athlete_no_seq.currval, (SELECT discipline_code FROM discipline where discipline_name = 'Swimming'), 30)
    INTO competes (athlete_no, discipline_code, sg_gameno)
    VALUES (athlete_no_seq.currval, (SELECT discipline_code FROM discipline where discipline_name = 'Swimming'), 30)
    INTO competes (athlete_no, discipline_code, sg_gameno)
    VALUES (athlete_no_seq.currval, (SELECT discipline_code FROM discipline where discipline_name = 'Swimming'), 30)
    INTO competes (athlete_no, discipline_code, sg_gameno)
    VALUES (athlete_no_seq.currval, (SELECT discipline_code FROM discipline where discipline_name = 'Swimming'), 30)
INTO venue(venue_no, venue_name, venue_location, venue_usedfrom, venueused_to, venue_seatingcapacity, venue_structure, venue_use)
    VALUES(venue_no_seq.nextval, 'Aquatics Centre', 'Olympics Park, East London', to_date('2012-07-28','yyyy-mm-dd'), to_date('2012-10-08','yyyy-mm-dd'), 17500, 'N', 'P')
    INTO venue(venue_no, venue_name, venue_location, venue_usedfrom, venueused_to, venue_seatingcapacity, venue_structure, venue_use)
    VALUES(venue_no_seq.nextval, 'Wembley Arena', 'North West London', to_date('2012-07-28','yyyy-,mm-dd'), to_date('2012-08-05','yyyy-mm-dd'), 6000, 'E', 'P')
SELECT * FROM dual;
commit;

推荐答案

发布的代码使用INSERT ALL语法在单个语句中插入多行.它尝试使用序列NEXTVAL为每一行生成唯一的标识符.

The posted code uses the INSERT ALL syntax to insert multiple rows in a single statement. It attempts to use sequence NEXTVAL to generate a unique identifier for each row.

文档非常清楚NEXTVAL的功能:

The documentation is quite clear on the functioning of NEXTVAL:

在单个SQL语句中，该语句包含对NEXTVAL的引用，Oracle将序列递增一次"

"Within a single SQL statement containing a reference to NEXTVAL, Oracle increments the sequence once"

因此，每个对NEXTVAL的调用都将返回序列中的相同值，因此该语句将发出ORA-00001.

So each of those calls to NEXTVAL will return the same value from the sequence, and so the statement hurls ORA-00001.

问题是OP的代码滥用了多表插入语法.它旨在将来自一组源数据的行分布到多个表中，或有条件地将这些行操纵到一个表中.无论哪种情况，都假定源数据已经具有主键.

The problem is that the OP's code is misusing the multi-table insert syntax. It is intended to distribute rows from one set of source data across several tables, or conditionally manipulate the rows into one table. In either case it assumes the source data already has a primary key.

有多种方法可以解决此问题，从对ID进行硬编码到使用多个单表插入语句.

There are various ways of working around this, from hardcoding the ID to using multiple single table insert statements.

这是用发布的数据填充这些表的一种方法:

Here is one way to populate these tables with the posted data:

insert into athlete (athlete_no, athlete_name, athlete_birthdate, athlete_birthplace, athlete_born_country, athlete_gender, athlete_height, athlete_weight, athlete_team_country)
select athlete_no_seq.nextval , nm, dt, pl, ctry, gn, ht, wt, tm
from (
    select 'Michael Phelps' nm, to_date('1985-06-30','yyyy-mm-dd') dt, 'Towson' pl, 'USA' ctry, 'M' gn, 193 ht, 88 wt, 'USA' tm from dual union all
    select 'Stephanie Rice', to_date('1988-06-17','yyyy-mm-dd'), 'Brisbane', 'AUS', 'F', 176, 67, 'AUS' from dual union all
    select 'Rebecca Adlington', to_date('1989-02-17','yyyy-mm-dd'), 'Mansfield', 'GBR', 'F', 179, 870, 'GBR' from dual union all
    select 'Lee Chong Wei', to_date('1982-10-21','yyyy-mm-dd'), 'Perak', 'MAS', 'M', 170, 60, 'MAS' from dual union all
    select 'Lin Dan', to_date('1983-10-14','yyyy-mm-dd'), 'Fujian', 'CHN', 'M', 176, 68, 'CHN' from dual union all
    select 'Peter Gade', to_date('1976-12-14','yyyy-mm-dd'), 'Aalborg', 'DEN', 'M', 183, 73, 'DEN' from dual
    )
/    

insert into competes (athlete_no, discipline_code, sg_gameno)
select ath.athlete_no, disc.discipline_code, 30
from athlete ath
     cross join discipline disc
where disc.discipline_name = 'Swimming'
/

insert into venue(venue_no, venue_name, venue_location, venue_usedfrom, venueused_to, venue_seatingcapacity, venue_structure, venue_use)
select venue_no_seq.nextval, nm, loc, dtf, dtt, cap, vs, vu
from (
    select 'Aquatics Centre' nm, 'Olympics Park, East London' loc, to_date('2012-07-28','yyyy-mm-dd') dtf, to_date('2012-10-08','yyyy-mm-dd') dtt, 17500 cap, 'N' vs, 'P' vu  from dual union all
    select 'Wembley Arena', 'North West London', to_date('2012-07-28','yyyy-,mm-dd'), to_date('2012-08-05','yyyy-mm-dd'), 6000, 'E', 'P'  from dual 
    )
/

这篇关于oracle sql中违反了PK的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！