How to create a UNIQUE table constraint with migrations.

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

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

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)

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:

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

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.

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

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

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.

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