Philip Hallstrom <[email protected]> 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)
***************************************************************************************
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"
--
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 [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.
