On fim, 2006-12-07 at 09:27 -0800, Jeff Davis wrote: > On Thu, 2006-12-07 at 12:26 +0000, Ragnar wrote: > > On mið, 2006-12-06 at 18:22 -0800, Jeff Davis wrote: > > > > > Interesting, I was just thinking about this today as well. I am thinking > > > it would be nice if we could: > > > > > > ALTER TABLE SET PRIMARY KEY INDEX foo_pkey; > > > > > > If it's already got a primary key we switch the primary key to be the > > > new primary key > > > > > > (throwing an error if the columns don't match up to the > > > existing primary key, > > > > not sure what you mean by this > > In my suggestion, if the table already has a primary key, then you can > only set the primary key index to be an index with exactly the same > columns as the existing primary key index. Why would you do that? I saw the use-case of when you have a primary key and a surrogate key , and decided you wanted the surrogate key to be the primary key after all, maybe because the natural key you had used turned out not to be a good candidate. > > > > or if it's not unique). > > > > must also be NOT NULL > > Indexes can't be NOT NULL; NOT NULL is a constraint. Sorry, I got confused by the UNIQUE in the create index syntax: CREATE [ UNIQUE ] INDEX name ON table [ USING method ] ( { column | ( expression ) } [ opclass ] [, ...] ) [ TABLESPACE tablespace ] [ WHERE predicate ] > > ... > > What about existing foreign key constraints ? > > as the only function of the PRIMARY key property of an > > index is making it the default target of a foreign key > > reference, you would have to decide what implications > > this has. Possibly none, as I am not sure the foreign > > key constraint remembers if the target was a primary key > > or not. > > Doesn't matter. Foreign keys don't reference an index, they reference a > set of attributes. I am just trying to provide an ability to change the > underlying unique index that is used to implement the unique constraint > that is necessary for all primary keys. I was still imagining here that you would want a different set of attributes froyour primary key. gnari