Want to make existing column unique in multiple tables

rubyonrails-talk
1

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.

2

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.

3

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