I have a table that is to hold all ISO 639 language codes: alpha3t, alpha3b,
alpha2, that bunch. It also (indirectly) holds language names, but
that''s off-topic.
alpha3t is the primary key, so I''ve told Active Record about this by
saying
set_primary_key "alpha3t" within the Language model. (Don''t
try to call your
models things like ISO639 (won''t work because of the capitalization) or
Iso639 (won''t work because I don''t know why).)
Now, when I assign a value to alpha3t, it doesn''t end up in the insert
statements.
The unit test tells me:
ActiveRecord::StatementInvalid: ERROR: null value in column "alpha3t"
violates
not-null constraint
: INSERT INTO i18n.iso639_eng ("name", "alpha2",
"alpha3b", "name_id") VALUES(''G
angsta'', ''ho'', ''dog'', NULL)
I totally agree! I don''t want any NULL values in my alpha3t column, so
is
Active Record simply ignoring this field because it thinks it should leave
primary keys alone or something? Or does it only manage single-column,
integer primary keys with a correctly named accompanying sequence?
Thanks for shedding any light on what is probably my ignorance,
- Rowan
--
Morality is usually taught by the immoral.
On 6/30/05, BigSmoke <bigsmoke-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> I have a table that is to hold all ISO 639 language codes: alpha3t, alpha3b, > alpha2, that bunch. It also (indirectly) holds language names, but > that''s off-topic. > > alpha3t is the primary key, so I''ve told Active Record about this by saying > set_primary_key "alpha3t" within the Language model. (Don''t try to call your > models things like ISO639 (won''t work because of the capitalization) or > Iso639 (won''t work because I don''t know why).) > > Now, when I assign a value to alpha3t, it doesn''t end up in the insert > statements. > The unit test tells me: > ActiveRecord::StatementInvalid: ERROR: null value in column "alpha3t" violates > not-null constraint > : INSERT INTO i18n.iso639_eng ("name", "alpha2", "alpha3b", "name_id") VALUES(''G > angsta'', ''ho'', ''dog'', NULL) > > I totally agree! I don''t want any NULL values in my alpha3t column, so is > Active Record simply ignoring this field because it thinks it should leave > primary keys alone or something? Or does it only manage single-column, > integer primary keys with a correctly named accompanying sequence? > > Thanks for shedding any light on what is probably my ignorance,What you''re looking for is to use a ''natural'' key for your data, this is something that ActiveRecord doesn''t support. The easiest thing to do is at an auto incrementing primary key to your table and just treat alpha3t as ''yet another attribute'' -- Cheers Koz
> Is support for this planned for ActiveRecord?I don''t believe so. If these tables are read-only you may be able to get away with it, but with inserts you must use surrogate keys.> Meanwhile, is it possible to _not_ have a primary key for a table?No, If this were the case how would active record be able to refer to them? What would you pass to Model.find()? How would @model.something work?> I have other tables too with primary keys that > aren''t auto incremented by a sequence and I have primary keys that are > multi-column.If you''re working with a pre-existing schema, you need to approach your design slightly differently. Active Record will not be supporting multi-column primary keys for quite some time. Most ORMs *strongly* advise not using multi-column keys, and also *strongly* advise using surrogate rather than natural keys.> These are all quite essential in this database and just adding > a few extra ID''s would be kludgy (at best) IMHO.Natural keys are *never* essential in a new database, a pre-existing schema maybe, but I''ll still argue that it''s almost always optional. You''ll find that your database actually maps to a single value in its indexes anyway. If your concerns are the disk space, relax. If your concerned about ''duplication'' then use unique indexes. You''re actually saving diskspace with a surrogate key as all the references just store integers, rather than multiple columns or big-long-varchars. Remember, the ''id'' field in active record is simply a representation of the inherent identity in OO systems. irb(main):006:0> c1 = Customer.new("Michael Koziarski") => #<Customer:0x1018a4a8 @customer_name="Michael Koziarski"> irb(main):007:0> c2 = Customer.new("Michael Koziarski") => #<Customer:0x10186188 @customer_name="Michael Koziarski"> irb(main):008:0> c1 == c2 => false Relational algebra says that two relations are considered equal <=> all corresponding entries are equal irrespective of their order. So representing Customer as sets c1 = ("Michael Koziarski") c2 = ("Michael Koziarski") c1 = c2 This is *not* what we want with OO systems, so we introduce a single column to represent the identity. Trying to avoid that is certainly possible, however I''d question the benefits. -- Cheers Koz
On 6/30/05, Michael Koziarski <koziarski-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> > Is support for this planned for ActiveRecord? > > I don''t believe so. If these tables are read-only you may be able to > get away with it, but with inserts you must use surrogate keys. > > > Meanwhile, is it possible to _not_ have a primary key for a table? > > No, If this were the case how would active record be able to refer > to them? What would you pass to Model.find()? How would > @model.something work?Good point. This wouldn''t have been a good work-around at all.> > I have other tables too with primary keys that > > aren''t auto incremented by a sequence and I have primary keys that are > > multi-column. > > If you''re working with a pre-existing schema, you need to approach > your design slightly differently. Active Record will not be > supporting multi-column primary keys for quite some time. Most ORMs > *strongly* advise not using multi-column keys, and also *strongly* > advise using surrogate rather than natural keys.Point taken.> > These are all quite essential in this database and just adding > > a few extra ID''s would be kludgy (at best) IMHO. > > Natural keys are *never* essential in a new database, a pre-existing > schema maybe, but I''ll still argue that it''s almost always optional. > > You''ll find that your database actually maps to a single value in its > indexes anyway. If your concerns are the disk space, relax. If your > concerned about ''duplication'' then use unique indexes. You''re > actually saving diskspace with a surrogate key as all the references > just store integers, rather than multiple columns or > big-long-varchars.I''m not in the least concerned with disk space. Basically, my idea to use the alpha3t lang. code as the primary key stemmed from an earlier iteration in the DB''s design where the language codes weren''t references, because of a problem with circular references. I solved this problem of circular references some time ago, so I guess I _can_ add an id field and use that.> Remember, the ''id'' field in active record is simply a representation > of the inherent identity in OO systems. > > irb(main):006:0> c1 = Customer.new("Michael Koziarski") > => #<Customer:0x1018a4a8 @customer_name="Michael Koziarski"> > irb(main):007:0> c2 = Customer.new("Michael Koziarski") > => #<Customer:0x10186188 @customer_name="Michael Koziarski"> > irb(main):008:0> c1 == c2 > => false > > Relational algebra says that two relations are considered equal <=> > all corresponding entries are equal irrespective of their order. > > So representing Customer as sets > > c1 = ("Michael Koziarski") > c2 = ("Michael Koziarski") > > c1 = c2 > > This is *not* what we want with OO systems, so we introduce a single > column to represent the identity. Trying to avoid that is certainly > possible, however I''d question the benefits.Thanks for enlightening me on this subject. I greatly appreciate :-D Now, I might have another problem. Doesn''t Rails assume a sequence such as table_id_seq? Well, I''m trying to insert into a view and I can''t have a sequence with two names. So, is it possible to set the name of the sequence to use with the primary key? In other words: can I reuse the same sequence for two different relations? This is very natural in Postgres, so I''m hoping that it''s possible in Rails too. If this isn''t so, I guess it would still be convenient if I could have a primary key that isn''t managed by Rails. Using the natural key worked well with find() operations. It could be a nice feature to be able to set the field value that is the primary key after all. In all the places in my DB where I don''t want anyone to touch IDs, I already protect them using triggers anyway. - Rowan -- Morality is usually taught by the immoral.