Hi All,
When saving a record to a table that doesn’t use primary key, the
adapater raises
ActiveRecord::StatementInvalid: PGError: ERROR: column “id” does not
exist
That’s because the postgres adapter does,
INSERT INTO … RETURNING “id”
And “id” doesn’t exist for a table that doesn’t ues it.
The offending block of code is from
PostgreSQLAdapter#insert(sql, name = nil, pk = nil, id_value = nil,
sequence_name = nil)
if supports_insert_with_returning?
pk, sequence_name = *pk_and_sequence_for(table) unless pk
if pk
id = select_value("#{sql} RETURNING #{quote_column_name(pk)}")
clear_query_cache
return id
end
end
The problem is that ActiveRecord::Base.create
would pass in the primary_key “id” for the
parameter pk even though the ActiveRecord class
doesn’t have a primary key.
this would work
if supports_insert_with_returning?
this would work
pk, sequence_name = *pk_and_sequence_for(table)
if pk
id = select_value("#{sql} RETURNING #{quote_column_name(pk)}")
clear_query_cache
return id
end
end
Or is there a way to set the primary key to nil?
class Foo < ActiveRecord::Base
set_primary_key nil
primary_key # => “”
end
Howard
I think you want to be using the gem “pg” as your postgresql adapter,
not “postgres”.
gem list pg -r
*** REMOTE GEMS ***
pg (0.8.0)
This adapter definitely supports tables without a primary key -
typical join table used for habtm models. For instance the following
migration works to provide role <-> user join. This works with
ruby1.8.7 or 1.9.1 and rails 2.3.0-2.3.4, postgres version 8.3.7.
NNNN_create_role_users.rb
class CreateRoleUsers < ActiveRecord::Migration
def self.up
create_table :roles_users, :id => false do |t|
t.integer :role_id
t.integer :user_id
end
end
models/role.rb
class Role < ActiveRecord::Base
has_and_belongs_to_many :users
models/user.rb
class User < ActiveRecord::Base
has_and_belongs_to_many :roles
Howard Y. wrote:
Hi All,
When saving a record to a table that doesn’t use primary key, the
adapater raises
ActiveRecord::StatementInvalid: PGError: ERROR: column “id” does not
exist
[…]
The problem is that ActiveRecord::Base.create
would pass in the primary_key “id” for the
parameter pk even though the ActiveRecord class
doesn’t have a primary key.
When would you ever want to use an AR model without a primary key? The
only case I can think of where Rails doesn’t want a primary key is on
habtm join tables, which don’t have an associated model class.
[…]
Or is there a way to set the primary key to nil?
class Foo < ActiveRecord::Base
set_primary_key nil
primary_key # => “”
end
Seems like a really bad idea. Without a primary key, there is no way to
uniquely identify a record. Why do you want to do this?
Howard
Best,
Marnen Laibow-Koser
http://www.marnen.org
[email protected]
On Sep 16, 6:48 pm, Marnen Laibow-Koser <rails-mailing-l…@andreas-
s.net> wrote:
The problem is that ActiveRecord::Base.create
would pass in the primary_key “id” for the
parameter pk even though the ActiveRecord class
doesn’t have a primary key.
When would you ever want to use an AR model without a primary key? The
only case I can think of where Rails doesn’t want a primary key is on
habtm join tables, which don’t have an associated model class.
The particular case is for a has_many association, but I want to avoid
the overhead of having two indices.
It’ll be insertion only, so duplicate foreign key is acceptable.
The association has the bag semantics.
If this doesn’t work, what’s “create_table :id => false” ever used
for?
On Sep 17, 2:13 pm, Marnen Laibow-Koser <rails-mailing-l…@andreas-
s.net> wrote:
The association has the bag semantics.
I’m not familiar with that term. What do you mean?
It just means under a key there can be many items. That’s exactly what
I want, and it seems silly to have a primary key I don’t use. But
nevermind that, if rails insists, so be it.
Howard Y. wrote:
[…]
The particular case is for a has_many association, but I want to avoid
the overhead of having two indices.
That strikes me as a foolish premature optimization, rather like saying
that you don’t want headlights on your car because they take too much
power from the engine. 
It’ll be insertion only, so duplicate foreign key is acceptable.
Well, of course duplicate foreign keys would be acceptable. But what do
you mean by “insertion only”? Do you mean you’ll never be reading from
the table? If so, why have it?
The association has the bag semantics.
I’m not familiar with that term. What do you mean?
If this doesn’t work, what’s “create_table :id => false” ever used
for?
For tables without AR models. In a typical Rails app, that would only
be habtm join tables.
AR wants a primary key, and in any case, it’s poor practice not to have
one, even if it’s composite.
Best,
Marnen Laibow-Koser
http://www.marnen.org
[email protected]
Rails doesn’t insist - it’s only Marnen.
Rick Lloyd wrote:
Rails doesn’t insist - it’s only Marnen.
What is that supposed to mean?
Best,
Marnen Laibow-Koser
http://www.marnen.org
[email protected]
So the original question was:
Is it possible to have a table without a primary key?
Two answers were given:
Yes it’s possible and allowed and occasionally useful. Here’s how
it’s
done.
Why would you ever want to do that? It flies in the face of All
That’s
Rails. You’ll be sorry if you do that.
That’s what I meant by that.
On Sep 19, 10:10 am, Marnen Laibow-Koser <rails-mailing-l…@andreas-
Howard Y. wrote:
On Sep 17, 2:13 pm, Marnen Laibow-Koser <rails-mailing-l…@andreas-
s.net> wrote:
The association has the bag semantics.
I’m not familiar with that term. What do you mean?
It just means under a key there can be many items.
Yeah, I looked up the term after I responded.
That’s exactly what
I want, and it seems silly to have a primary key I don’t use.
But you will be using the primary key, I think. How will you be using
the data in this table?
But
nevermind that, if rails insists, so be it.
Well, it certainly won’t hurt anything, and it will make database
administration a lot easier, in addition to making Rails happier.
Best,
Marnen Laibow-Koser
http://www.marnen.org
[email protected]
We have 30-40 tables in a legacy db with no PKs. These tables hold
various logging events and may be searched by a given day, a given
user id, a given event, etc., but not by a particular row id. So there
are reasonable reasons to not have PKs on all tables. Unfortunately,
AR really like PKs…
If I set_primary_key nil I can’t do a create (I get an interning empty
string error). If I set the PK to an arbitrary integer column then
that column is set to NULL (on an insert), despite its value being
passed in the attributes hash… I don’t yet have a solution.
Jeff
On Sep 16, 6:48 pm, Marnen Laibow-Koser <rails-mailing-l…@andreas-
I tried the composite_primary_keys (2.3.2) plugin, with the same
overall result: I cannot insert a row (via Postgres) into a table
without a PK. My model now has this line:
set_primary_keys :doc_id, :user_id, :view_date, :view_time #
legacy tbl, include all cols
My insert error is thus:
Mcolumn “doc_id,user_id,view_date,view_time” does not exist
P170
Fparse_expr.c
L421
RtransformColumnRef: INSERT INTO “document_ref_access” (“doc_id”,
“user_id”,
“view_date”, “view_time”) VALUES (1, E’W:5:a_name’, ‘2009-10-19’,
‘2009-10-1
9 14:52:11.605227’) RETURNING “doc_id,user_id,view_date,view_time”)
The RETURNING clause seems to be causing the problem. If I set the PK
to, say, :doc_id (mind you this col is NOT a PK for this table) the
insert works but the doc_id col is set to NULL. Any other suggestions?
Thanks,
Jeff
On Oct 16, 12:32 pm, Marnen Laibow-Koser <rails-mailing-l…@andreas-
Solved (at least for now):
I used the composite primary keys plugin but listed only a single (and
fake) primary key:
set_primary_keys :doc_id # note plural!
:doc_id is not a PK, and for this logging table it’s possible there is
no combination of columns that will yield a single row, depending on
the timing of multi- inserts. But the insert now works and I can move
on!
Jeff
JeffV wrote:
We have 30-40 tables in a legacy db with no PKs. These tables hold
various logging events and may be searched by a given day, a given
user id, a given event, etc., but not by a particular row id. So there
are reasonable reasons to not have PKs on all tables.
I think you’re confusing the concept of a surrogate PK with a PK in
general. Sure, in the case of your logging tables, it’s probably
reasonable not to have a separate Rails-style id field (surrogate PK).
But it is advisable – I’d even say essential – to find some existing
field or combination of fields that can be guaranteed unique for each
record, and to declare them as a (perhaps composite) PK.
Unfortunately,
AR really like PKs…
And so it should. If you don’t want a surrogate PK, then declare some
other field the PK. The composite_primary_keys plugin may help here. I
maintain – and I believe big-name DB gurus would say likewise – that a
table without some sort of PK is virtually always a Bad Thing, with or
without Rails. .
If I set_primary_key nil I can’t do a create (I get an interning empty
string error). If I set the PK to an arbitrary integer column then
that column is set to NULL (on an insert), despite its value being
passed in the attributes hash… I don’t yet have a solution.
See what composite_primary_keys will do for you.
Jeff
On Sep 16, 6:48�pm, Marnen Laibow-Koser <rails-mailing-l…@andreas-
Best,
Marnen Laibow-Koser
http://www.marnen.org
[email protected]
JeffV wrote:
Solved (at least for now):
I used the composite primary keys plugin but listed only a single (and
fake) primary key:
set_primary_keys :doc_id # note plural!
:doc_id is not a PK, and for this logging table it’s possible there is
no combination of columns that will yield a single row, depending on
the timing of multi- inserts. But the insert now works and I can move
on!
Great! Now declare doc_id as the PK in the DB.
Jeff
Best,
Marnen Laibow-Koser
http://www.marnen.org
[email protected]
doc_id is not a PK. An accurate model of the table would be:
set_primary_key nil
But the PG postgres adapter is unhappy with that because it’s
generating something like:
INSERT into the_table VALUES (val1, val2…) RETURNING pk
when pk is nil the postgres adapter is unhappy.
For some reason the composite_primary_keys plugin fixes the problem
(as only a single PK is specified).
To me the problem is the PG adapter. I don’t know why it uses
RETURNING in its INSERT statement.
Jeff
On Oct 20, 9:14 am, Marnen Laibow-Koser <rails-mailing-l…@andreas-
JeffV wrote:
doc_id is not a PK. An accurate model of the table would be:
set_primary_key nil
Again: determine a suitable unique index (potentially composite, say,
doc_id and something else) and declare that as your primary key in the
DB. This will have many advantages and virtually no downside AFAIK.
But the PG postgres adapter is unhappy with that because it’s
generating something like:
INSERT into the_table VALUES (val1, val2…) RETURNING pk
when pk is nil the postgres adapter is unhappy.
For some reason the composite_primary_keys plugin fixes the problem
(as only a single PK is specified).
To me the problem is the PG adapter. I don’t know why it uses
RETURNING in its INSERT statement.
No, the problem is in your insistence that you don’t want a PK. Really,
any unique index will do the trick. Stop fighting Rails and good DB
design and declare one.
Jeff
Best,
Marnen Laibow-Koser
http://www.marnen.org
[email protected]
The downside is pretty large: it doesn’t work if I define a composite
PK. If you read my posting above you’ll see that I get a Postgres
adapter error on the RETURNING statement. That and the fact that this
is a logging table (in a legacy DB) with no PK(s). The columns are:
doc_id (not unique), user_id (not unique), date viewed (not unique),
and the combination is not unique. That’s it! No PK. It’s not my
insistence, but rather the nature of a logging table. A row in this
table is never updated, just INSERTs and SELECTs. I cannot add a
row_id column to this table as that would break many COBOL programs.
So that is life in the real world. By claiming the table has a single
PK via “set_primary_keys” (note it’s plural) it works.
Jeff
On Oct 21, 12:50 am, Marnen Laibow-Koser <rails-mailing-l…@andreas-
Marnen Laibow-Koser wrote:
JeffV wrote:
The downside is pretty large: it doesn’t work if I define a composite
PK. If you read my posting above you’ll see that I get a Postgres
adapter error on the RETURNING statement.
No, your posting above says that you get the error when you define a
nil PK, not a composite one.
My mistake. I missed a post where you did say this. Doing some
research…
Best,
Marnen Laibow-Koser
http://www.marnen.org
[email protected]
Marnen Laibow-Koser wrote:
Marnen Laibow-Koser wrote:
JeffV wrote:
The downside is pretty large: it doesn’t work if I define a composite
PK. If you read my posting above you’ll see that I get a Postgres
adapter error on the RETURNING statement.
No, your posting above says that you get the error when you define a
nil PK, not a composite one.
My mistake. I missed a post where you did say this. Doing some
research…
Research done. I created a sample app testing composite_primary_keys,
which is up on Github at GitHub - marnen/cpk_sample: Tests of composite_primary_keys Rails plugin, for discussion at http://www.ruby-forum.com/topic/195472 . if you want
to play around with it. The app uses one table (entries) with three
integer fields (alpha, beta, gamma) and no id field. There are several
branches:
-
no_index_nil_keys – no index on DB table, Entry.set_primary_keys nil.
Doesn’t work.
-
no_index_all_keys – no index on DB table, Entry.set_primary_keys
:alpha, :beta, :gamma. Works for reading and writing (that is,
Entry.create and Entry.all both work as expected).
-
all_index_all_keys – primary key(alpha beta gamma) on DB table,
Entry.set_primary_keys :alpha, :beta, :gamma. Works for reading and
writing.
I think you were getting the error in a no_index_all_keys situation, and
I cannot reproduce that error. I’m using Mac OS X 10.6.1, Ruby 1.8.7,
Rails 2.3.4, composite_primary_keys 2.3.2, and PostgreSQL 8.4.1. Let me
know if you get different results with my code…
Best,
Marnen Laibow-Koser
http://www.marnen.org
[email protected]