You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Bob M <rg...@orcon.net.nz> on 2014/02/15 03:57:31 UTC
adding a record to a table
Hi
I have a table in which the key is the first two fields viz date and time
the last two records in the table are
2014-02-14 0 .......................
2014-02-14 6 ......................
When I try to add a new record
2014-02-14 12 ........................
I get Error code-1, SQL state 23505
Insert command failed: The statement was aborted because it would have
caused a duplicate key value in a unique or primary key constraint or unique
index indentified by 'SQL14021012215370' defined on 'table name'
What does this error mean ?
--
View this message in context: http://apache-database.10148.n7.nabble.com/adding-a-record-to-a-table-tp137120.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: adding a record to a table
Posted by Rick Hillegas <ri...@oracle.com>.
On 2/17/14 8:45 PM, Bob M wrote:
> Hi guys (John and Rick)
> Thank you for your considerations (even though they are a bit above my level
> of comprehension)
>
> I have added a constraint a follows:-
>
> CONSTRAINT tablekey_pk PRIMARY KEY(Trading Date, Trading_Time)
> however, lets step back a bit............after reading your suggested
> readings
>
> [A]
> 1) searching for derby.log on my C drive results in
> 2 shortcuts
> 4 text documents
> and some other odd things............
>
> The first text document is in C:\Databases dated 18/2/2014 1.17KB
> The second is in C:\Users\Stan\Downloads same date 1.05KB
> The third and fourth are dated 1 Dec 2013
> I don't understand why I have so many ?
>
> [B]
> derby.properties file
> I read that one has to provide this file oneself
> and yet I have one!
> it is located in C:\Users\Stan\.netbeans-derby
> In that folder is also
> (a) a subfolder called Weka Input
> derby.log dated 1 Dec 2013
> the derby.properties is dated 18/2/2014 and contains two commented lines - a
> heading and a date
> I have since added the extra line as John has suggested
>
> But, my question is - does this setup look like a mess to you?
Hi Bob,
I don't see anything alarming. It looks as though you have more than one
program which boots Derby. Typically, each program will have its own
customized properties file and each program will have its own derby.log.
The general rule is this: you need to create your own derby.properties
file in the directory where you boot your program, and that's the same
directory where you'll find your program-specific derby.log.
Hope this helps,
-Rick
>
>
> --
> View this message in context: http://apache-database.10148.n7.nabble.com/adding-a-record-to-a-table-tp137120p137177.html
> Sent from the Apache Derby Users mailing list archive at Nabble.com.
>
Re: adding a record to a table
Posted by Bob M <rg...@orcon.net.nz>.
Hi guys (John and Rick)
Thank you for your considerations (even though they are a bit above my level
of comprehension)
I have added a constraint a follows:-
CONSTRAINT tablekey_pk PRIMARY KEY(Trading Date, Trading_Time)
however, lets step back a bit............after reading your suggested
readings
[A]
1) searching for derby.log on my C drive results in
2 shortcuts
4 text documents
and some other odd things............
The first text document is in C:\Databases dated 18/2/2014 1.17KB
The second is in C:\Users\Stan\Downloads same date 1.05KB
The third and fourth are dated 1 Dec 2013
I don't understand why I have so many ?
[B]
derby.properties file
I read that one has to provide this file oneself
and yet I have one!
it is located in C:\Users\Stan\.netbeans-derby
In that folder is also
(a) a subfolder called Weka Input
derby.log dated 1 Dec 2013
the derby.properties is dated 18/2/2014 and contains two commented lines - a
heading and a date
I have since added the extra line as John has suggested
But, my question is - does this setup look like a mess to you?
--
View this message in context: http://apache-database.10148.n7.nabble.com/adding-a-record-to-a-table-tp137120p137177.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: adding a record to a table
Posted by Rick Hillegas <ri...@oracle.com>.
On 2/17/14 2:03 AM, John English wrote:
> On 17/02/2014 11:29, Bob M wrote:
>> Hi John
>>
>> I am having difficulty following your latest message.........sorry :(
>>
>> I don't understand where to give constraints names?
>> Is it when I set up the table?
>
> Yes, replace PRIMARY KEY(x) with CONSTRAINT c PRIMARY KEY(x) in the
> table definition. See
> http://db.apache.org/derby/docs/10.1/ref/rrefsqlj42154.html
>
>> derby.properties - I have no idea where / what that is?
>
> Create a file in the startup directory (where derby.log should also
> be) containing this line:
>
> derby.language.logStatementText=true
>
> See http://db.apache.org/derby/docs/10.1/tuning/ctunsetprop13074.html
>
> HTH,
If you haven't named a constraint, then its system-generated name should
be the same as the name of the system-generated index which backs the
constraint. You should be able to use DatabaseMetaData.getIndexInfo() to
retrieve enough information to figure out which one is being violated.
The following script shows how to do this:
connect 'jdbc:derby:memory:db;create=true';
create table t1( z int primary key );
create table t2( x int not null unique, y int primary key, z int
references t1( z ) );
call syscs_util.syscs_register_tool( 'databaseMetaData', true );
select substr( index_name, 1, 36 ), substr( column_name, 1, 36 ),
ordinal_position
from table( getIndexInfo( null, 'APP', 'T2', false, false ) ) s
order by index_name, ordinal_position;
call syscs_util.syscs_register_tool( 'databaseMetaData', false );
Hope this helps,
-Rick
Re: adding a record to a table
Posted by John English <jo...@gmail.com>.
On 17/02/2014 11:29, Bob M wrote:
> Hi John
>
> I am having difficulty following your latest message.........sorry :(
>
> I don't understand where to give constraints names?
> Is it when I set up the table?
Yes, replace PRIMARY KEY(x) with CONSTRAINT c PRIMARY KEY(x) in the table
definition. See http://db.apache.org/derby/docs/10.1/ref/rrefsqlj42154.html
> derby.properties - I have no idea where / what that is?
Create a file in the startup directory (where derby.log should also be)
containing this line:
derby.language.logStatementText=true
See http://db.apache.org/derby/docs/10.1/tuning/ctunsetprop13074.html
HTH,
--
John English
Re: adding a record to a table
Posted by Bob M <rg...@orcon.net.nz>.
Hi John
I am having difficulty following your latest message.........sorry :(
I don't understand where to give constraints names?
Is it when I set up the table?
derby.properties - I have no idea where / what that is?
I am using netbeans IDE to view and modify the odd record from time to time
I connect to the database
view data
and attempt to add a new record with a new key which is NOT a duplicate
all the other fields are straight forward
Bob M
--
View this message in context: http://apache-database.10148.n7.nabble.com/adding-a-record-to-a-table-tp137120p137136.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: adding a record to a table
Posted by John English <jo...@gmail.com>.
On 15/02/2014 22:05, Bob M wrote:
> Hello John
>
> Table Specs:-
> //+ "PRIMARY KEY (Trading_Date, Trading_Time)");
>
> Does this help?
It's still not clear if this is the constraint being violated. The data looks
OK, assuming that the table does not already contain the key (2014-02-14,12)
which I assume was the first thing you would have checked.
It's usually better to give names to constraints, e.g.
CONSTRAINT xxx_pk PRIMARY KEY (Trading_Date, Trading_Time)
so that the error message refers to xxx_pk rather than SQL14021012215370. It's
always possible that some other contraint is being violated (e.g. by a trigger
which is fired by the insert). Why not try naming your constraints and see if
that tells you more about what's happening? You can drop the existing
constraints and then add them back with names using ALTER TABLE.
You can also set derby.language.logStatementText=true in derby.properties for
even more information.
HTH,
--
John English
Re: adding a record to a table
Posted by Bob M <rg...@orcon.net.nz>.
Hello John
Table Specs:-
//s.execute("CREATE TABLE xxx_xxx(Trading_Date DATE NOT NULL, Trading_Time
INT NOT NULL,"
//+ "Open_price DECIMAL(6,3), High_price DECIMAL(6,3),"
//+ "Low_price DECIMAL(6,3), Close_price DECIMAL(6,3),"
//+ "DOW_numeric INT, DOW_nominal VARCHAR(9), Hour_numeric INT,"
//+ "Hour_nominal VARCHAR(8), MA2 DECIMAL(8,7),"
//+ "MA6 DECIMAL(8,7), MA10 DECIMAL(8,7),"
//+ "Return_current DECIMAL(8,7), Class_current VARCHAR(4),"
//+ "Class1_predicted VARCHAR(4), Class2_predicted VARCHAR(4),"
//+ "Class3_predicted VARCHAR(4), Class4_predicted VARCHAR(4),"
//+ "Class5_predicted VARCHAR(4), Return6_predicted DECIMAL(8,7),"
//+ "Class6_predicted VARCHAR(4), Return7_predicted DECIMAL(8,7),"
//+ "Class7_predicted VARCHAR(4), Class_predicted VARCHAR(4),"
//+ "Trade_ID VARCHAR(30), Profilt_Loss DECIMAL(5,2),"
//+ "PRIMARY KEY (Trading_Date, Trading_Time)");
Does this help?
--
View this message in context: http://apache-database.10148.n7.nabble.com/adding-a-record-to-a-table-tp137120p137127.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: adding a record to a table
Posted by John English <jo...@gmail.com>.
On 15/02/2014 04:57, Bob M wrote:
> Hi
>
> I have a table in which the key is the first two fields viz date and time
> the last two records in the table are
> 2014-02-14 0 .......................
> 2014-02-14 6 ......................
>
> When I try to add a new record
>
> 2014-02-14 12 ........................
>
> I get Error code-1, SQL state 23505
> Insert command failed: The statement was aborted because it would have
> caused a duplicate key value in a unique or primary key constraint or unique
> index indentified by 'SQL14021012215370' defined on 'table name'
>
> What does this error mean ?
Naming your constraints is a good idea, since then you can see exactly what is
being violated. It could be that the constraint that is causing the problem is
not the primary key in this table but something completely different. Without
seeing the table spec and the failing query, it's hard to tell from here.
--
John English