Want to make existing column unique in multiple tables

[ritesh ranjan]

Hi,

I want to make a particular existing column unique. I have to same name column in 4 tables. I have created a new migration but not sure what to write inside 

def up

     ...........

     ...........

end

I am using rails 3.2.1. Please help.

--
Regards

Ritesh Ranjan

iPhone Developer & Designer

tapisfun.com

Follow me @ Twitter 

[Colin Law]

On 26 June 2012 19:48, ritesh ranjan <riteshr...@gmail.com> wrote:
> Hi,
>
> I want to make a particular existing column unique. I have to same name
> column in 4 tables. I have created a new migration but not sure what to
> write inside

It is not clear to me exactly what you want to do. Please explain in
more detail.

>
> def up
>      ...........
>      ...........
> end
>
>
> I am using rails 3.2.1. Please help.
>
> --
> Regards
> Ritesh Ranjan
> iPhone Developer & Designer
> tapisfun.com
> Follow me @ Twitter
>

> --
> You received this message because you are subscribed to the Google Groups
> "Ruby on Rails: Talk" group.
> To post to this group, send email to rubyonra...@googlegroups.com.
> To unsubscribe from this group, send email to
> rubyonrails-ta...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/rubyonrails-talk?hl=en.

[Gregory Bataille]

There is no super quick way to do that.

Are you looking to do it just for your migration, or to keep this behavior in the future.

What I would do is use a fifth table that contains the next value for the incremental counter.

Every time you need one or several IDs, you do use a stored proc to get the next value.

What's important when doing that is thinking about concurrency.

so you would have a code that in transact sql would look like

proc getNextId(@numberOfIds int)

  declare @returnId int

  update next_id

  set @returnId = nextId

     , nextId = nextId + @numberOfIds

  return @returnId

go

Something like that normally ensure that the table is updated at the same time the value is returned to you (i.e. operation is atomic).

I guess you can do something like that on the application server layer too, but then you do need to continue to think about multithreading, server restart and things like that that can get tricky.

But to conclude on this point, having such a need normally means that your datamodel is not clean. If those table share a key that needs to be unique across tables, it means most likely that this key has the same semantic for all 4 tables. Therefore it should most likely be just one table (maybe with some extensions tables filled depending on the row, but still all should start with a single table).