How to create a UNIQUE table constraint with migrations

Rails
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

On Feb 24, 2010, at 11:17 AM, Jarl F. wrote:

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 H. [email protected] 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.

Jarl

4

Jarl F. wrote:

Philip H. [email protected] 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.

5

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)

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”

6

Robert W. 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 W. 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.

1 Like
8

Robert W. [email protected] writes:

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

CREATE TABLE properties (

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

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.

±-----------±-------------±-----±----±--------±---------------+
| 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

10

Philip H. [email protected] writes:

create_table :properties…
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

11

Philip H. [email protected] writes:

CREATE TABLE properties (

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

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