CODEWITHSUNDEEP
rubysql-serveractiverecord
Sergey Bezugliy
Sergey Bezugliy

Reputation: 672

How to use ActiveRecord (without Rails) with database that doesn't have primary keys

I now writing automation program for system based on MSSQL Server, using Ruby 1.9.3 and ActiveRecord 3.1.6 without Rails.

Tables have nonstandard ids (stk_id, urb_id, instead of id), also some tables haven't primary keys.

Before I added column id and set it as primary key my program worked very slowly. I waited nearly 3 minutes while the program makes two operations of selection and some little processing in table with 9000 records. But when I added column id and set it as primary key, these operations were finished in less then 10 secs.

Yet another problem I found in deletion operation: it doesn't work at all without primary key in table. Error when trying to delete without primary key:

undefined method `to_sym' for nil:NilClass

I can't modify the table structure of the production database. Maybe someone knows how to solve this problem without adding id columns and setting primary keys?

Upvotes: 0

Views: 490

Answers (3)

Klaus
Klaus

Reputation: 902

Remark: A database without primary keys is BAD !

http://www.helium.com/items/1539899-why-a-relational-database-needs-a-primary-key

Using nonstandard keys is no problem, just use self.primary_key = "stk_id"

You may also use composite_primary_keys: https://github.com/drnic/composite_primary_keys

Upvotes: 1

Jack Christensen
Jack Christensen

Reputation: 112

I suggest looking into using Sequel instead of ActiveRecord. It is not opinionated about the database schema like ActiveRecord is and may be easier to use with a database schema you can't modify.

Upvotes: 1

JeffO
JeffO

Reputation: 8043

Create indexed views on each of the tables with no primary key. A unique clustered index as well as other indexes as needed can be applied. Including a single table in the view should prevent you from violating the many conditions an indexed view requires/prohibits.

Upvotes: 1

Related Questions