Error creating table with primary key on 2 columns

[Gandalf]

I am trying to create a SQLite table with a primary key covering 2 columns. My PHP code is:

 $db->exec('CREATE TABLE matters (
   cid NOT NULL INTEGER,
   mid NOT NULL INTEGER,
   mname TEXT,
   clcb INTEGER,
   odate TEXT,
   open INTEGER,
   PRIMARY KEY (cid, mid)
 )');

which seems to match what’s in the manual, but I get an error

SQLSTATE[HY000]: General error: 1 near “INTEGER”: syntax error

Am I missing something obvious?

[Mittineague]

It looks like the problem is with the constraint (not null) being before the datatype (integer).

[Gandalf]

Thanks, squire. I think I need new glasses (or a brain!)

[Mittineague]

My guess is you need only to get more used to “reading” the “flow chart” syntax documentation. I know it took me a while to understand it. eg.

https://www.sqlite.org/lang_createtable.html

[Gandalf]

Adding a primary key for 2 columns has given me another issue, when INSERTing, that the second part of the key doesn’t automatically get given a value.

On an INSERT, if the ROWID or INTEGER PRIMARY KEY column is not explicitly given a value, then it will be filled automatically with an unused integer, usually one more than the largest ROWID currently in use.

I was thinking of using AUTOINCREMENT but that seems to be advised against, so I’m wondering where I can get a value for the 2nd part of a 2 part key.

[Mittineague]

I’m not sure I understand what you mean by

@Gandalf

primary key for 2 columns

There can be only one PK, though that can be a composite key on more than one field.

Maybe each unique auto_increment ? Though TBH that doesn’t make much sense in any use case I can think of.

[SamA74]

@Gandalf

so I’m wondering where I can get a value for the 2nd part of a 2 part key.

I presume you are using a composite key for a reason. What does the second key column refer to?

[Gandalf]

The first column is client and the second job. Each client is unique and each job should be unique within each client.

Hope that makes sense.

[SamA74]

So I guess each job is unique, but a client may have several jobs and be repeated in the table and you don’t want the same clien/job pairing twice.
I’m thinking either job should be an AI PK and client ID just a foreign key and index, or this should be more than one table.
Normally I would use a composite key in a 2 col look-up table for many-to-many, but this looks like one-to-many.

@Gandalf

I was thinking of using AUTOINCREMENT but that seems to be advised against

I’m no database guru, hopfully one who is can clarify. But I’m thinking the point of a composite key is to ensure a unique pairing. If one column in the key is AI they will be unique anyway making the composite redundant.

[Mittineague]

I think I understand the issue, unfortunately I’m having trouble thinking of a solution.

There will be an unknown number of clients, each having an unknown number of jobs. What you are looking for is a way to have something like “nested auto increments” or a “multidimensional array”. When a new client is added they would get the next client id value. And when an existing client has a new job added, that would get the next job id value.

I’m thinking the job table should have a field that keys to the client table but I can’t think of an easy way to have the client table have a field that keys to the job table.

In other words (because both are unknown)
client.id = job.client_id
could be doable, but not
client.job_id = job.id

[m_hutley]

@Mittineague

I can’t think of an easy way to have the client table have a field that keys to the job table.

This would be the domain of a join table, usually.

What gandalf is roughly describing would be INSERT…SELECT. Something to the tune of

INSERT into `matters` (`mid`,`cid`,`other`,`columns`) SELECT MAX(`mid`)+1, cidvaluehere, "othervalue", "columnsvalue" FROM `matters` WHERE `cid` = cidvaluehere;

[Gandalf]

Thanks guys. It’s never a good idea to start something like this shortly before bedtime :yawn: and it doesn’t help that I thought I had an answer and went about it half-cock. Thanks for digging deep within my head to realise what I was actually trying to do.

I will make sure I’ve had plenty of coffee before trying your solution Marc.

[Gandalf]

It worked a dream - even without coffee - thanks. I thought it would be more tricky using prepared statements.

I’m puzzled by the syntax though. I have (simplified)

$query = 'INSERT into `matters`
  (`mid`, `cid`, `mname`)
  SELECT MAX(`mid`)+1, :cid, :mname
    FROM `matters` WHERE `cid` = :cid;';

where :mname appears to be part of the select clause, but it’s actually part of the insert clause, isn’t it?

I assume I can dispense with the idea of a primary key consisting of 2 columns?

[m_hutley]

So the key to solving that mental issue is to understand that you can issue the following command to your SQL server:

SELECT 1,2,3 FROM `matters`

and it will work - it will simply return rows containing the values 1, 2, and 3.

If you specify a literal (read: anything other than a field name), then it returns that literal.

So what we’re telling the system to do is to find the MAX value of the MID, and then the literal values for the rest of the fields. So the INSERT query sees a result that reads as a tuple that matches the number of columns specified, and simply uses those values.

This code will generate a need for a composite PK, which is what your question was trying to do I make no judgements on whether that’s the correct method (partially it depends on your other tables’ structure…) but this will generate a table that looks like

cid	mid	others
1	1	stuff
1	2	others
2	1	differentstuff

etc.

[DarthGuido]

@SamA74

So I guess each job is unique, but a client may have several jobs and be repeated in the table and you don’t want the same clien/job pairing twice.
I’m thinking either job should be an AI PK and client ID just a foreign key and index

This

[Gandalf]

Ah but autoincrement is not recommended in SQLite: https://sqlite.org/autoinc.html

[m_hutley]

@That Page

On an INSERT, if the ROWID or INTEGER PRIMARY KEY column is not explicitly given a value, then it will be filled automatically with an unused integer, usually one more than the largest ROWID currently in use. This is true regardless of whether or not the AUTOINCREMENT keyword is used.

So… it doesn’t want you to autoincrement, but does it automatically for you even if you DONT autoincrement. Because…logic. #4 is the real difference, i suppose…

I don’t… really know that there would be a significant difference if you’re only updating rows on a non-regular and non-intensive basis.

It also sounds like SQLite is running its own overhead by having a hidden automatic not-quite-autoincrementing field in every table. So… shrug

[Mittineague]

@Gandalf

autoincrement is not recommended

Interesting documentation, I’m not quite sure what to make of this:

the purpose of AUTOINCREMENT is to prevent the reuse of ROWIDs from previously deleted rows.
…
AUTOINCREMENT guarantees that automatically chosen ROWIDs will be increasing but not that they will be sequential.

My take is it’s not recommended because of resource use, though how much of an impact it might be is unclear (I imagine it’s insignificant until it isn’t)

AUTOINCREMENT keyword imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed.

The recommended is to have the key be a “rowid” that is pretty much an autoincrement except that ids of deleted rows can be reused.