How to create a UNIQUE table constraint with migrations.

[Jarl Friis]

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

[Philip Hallstrom]

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

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

> Jarl
>
> --
> 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 rubyonrails-
> ta...@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
> .
>

[Jarl Friis]

Philip Hallstrom <phi...@pjkh.com> writes:

> On Feb 24, 2010, at 11:17 AM, Jarl Friis wrote:
>
>> 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.

Jarl

[Philip Hallstrom]

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

***************************************************************************************
class CreateProperties < ActiveRecord::Migration
def self.up
create_table :properties do |t|
t.string :namespace
t.string :name
t.string :value

t.timestamps

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

end

def self.down
drop_table :properties
end
end
***************************************************************************************
foo_development=# \d properties;
Table "public.properties"
Column | Type |
Modifiers
------------+-----------------------------
+---------------------------------------------------------
id | integer | not null default
nextval('properties_id_seq'::regclass)
namespace | character varying(255) |
name | character varying(255) |
value | character varying(255) |
created_at | timestamp without time zone |
updated_at | timestamp without time zone |
Indexes:
"properties_pkey" PRIMARY KEY, btree (id)
"index_properties_on_namespace_and_name" UNIQUE, btree
(namespace, name)

foo_development=# insert into properties (namespace, name) values
('one', 'two');
INSERT 0 1
foo_development=# select * from properties;
id | namespace | name | value | created_at | updated_at
----+-----------+------+-------+------------+------------
1 | one | two | | |
(1 row)

foo_development=# insert into properties (namespace, name) values
('one', 'two');
ERROR: duplicate key value violates unique constraint
"index_properties_on_namespace_and_name"
***************************************************************************************
>> Property.create!(:namespace => 'three', :name => 'four')
SQL (0.2ms) SET client_min_messages TO 'panic'
SQL (0.1ms) SET client_min_messages TO 'notice'
SQL (0.2ms) BEGIN
SQL (1.2ms) INSERT INTO "properties" ("name", "updated_at",
"namespace", "value", "created_at") VALUES(E'four', '2010-03-02
17:09:34.515886', E'three', NULL, '2010-03-02 17:09:34.515886')
RETURNING "id"
SQL (0.9ms) COMMIT
=> #<Property id: 3, namespace: "three", name: "four", value: nil,
created_at: "2010-03-02 17:09:34", updated_at: "2010-03-02 17:09:34">
>> Property.create!(:namespace => 'three', :name => 'four')
SQL (0.1ms) BEGIN
SQL (0.0ms) PGError: ERROR: duplicate key value violates unique
constraint "index_properties_on_namespace_and_name"
: INSERT INTO "properties" ("name", "updated_at", "namespace",
"value", "created_at") VALUES(E'four', '2010-03-02 17:09:36.947674',
E'three', NULL, '2010-03-02 17:09:36.947674') RETURNING "id"
SQL (0.2ms) ROLLBACK
ActiveRecord::StatementInvalid: PGError: ERROR: duplicate key value
violates unique constraint "index_properties_on_namespace_and_name"
: INSERT INTO "properties" ("name", "updated_at", "namespace",
"value", "created_at") VALUES(E'four', '2010-03-02 17:09:36.947674',
E'three', NULL, '2010-03-02 17:09:36.947674') RETURNING "id"

[Robert Walker]

Jarl Friis wrote:
> Philip Hallstrom <phi...@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.
----------------------------------

From what I gather using "add_index :properties, [:namespace, :name],
:unique => true" should do pretty much the same thing as adding a unique
constraint, and do so in a database agnostic manner.

If you really want to use the constraint then simply execute the SQL
yourself:

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

Note: Don't forget to drop the constraint in your down method if
necessary.
--
Posted via http://www.ruby-forum.com/.

[Robert Walker]

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]

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.

[Jarl Friis]

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

> Jarl Friis wrote:
>> Philip Hallstrom <phi...@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

[Jarl Friis]

Philip Hallstrom <phi...@pjkh.com> writes:

>> Philip Hallstrom <phi...@pjkh.com> writes:
>>
>>> On Feb 24, 2010, at 11:17 AM, Jarl Friis wrote:
>>>
>>>> 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

[Philip Hallstrom]

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.

+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| a | varchar(255) | YES | | NULL | |
| b | varchar(255) | YES | | NULL | |
| created_at | datetime | YES | | NULL | |
| updated_at | datetime | YES | | NULL | |
+------------+--------------+------+-----+---------+----------------+

So, at least it's not a postgresql specific bug.

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

-philip

[Jarl Friis]

Philip Hallstrom <phi...@pjkh.com> writes:

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