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 -- 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-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Philip Hallstrom
2010-Feb-24 20:13 UTC
Re: How to create a UNIQUE table constraint with migrations.
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> 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- > talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org > . > For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en > . >-- 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-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Jarl Friis
2010-Mar-02 16:10 UTC
Re: How to create a UNIQUE table constraint with migrations.
Philip Hallstrom <philip-LSG90OXdqQE@public.gmane.org> 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 => trueAfter 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 -- 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-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Philip Hallstrom
2010-Mar-02 17:12 UTC
Re: How to create a UNIQUE table constraint with migrations.
> Philip Hallstrom <philip-LSG90OXdqQE@public.gmane.org> 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Robert Walker
2010-Mar-02 20:36 UTC
Re: How to create a UNIQUE table constraint with migrations.
Jarl Friis wrote:> Philip Hallstrom <philip-LSG90OXdqQE@public.gmane.org> 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/. -- 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-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Robert Walker
2010-Mar-02 20:42 UTC
Re: How to create a UNIQUE table constraint with migrations.
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) > SQLOops, 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 -- Posted via http://www.ruby-forum.com/. -- 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-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Robert Walker
2010-Mar-02 20:44 UTC
Re: How to create a UNIQUE table constraint with migrations.
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 > endUgh! Still got ahead of myself. Ignore that the table name and attributes don''t match yours. You should get the idea anyway. Sorry. -- Posted via http://www.ruby-forum.com/. -- 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-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Jarl Friis
2010-Mar-03 10:52 UTC
Re: Re: How to create a UNIQUE table constraint with migrations.
Robert Walker <lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> writes:> Jarl Friis wrote: >> Philip Hallstrom <philip-LSG90OXdqQE@public.gmane.org> 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 -- 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-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Jarl Friis
2010-Mar-03 11:02 UTC
Re: How to create a UNIQUE table constraint with migrations.
Philip Hallstrom <philip-LSG90OXdqQE@public.gmane.org> writes:>> Philip Hallstrom <philip-LSG90OXdqQE@public.gmane.org> 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 -- 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-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Philip Hallstrom
2010-Mar-03 17:55 UTC
Re: 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) >>>>> ); >>>>> >>>> >>>> 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. +------------+--------------+------+-----+---------+----------------+ | 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 -- 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-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Jarl Friis
2010-Mar-03 20:54 UTC
Re: How to create a UNIQUE table constraint with migrations.
Philip Hallstrom <philip-LSG90OXdqQE@public.gmane.org> 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 -- 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-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.