ActiveRecord + postgresql + data constraints

Question

I would like to use ActiveRecord in my Rails application, but I would like to encode data constraints in the PostgreSQL database instead of in the ActiveRecord models.

For example, if I have a model generated by a scaffold,

 rails generate scaffold User name:string email:string yearofbirth:integer

I'll want to make sure that the name is reasonably long. e.g.

 class User < ActiveRecord::Base
   validates :name, :length => { :maximum => 140 }
   validates_uniqueness_of :name
   validates_numericality_of :yearofbirth, :greater_than_or_equal_to => 1900
 end

These validations are nice because ActiveRecord will throw up some nice error messages to the view if one tries to add in bad data.

However, I would like to encode the rules in the database itself, and have the database throw up the same nice looking and informative error messages to ActiveRecord, which will then pass them to the view layer. For example,

 create table "Users" (
   id integer unique, 
   name varchar(140) unique check(length(name)<140), 
   email varchar(255),
   yearofbirth integer check (yearofbirth >= 1900) 
 );

What do I need to do in PostgreSQL or ActiveRecord to make this happen? When I tried it with the pgAdmin III tool, it threw up error messages, but I would like to customize them.

Also, is there a way to encode these database rules right into the database migration files in Ruby?

I appreciate any help you can give me.

+1: constraints on data should always be in the DB and then added automatically to the ActiveRecord model. Rails and ActiveRecord are only one way to access our data, not the one and only mandatory checkpoint. This ensures integrity and respects the DRY principle. Thus your question is interesting and any good answer will be too. — Ludovic Kuty
– Ludovic Kuty, Commented Dec 15, 2011 at 12:50

Peter Eisentraut · Accepted Answer · 2011-01-24 20:42:59Z

1

The only thing wrong with the above CREATE TABLE statement is that USER is a reserved word and must be quoted. Therefore,

CREATE TABLE "User" ( ... );

will work. (Choose the capitalization of the name that you intended.)

answered Jan 24, 2011 at 20:42

Peter Eisentraut

37.1k12 gold badges91 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jay Godse Over a year ago

Thank you. I made the correction. Would you, by any chance, know how to get custom error messages from constraint violations?

Peter Eisentraut Over a year ago

You can catch the particular SQLSTATE error code either in the client or in a stored procedure. But there is no way to change the error message directly in the database system.

m4risU · Accepted Answer · 2011-01-25 00:29:42Z

1

In fact you should have table Users, In other case you can get many more problems. Of course you can define other inflection, but it is simply wrong and not railsway.

class User
  ...
end

then create table Users ...

answered Jan 25, 2011 at 0:29

m4risU

1,24111 silver badges14 bronze badges

Comments

Ludovic Kuty · Accepted Answer · 2011-12-15 14:44:44Z

Here is an example using Oracle (not Postgresql) but the idea is the same. Only the catalog differs. Note that I named my constraint "table_column_nn" for a not null constraint. The solution below could probably be improved.

Say the model Author representing an Author should not have a NULL name.

class Author < ActiveRecord::Base
  has_and_belongs_to_many :books

  r = ActiveRecord::Base.connection.select_one("select CONSTRAINT_NAME from user_constraints where TABLE_NAME = 'AUTHORS' and constraint_name like '%_NN'")
  m = r["constraint_name"].match(/^[^_]*_([^_]*)_NN$/)
  if !m.nil?
    column = m[1]
    validates column.downcase.to_sym, :presence => true
  end
end

The SQL script to setup the schema is:

create table authors (
    id integer primary key,
    name varchar2(100) constraint authors_name_nn not null,
    updated_at timestamp with local time zone,
    created_at timestamp with local time zone
);

The SQL request alone on the DB gives this:

select constraint_name, search_condition from user_constraints where table_name = 'AUTHORS' and constraint_name like '%_NN';
CONSTRAINT_NAME     SEARCH_CONDITION
---------------     ----------------------
AUTHORS_NAME_NN      "NOM" IS NOT NULL

1 row selected.

Collectives™ on Stack Overflow

ActiveRecord + postgresql + data constraints

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related