How to create a UNIQUE table constraint with migrations.

rubyonrails-talk
1

Hi.

This must be the most obvious thing to do, but I just can't seem to find examples of how to do this. I would like to create a table with a table unique constraint on database level.

In deed some migration code that would generate the following SQL

CREATE TABLE properties (   namespace CHAR(50),   name CHAR(50),   value VARCHAR(100),   CONSTRAINT my_constraint UNIQUE (namespace, name) );

Jarl

2

Hi.

This must be the most obvious thing to do, but I just can't seem to find examples of how to do this. I would like to create a table with a table unique constraint on database level.

In deed some migration code that would generate the following SQL

CREATE TABLE properties ( namespace CHAR(50), name CHAR(50), value VARCHAR(100), CONSTRAINT my_constraint UNIQUE (namespace, name) );

create_table :properties.....    ..... end

add_index :properties, [:namespace, :name], :unique => true

3

Philip Hallstrom <philip@pjkh.com> writes:

4

Philip Hallstrom <philip@pjkh.com> writes:

Hi.

This must be the most obvious thing to do, but I just can't seem to find examples of how to do this. I would like to create a table with a table unique constraint on database level.

In deed some migration code that would generate the following SQL

CREATE TABLE properties ( namespace CHAR(50), name CHAR(50), value VARCHAR(100), CONSTRAINT my_constraint UNIQUE (namespace, name) );

create_table :properties.....   ..... end

add_index :properties, [:namespace, :name], :unique => true

After trying this and opening my interactive SQL prompt (psql), I can see that this only creates an index on the table not a table constraint. I can still put duplicate rows in the table.

Hrm. I can't... Rails 2.3.5, Postgresql 8.4.1 (on mac, but doubt that matters)

5

Jarl Friis wrote:

Philip Hallstrom <philip@pjkh.com> writes:

CREATE TABLE properties (

add_index :properties, [:namespace, :name], :unique => true

After trying this and opening my interactive SQL prompt (psql), I can see that this only creates an index on the table not a table constraint. I can still put duplicate rows in the table.

Excerpt from the PostgreSQL manual:

6

Robert Walker wrote:

Example:

  CREATE TABLE properties (     namespace CHAR(50),     name CHAR(50),     value VARCHAR(100),   );   execute <<-SQL     ALTER TABLE products     ADD CONSTRAINT my_constraint UNIQUE (namespace, name)   SQL

Oops, I didn't notice your original post was not in migration syntax:

Better example:

  def self.up     create_table :products do |t|       t.references :category     end     #add unique constraint     execute <<-SQL       ALTER TABLE products       ADD CONSTRAINT my_constraint UNIQUE (namespace, name)     SQL   end

7

Robert Walker wrote:

  def self.up     create_table :products do |t|       t.references :category     end     #add unique constraint     execute <<-SQL       ALTER TABLE products       ADD CONSTRAINT my_constraint UNIQUE (namespace, name)     SQL   end

Ugh! Still got ahead of myself. Ignore that the table name and attributes don't match yours. You should get the idea anyway. Sorry.

8

Robert Walker <lists@ruby-forum.com> writes:

Jarl Friis wrote:

Philip Hallstrom <philip@pjkh.com> writes:

CREATE TABLE properties (

add_index :properties, [:namespace, :name], :unique => true

After trying this and opening my interactive SQL prompt (psql), I can see that this only creates an index on the table not a table constraint. I can still put duplicate rows in the table.

Excerpt from the PostgreSQL manual: ---------------------------------- PostgreSQL automatically creates a unique index when a unique constraint or a primary key is defined for a table. The index covers the columns that make up the primary key or unique columns (a multicolumn index, if appropriate), and is the mechanism that enforces the constraint. ----------------------------------

This says that an index is created whenever you create a constraint, not that a constraint is created whenever you create an index.

Nevertheless, it seem to be a fact, that whenever you create an unique index, it also craetes a constraint.

Jarl

9

Philip Hallstrom <philip@pjkh.com> writes:

Philip Hallstrom <philip@pjkh.com> writes:

Hi.

This must be the most obvious thing to do, but I just can't seem to find examples of how to do this. I would like to create a table with a table unique constraint on database level.

In deed some migration code that would generate the following SQL

CREATE TABLE properties ( namespace CHAR(50), name CHAR(50), value VARCHAR(100), CONSTRAINT my_constraint UNIQUE (namespace, name) );

create_table :properties.....   ..... end

add_index :properties, [:namespace, :name], :unique => true

After trying this and opening my interactive SQL prompt (psql), I can see that this only creates an index on the table not a table constraint. I can still put duplicate rows in the table.

Hrm. I can't... Rails 2.3.5, Postgresql 8.4.1 (on mac, but doubt that matters)

I am so sorry. I did't do exactly as you said, explanation: I used

create_table :properties do |t|   .....   t.index [:namespace, :name], :unique => true end

That does NOT create an index!!! and therefore neither a constraint!!!

I gues that is a bug in the PostgreSQL adapter.

But when I do as you describe using add_index syntax instead it will create an index (AND constraint!)

But the fact that 't.index [:namespace, :name], :unique => true' does not generate an index is a bug, right?

Thanks for all help. I appreciate the time you've spent on this.

Jarl

10

Hi.

This must be the most obvious thing to do, but I just can't seem to find examples of how to do this. I would like to create a table with a table unique constraint on database level.

In deed some migration code that would generate the following SQL

CREATE TABLE properties ( namespace CHAR(50), name CHAR(50), value VARCHAR(100), CONSTRAINT my_constraint UNIQUE (namespace, name) );

create_table :properties..... ..... end

add_index :properties, [:namespace, :name], :unique => true

After trying this and opening my interactive SQL prompt (psql), I can see that this only creates an index on the table not a table constraint. I can still put duplicate rows in the table.

Hrm. I can't... Rails 2.3.5, Postgresql 8.4.1 (on mac, but doubt that matters)

I am so sorry. I did't do exactly as you said, explanation: I used

create_table :properties do |t| ..... t.index [:namespace, :name], :unique => true end

That does NOT create an index!!! and therefore neither a constraint!!!

I gues that is a bug in the PostgreSQL adapter.

But when I do as you describe using add_index syntax instead it will create an index (AND constraint!)

But the fact that 't.index [:namespace, :name], :unique => true' does not generate an index is a bug, right?

Thanks for all help. I appreciate the time you've spent on this.

That does indeed look like a bug. I just tried it and it doesn't work. What's strange is the source code seems to say that "t.index" simply calls "add_index" just like if I'd done it normally.

I just tried it using MySQL as the backend and it does NOT work either.

11

Philip Hallstrom <philip@pjkh.com> writes:

Hi.

This must be the most obvious thing to do, but I just can't seem to find examples of how to do this. I would like to create a table with a table unique constraint on database level.

In deed some migration code that would generate the following SQL

CREATE TABLE properties ( namespace CHAR(50), name CHAR(50), value VARCHAR(100), CONSTRAINT my_constraint UNIQUE (namespace, name) );

create_table :properties..... ..... end

add_index :properties, [:namespace, :name], :unique => true

After trying this and opening my interactive SQL prompt (psql), I can see that this only creates an index on the table not a table constraint. I can still put duplicate rows in the table.

Hrm. I can't... Rails 2.3.5, Postgresql 8.4.1 (on mac, but doubt that matters)

I am so sorry. I did't do exactly as you said, explanation: I used

create_table :properties do |t| ..... t.index [:namespace, :name], :unique => true end

That does NOT create an index!!! and therefore neither a constraint!!!

I gues that is a bug in the PostgreSQL adapter.

But when I do as you describe using add_index syntax instead it will create an index (AND constraint!)

But the fact that 't.index [:namespace, :name], :unique => true' does not generate an index is a bug, right?

Thanks for all help. I appreciate the time you've spent on this.

That does indeed look like a bug. I just tried it and it doesn't work. What's strange is the source code seems to say that "t.index" simply calls "add_index" just like if I'd done it normally.

I just tried it using MySQL as the backend and it does NOT work either.

Thanks for confirming I am not missing something.

You should submit a ticket to the Rails folks...

I just did: https://rails.lighthouseapp.com/projects/8994/tickets/4101-activerecordconnectionadapterstableindex-does-not-generat-indexes

Jarl