System.Data.SQLite

Hi

I'm pretty green when it comes down to db relationships so please don't be mad at me if this is a dumb question, i swear i spent 2 days searching for some way to do this and i can't find anything useful, even on google.

I have two very simple tables, each has a primary key and a column that references to the other table's primary key (foreign key?). I need this second column to contain one or MORE primary keys of the other table. Something like this:

CREATE TABLE 'table1'
'ID' INTEGER PRIMARY KEY NOT NULL,
'Items' INTEGER REFERENCES 'table2'('ID'),
'Comment' NVARCHAR,
'CreatedOn' DATETIME

CREATE TABLE table2
'ID' INTEGER PRIMARY KEY NOT NULL,
'Object' INTEGER REFERENCES 'table1'('ID'),
'Comment' NVARCHAR,
'CreatedOn' DATETIME

Now i want to insert multiple IDs from table2 into Items of table1, vice versa i want to to be able to have multiple IDs from table1 in the Object column of table2. It was in fact a painful experience searching how to do this, i couldn't find a simple SQL insert statement that would have described how to assign multiple values to a single column. Let alone doing it using DbCommand.Parameters (which i fear, and as far as i could understand, is not possible anyway).

Can anyone be so kind and tell me how to do something of this kind, even using string manipulation? Would be nice if it still could retain some speed for bulk inserting

Thank you very much.

In short, you can't, that's not how SQL works.

If you really wanted to do something like this, you could store several values in a string and write a insert and update triggers to parse the string and verify that it is all valid integers and the integer values all reference existing rows, and update/delete triggers on the other table to make sure it doesn't break any existing links.  That would be a royal and bug-prone pain however, and also would probably slow down your bulk inserts more than you'd like.

There are two "normal" ways to solve your problem:

If each item in table2 goes with just one item in table1, then you move the association column into table2.

If it is a many-to-many (sounds like it from what you wrote), then you add a third table that has two columns, one for the id in table1, one for the id in table2.  With that you can associate any pair table1 and table2 rows by adding the id of each to a row in your new table.  Commonly you set a unique constraint on that table so that you cannot have duplicate pairs.

Finally, keep in mind that SQLite does not natively enforce foreign keys.  There is a utility that's part of the command line SQLite distribution that will parse all your foreign key constraints and create triggers to enforce them, however.