OracleDataTable -> Constrained to be unique error.

OracleDataTable -> Constrained to be unique error.

Postby 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.
zguven
 
Posts: 21
Joined: Mon 05 May 2008 19:39

Postby 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.
Alexey.mdr
 
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Postby 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.
zguven
 
Posts: 21
Joined: Mon 05 May 2008 19:39

Postby 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.
Alexey.mdr
 
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Postby 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;
zguven
 
Posts: 21
Joined: Mon 05 May 2008 19:39

Postby 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.
Alexey.mdr
 
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Postby 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

Postby zguven » Tue 06 May 2008 17:41

I remove constraint from Constraints property in the "Properties window". but its working properly only "Preview..." window.
zguven
 
Posts: 21
Joined: Mon 05 May 2008 19:39

Postby 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!
Alexey.mdr
 
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Postby zguven » Wed 07 May 2008 12:54

Thanks its work!
zguven
 
Posts: 21
Joined: Mon 05 May 2008 19:39

Postby 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.
Alexey.mdr
 
Posts: 729
Joined: Thu 13 Dec 2007 10:24


Return to dotConnect for Oracle