ActiveRecord + postgresql + data limits

Question

I would like to use ActiveRecord in my Rails application, but I would like to encode data restrictions in a PostgreSQL database, not in ActiveRecord models.

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

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

I want the name to be long enough. eg

  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 checks are good because ActiveRecord gives some good error messages to the view if you are trying to add bad data.

However, I would like to encode the rules in the database itself and create a database with the same nice and informative ActiveRecord error messages that will then pass them to the presentation layer. For instance,

  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 for this to happen? When I tried it with the pgAdmin III tool, it gave error messages, but I would like to configure them.

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

I appreciate any help you can give me.

Answer 1

The only thing wrong with the CREATE TABLE statement above is that USER is a reserved word and should be specified. Hence,

 CREATE TABLE "User" ( ... ); 

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

Answer 2

In fact, you should have a Users table, otherwise you can get many more problems. Of course, you can identify other excesses, but this is simply wrong, not a railsway.

 class User ... end 

then create table Users ...

Answer 3

Here is an example of using Oracle (not Postgresql), but the idea is the same. Only the directory is different. Note that I called my constraint "table_column_nn" for a non-zero constraint. Perhaps the solution below could be improved.

Say that the Author model representing the author must 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 

SQL script to configure the schema:

 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 ); 

Just a SQL query in DB gives the following:

 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.