Thread

1. Re: [BUGS] UNIQUE constraint no longer works under 6.5.1

   Tom Lane <tgl@sss.pgh.pa.us> — 1999-08-14T14:46:31Z

   Mark Dalphin <mdalphin@amgen.com> writes:
   > Using the UNIQUE constraint in a TABLE definition no longer does anything.
   
   Interesting.  Playing with some variants of your example shows that
   UNIQUE works fine *unless* there is another column marked PRIMARY KEY.
   Then the UNIQUE constraint is ignored.  Looks like a simple logic bug in
   the table-definition expander.
   
   A look at the CVS logs reveals this apparently related entry for
   parser/analyze.c:
   
   revision 1.102
   date: 1999/05/12 07:17:18;  author: thomas;  state: Exp;  lines: +68 -24
   Fix problem with multiple indices defined if using column- and table-
    constraints. Reported by Tom Lane.
   Now, check for duplicate indices and retain the one which is a primary-key.
   
   Thomas, do you recall what that was all about?  I don't offhand...
   
   			regards, tom lane
   
   

2. Re: [BUGS] UNIQUE constraint no longer works under 6.5.1

   Thomas Lockhart <lockhart@alumni.caltech.edu> — 1999-08-14T15:17:59Z

   > Interesting.  Playing with some variants of your example shows that
   > UNIQUE works fine *unless* there is another column marked PRIMARY KEY.
   > Then the UNIQUE constraint is ignored.  Looks like a simple logic bug in
   > the table-definition expander.
   > A look at the CVS logs reveals this apparently related entry for
   > parser/analyze.c:
   > revision 1.102
   > date: 1999/05/12 07:17:18;  author: thomas;  state: Exp;  lines: +68 -24
   > Fix problem with multiple indices defined if using column- and table-
   >  constraints. Reported by Tom Lane.
   > Now, check for duplicate indices and retain the one which is a primary-key.
   
   Yow! The problem reported earlier (by you, so you share some blame! ;)
   was that if one specified a primary key *and* a unique constraint, and
   they both pointed to the same column, then you got two indices
   created. So I tried to go through the list of indices and drop any
   which seemed to be the same as the primary key index.
   
   I apparently hadn't tested for this reported case (obviously :() but
   it should be easy to fix. I'll look at it soon, unless someone already
   has.
   
                          - Thomas
   
   -- 
   Thomas Lockhart				lockhart@alumni.caltech.edu
   South Pasadena, California
   
   

3. Re: [BUGS] UNIQUE constraint no longer works under 6.5.1

   Thomas Lockhart <lockhart@alumni.caltech.edu> — 1999-08-14T23:45:27Z

   > > Interesting.  Playing with some variants of your example shows that
   > > UNIQUE works fine *unless* there is another column marked PRIMARY KEY.
   > > Then the UNIQUE constraint is ignored.  Looks like a simple logic bug in
   > > the table-definition expander.
   > > A look at the CVS logs reveals this apparently related entry for
   > > parser/analyze.c:
   > > revision 1.102
   > > date: 1999/05/12 07:17:18;  author: thomas;  state: Exp;  lines: +68 -24
   > > Fix problem with multiple indices defined if using column- and table-
   > >  constraints. Reported by Tom Lane.
   > > Now, check for duplicate indices and retain the one which is a primary-key.
   > Yow! The problem reported earlier (by you, so you share some blame! ;)
   > was that if one specified a primary key *and* a unique constraint, and
   > they both pointed to the same column, then you got two indices
   > created. So I tried to go through the list of indices and drop any
   > which seemed to be the same as the primary key index.
   
   OK, the immediate problem was due to a cut and paste typo (I was
   comparing column names to decide if indices were identical, and the
   pointer to the name was set to be the same for both index elements).
   
   But, the code which was in there was always a bit wimpy; it only
   checked for duplicate indices if they both had only one column. I've
   modified it to (I think) check for any number of columns, so
   constraints like
   
     create table t1 (i int, j int, unique(i,j), primary key(i,j))
   
   should also work correctly by swallowing the "unique" index.
   
   Here is a patch, to be applied in src/backend/parser/. Let me know if
   it fixes your problem and any other cases you can think of, and I'll
   apply it to the tree(s).
   
                         - Thomas
   
   -- 
   Thomas Lockhart				lockhart@alumni.caltech.edu
   South Pasadena, California