0

I am trying to build a composite primary key for my tabels. They should also have a self incremented id. My problem is that when I use a composite primary key the ID becomes NULL (as seen in the pictures)

here it works as it should but no composite key

here the id is NULL no matter what.

I tried different synatxes and also key words like NOT NULL and AUTOINCREMENT but nothing seems to work.

Here is the code without composite key

mystr = "CREATE TABLE IF NOT EXISTS KM%s(id INTEGER PRIMARY KEY, date TEXT, client INTEGER)"%(month.replace('-',"))
print(mystr)
c.execute(mystr) #create a table
conn.commit()'''

Here is the code with COMPOSITE KEY

mystr = "CREATE TABLE IF NOT EXISTS KM%s(id INTEGER, date TEXT, client INTEGER, primary key (id, client)"%(month.replace('-',"))
print(mystr)
c.execute(mystr) #create a table
conn.commit()
Helenk
  • 1
  • 1
  • 2
    The only way to have an autoincremented column is to define it as `INTEGER PRIMARY KEY`. – forpas Oct 17 '22 at 14:01
  • [Why should I not upload images of code/data/errors when asking a question?](https://meta.stackoverflow.com/q/285551/3404097) – philipxy Oct 17 '22 at 18:26

1 Answers1

0

I was sure that I'd used autoincremented integer columns in the past which were not primary keys, but it certainly doesn't work today with SQLite.

I must echo what @forpas has already said in the comment that you just can't do that.

The solution would be to add the UNIQUE constraint to id and generate your ID programmatically as you go. You do not need to track your current maximum ID because you can simply ask SQLite what the max is:

SELECT MAX(id) FROM KM<month>;

Increment that value by 1 and include it in your INSERT INTO statement.

I'd like to offer a couple of tips:

Using two integers as your composite key is a bad idea. Take composite key 1315 for example. Is that client 315 with an ID of 1, client 15 with an ID of 13, or client 5 with an ID of 131? It's true that primary keys are just for searching and do not have to be unique in many cases, but using integers generally does not work well.

The second tip is not to create a new database table for each month. A very good rule is that identically-structured tables should be combined into a single table. In this case you would add a column called month (actually, it would be 'date' then you would search by month) and keep everything in one table, not one table per month.

Ian Moote
  • 851
  • 8
  • 15