OracleDataTable -> Constrained to be unique error.

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
zguven
Posts: 21
Joined: Mon 05 May 2008 19:39

OracleDataTable -> Constrained to be unique error.

Post by zguven » Mon 05 May 2008 19:53

I recieve this error:
Column 'TESTFIELD' is constrained to be unique error. Value '' is already present.

but value is NULL, not ''.

I use OracleDataTable. OraDirect.Net v 4.6, Oracle 10.2.01, VS 2005

Column constraint defined in oracle :
alter table TESTTABLE
add constraint UQ_TEST unique (TESTFIELD);

Best Regards.
Zeki Güven.

Alexey.mdr
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Post by Alexey.mdr » Tue 06 May 2008 11:25

Hello Zeki,

You are receiving this error because for Oracle server
NULL and '' (Empty string) are the same.

zguven
Posts: 21
Joined: Mon 05 May 2008 19:39

Post by zguven » Tue 06 May 2008 11:42

Problem with your OracleDataTable component. Its running in delphi with TOraQuery without any problem.

It's not a oracle problem.

Alexey.mdr
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Post by Alexey.mdr » Tue 06 May 2008 11:43

Please send me (alexeyman*crlab*com) a small test project to reproduce the problem.
It is desirable to use 'test' schema objects, otherwise include the
definition of your own database objects.
Do not use third party components.
If it is impossible for you to create the test project, send us a piece of
your code where the error occurs.

zguven
Posts: 21
Joined: Mon 05 May 2008 19:39

Post by zguven » Tue 06 May 2008 12:14

I send to you sample test project, sql code is below.

Code: Select all

create table TESTTABLE

(
  ID        NUMBER not null,
  TEST1     VARCHAR2(10),
  TEST2     VARCHAR2(10),
  TESTFIELD VARCHAR2(25)
);

alter table TESTTABLE
  add constraint PK_TESTTABLE primary key (ID);
alter table TESTTABLE
  add constraint UQ_TEST unique (TESTFIELD);

 
delete from TESTTABLE;
commit;
insert into TESTTABLE (ID, TEST1, TEST2, TESTFIELD)
values (1, 'test', 'wqe', '1');
insert into TESTTABLE (ID, TEST1, TEST2, TESTFIELD)
values (2, 'wqwqw', 'qwe', null);
insert into TESTTABLE (ID, TEST1, TEST2, TESTFIELD)
values (3, 'wqe', 'wq', '2');
insert into TESTTABLE (ID, TEST1, TEST2, TESTFIELD)
values (4, 'qwe', 'qw', null);
insert into TESTTABLE (ID, TEST1, TEST2, TESTFIELD)
values (5, 'wqe', 'we', '3');
insert into TESTTABLE (ID, TEST1, TEST2, TESTFIELD)
values (6, 'wqe', 'qw', null);
insert into TESTTABLE (ID, TEST1, TEST2, TESTFIELD)
values (7, 'wqe', 'eqw', '4');
insert into TESTTABLE (ID, TEST1, TEST2, TESTFIELD)
values (8, 'qwe', 'wq', null);
commit;

Alexey.mdr
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Post by Alexey.mdr » Tue 06 May 2008 14:16

OraDirect .NET follows standard ADO.NET architecture and raises such exception.
Standard Microsoft data provider for Oracle also raises an same exception.
You should either insert unique data or not enforce a constraint.

zguven
Posts: 21
Joined: Mon 05 May 2008 19:39

Post by zguven » Tue 06 May 2008 15:52

Alexey.mdr wrote:OraDirect .NET follows standard ADO.NET architecture and raises such exception.
Standard Microsoft data provider for Oracle also raises an same exception.
You should either insert unique data or not enforce a constraint.
I want to solve this problem. any way to disable this constraint.

Oracle running without exception, but driver not running :shock:

zguven
Posts: 21
Joined: Mon 05 May 2008 19:39

Post by zguven » Tue 06 May 2008 17:41

I remove constraint from Constraints property in the "Properties window". but its working properly only "Preview..." window.

Alexey.mdr
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Post by Alexey.mdr » Wed 07 May 2008 10:27

To solve the problem try setting IsUnique sub-property to false
in the Columns property of oracleDataTable1.
Hope it helps!

zguven
Posts: 21
Joined: Mon 05 May 2008 19:39

Post by zguven » Wed 07 May 2008 12:54

Thanks its work!

Alexey.mdr
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Post by Alexey.mdr » Thu 08 May 2008 09:28

That's great!
Feel free to contact us if you have any problems or concerns about the product.

Post Reply