search

Segfault-Inc / Multicorn

Data Access Library
https://multicorn.org/
PostgreSQL License
700 stars 145 forks source link

Composite keys #97

Open kotowick opened 9 years ago

kotowick commented 9 years ago

I am using Multicorn with Rails to interact with our MSSql DB.

Everything works perfectly, except for DELETING (yes, delete only) with tables that use composite key: primary key (key1, key2).

With Multicorn, we can only specify one primary key. I tried specifying the primary key as KEY1, and the foreign key as KEY2. However, when I remove an entry (DELETE FROM table WHERE key1='xxxx' and key2='yyyy'), it deletes all rows where key1='xxxx'.

Has anyone ran into this issue and has anyone found a fix?

oscardssmith commented 9 years ago

I had a similar situation (although using a custom fdw) where 2 columns together uniquely identified charges. My solution was to create a new column in PSQL that was equal to col1*2^32+col2. Assuming that both columns are ints, this makes it so the first 32 bits are the first column and the second 32 are the second column. Since this was handled at the data wrapper level, if you took this approach it would be fairly simple to separate the columns for comparisons against the database.