0

I'm having problems with my Java program. I'm using MS Access as a database and UCanAccess to connect to the database.

When I'm trying to insert a text into the database, I get an Exception:

net.ucanaccess.jdbc.UcanaccessSQLException: integrity constraint violation: unique constraint or index violation; ENTRIES_PRIMARYKEY table: ENTRIES

This is the SQL statement, that results in the exception:

"INSERT INTO Entries (Text, Title, Date, Time) VALUES"
                + "(\"" + text + "\", \"" + title + "\", \"" + date  + "\", \"" + time + "\");";

The primary keys of table Entries is (Title, Date). The information I'm inserting does not exist in the table.

I've made a System.out.println() containing the same string to make sure the variables contain the correct information, and they do.

Can someone tell me what I'm doing wrong?

Jákup
  • 74
  • 12
  • I am unable to recreate your issue as stated. Run `console.bat` or `console.sh` (as appropriate) from the folder containing the UCanAccess JAR file. When prompted, enter the full path to the Access database. Check the "Loaded Indexes:" section of the output and verify that you see `Primary Key on ENTRIES Columns: (TITLE,DATE)`. – Gord Thompson May 31 '15 at 16:32
  • It just says "Given file does not exist". I don't understand, I've even copy/pasted the directory and made sure that the filename is correct. I'm even looking at the file - it's right there! – Jákup May 31 '15 at 17:18
  • Are you including the .accdb or .mdb file extension, as appropriate? If you are working on a non-Windows machine are you sure that the full path, filename, and extension match exactly (case-sensitive)? – Gord Thompson May 31 '15 at 17:43
  • I'm working on a Windows machine, and I'm using the correct path/filename/extension.. But I'm not sure about the including. How so? Let me specify my problem: I tried inserting a row, and it worked. But when I tried to insert more rows, the problem occurred. I'm not sure why it worked before or why it doesn't work anymore. Where am I supposed to include the file? – Jákup Jun 01 '15 at 08:01

1 Answers1

1

(from comment to question:)

I tried inserting a row, and it worked. But when I tried to insert more rows, the problem occurred.

If the Primary Key for the table is (Title, Date) then the values in those two columns (fields) must be unique across all rows. That is, you can insert a row that looks like

Text        Title   Date        Time
----------  ------  ----------  --------
some text   Title1  2015-06-01  05:56:15

but if you try to insert another row like this

Text        Title   Date        Time
----------  ------  ----------  --------
other text  Title1  2015-06-01  06:15:44

the insert will fail because that combination of Title and Date already exists in the table. Yes, that row "doesn't exist" because the Text and Time values are different, but the Primary Key constraint doesn't care; it only looks at the Title and Date columns.

If you really need to insert that second row then you'll need to change the Primary Key for the table.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • I was using a different title, so there should be no constraint violation.. that's my problem, and I'm still stuck :/ – Jákup Jun 01 '15 at 13:17
  • 1
    @Jákup Then you'll need to provide us with a [Minimum, Complete and Verifiable Example](http://stackoverflow.com/help/mcve) to clearly demonstrate the issue. No offense, but I'm convinced that your code is simply not doing what you *think* it is doing. – Gord Thompson Jun 01 '15 at 15:00
  • I think I have figured out the problem. I do not, however, know the solution. Adding a new entry today, I see that it works. But adding one again adds to the same error. This looks like that the primary key is in fact ONLY the date. I've taken a screenshot of the design view of my database here: http://oi58.tinypic.com/xekjte.jpg In my previous examples, I've translated the attribute names, but it's the same order (text, title, date, time). In the picture, you can see that there are 2 keys and when I mouse over them, it says "The field is part of the table's Primary Key". – Jákup Jun 01 '15 at 21:36