Thread: Primary key requires SERIAL
We currently only allow the words PRIMARY KEY on a SERIAL column. Is there a reason we don't allow PRIMARY KEY on an integer field? Seems it should be allowed. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> We currently only allow the words PRIMARY KEY on a SERIAL column.
Say what? There are ColConstraintElem and ConstraintElem productions
for PRIMARY KEY ... are they broken?
regards, tom lane
> Bruce Momjian <pgman@candle.pha.pa.us> writes: > > We currently only allow the words PRIMARY KEY on a SERIAL column. > > Say what? There are ColConstraintElem and ConstraintElem productions > for PRIMARY KEY ... are they broken? > > regards, tom lane > Oh, I see it now. The grammer seems to only support it in SERIAL, but I see it works now. I guess i am surprised SERIAL PRIMARY creates the index and sequence, while INTEGER PRIMARY only creates the index. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
At 10:26 PM 11/18/99 -0500, Bruce Momjian wrote: >We currently only allow the words PRIMARY KEY on a SERIAL column. Is >there a reason we don't allow PRIMARY KEY on an integer field? Seems it >should be allowed. Presumably the only reason to disallow this is to make life difficult for those of us who want to port Oracle-based applications. Given that Oracle represents a huge slice of the established market, and given that in the past Postgres has been an "Oracle-friendly" db in terms of dialectical support (nextval and currval on sequences being germane to the subject at hand) one can only presume that the Postgres development group wants to make porting of Oracle-ish systems difficult. Why? "Currently" must mean the 7.0-in-work because 6.5.1 supports primary key on integer just fine. Why support "serial" and not support "primary key on integer" when Oracle rules the roost, not Sybase? If your statement's true, this is a horrible shift in direction for the PostgreSQL project. - Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Serviceand other goodies at http://donb.photo.net.
At 11:19 PM 11/18/99 -0500, Bruce Momjian wrote: >> Bruce Momjian <pgman@candle.pha.pa.us> writes: >> > We currently only allow the words PRIMARY KEY on a SERIAL column. >> >> Say what? There are ColConstraintElem and ConstraintElem productions >> for PRIMARY KEY ... are they broken? >> >> regards, tom lane >> > >Oh, I see it now. The grammer seems to only support it in SERIAL, but I >see it works now. I guess i am surprised SERIAL PRIMARY creates the >index and sequence, while INTEGER PRIMARY only creates the index. Oops, I guess I blew it by responding to a post by Bruce assuming he was right. Postgres supports a quasi-serial type by creating an index and sequence (while Sybase supports it more transparently) Postgres REALLY supports sequences much like Oracle (and others? I don't know, my DB knowledge is very sketchy). In Oracle, if you define a primary key of type integer and want to sequence it, you define a sequence and use "sequence_name.nextval" and "sequence_name.currval". This is very much like "nextval" and "currval" in Postgres, and I presume no accident. And in Oracle you create the sequence by hand - just like you do in Postgres. Personally, I think maintaining an "Oracle-ish" framework is wise, for the simple selfish reason that I'm interested in porting Oracle-dependent SQL to Postgres. If being "Oracle-ish" is still a goal (it was once a goal of at least some of the implementors, it's obvious) then generating the sequence just makes porting more difficult. Actually, I think the inclusion of "serial" as a more integrated type and leaving primary key stuff alone for existing types is what makes sense. You could provide a higher level of Sybase portability without messing up us Oracle-derived folk. - Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Serviceand other goodies at http://donb.photo.net.
> At 11:19 PM 11/18/99 -0500, Bruce Momjian wrote: > >> Bruce Momjian <pgman@candle.pha.pa.us> writes: > >> > We currently only allow the words PRIMARY KEY on a SERIAL column. > >> > >> Say what? There are ColConstraintElem and ConstraintElem productions > >> for PRIMARY KEY ... are they broken? > >> > >> regards, tom lane > >> > > > >Oh, I see it now. The grammer seems to only support it in SERIAL, but I > >see it works now. I guess i am surprised SERIAL PRIMARY creates the > >index and sequence, while INTEGER PRIMARY only creates the index. > > Oops, I guess I blew it by responding to a post by Bruce assuming he > was right. Seems I can't realy on understanding what we support by just looking at gram.y. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
> > >> > We currently only allow the words PRIMARY KEY on a SERIAL column.
> > Oops, I guess I blew it by responding to a post by Bruce assuming he
> > was right.
> Seems I can't realy on understanding what we support by just looking at
> gram.y.
You can, if you read carefully :)
The grammar allows *only* PRIMARY KEY on the SERIAL column
declaration, since the other keywords or clauses are either redundant
or nonsensical in the context of a serial column. As others have
pointed out, PRIMARY KEY is also allowed elsewhere.
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
> The grammar allows *only* PRIMARY KEY on the SERIAL column
> declaration, since the other keywords or clauses are either redundant
> or nonsensical in the context of a serial column.
Just to put another item on your todo list ;-) ...
I think it's poor practice to try to enforce such a restriction via
the grammar, because that way you cannot generate an error more
specific than "parse error near FOO". It'd be better to allow the
same ColQualifier for SERIAL as for any other column type, and then to
put sanity checks in analyze.c that would complain about conflicting
specifications. We have, or should have, most of those checks in
place already to catch conflicting ColQualifier entries for a plain
column type (eg, "foo int4 NULL NOT NULL"). Also, I do not like
generating hard errors for specifications that are merely redundant
("foo SERIAL NOT NULL"); is there any basis in the SQL spec for
refusing such constructs?
regards, tom lane
> I think it's poor practice to try to enforce such a restriction via
> the grammar, because that way you cannot generate an error more
> specific than "parse error near FOO". It'd be better to allow the
> same ColQualifier for SERIAL as for any other column type, and then to
> put sanity checks in analyze.c that would complain about conflicting
> specifications. We have, or should have, most of those checks in
> place already to catch conflicting ColQualifier entries for a plain
> column type (eg, "foo int4 NULL NOT NULL"). Also, I do not like
> generating hard errors for specifications that are merely redundant
> ("foo SERIAL NOT NULL"); is there any basis in the SQL spec for
> refusing such constructs?
Basis? Basis?? Since SERIAL is an extension, there is not anything
defined explicitly. And SQL tends to be a context-sensitive language
(hmm, what's the term for that?) so it does things in different ways
all over the place; it's not very self-consistant. What *should*
happen with a declaration like "foo int NOT NULL NOT NULL"? One could
argue that the backend should just do it, or perhaps should reject
this as a possibly corrupted declaration.
When I first implemented SERIAL, I'm pretty sure I would have had
trouble checking for conflicting qualifiers. But maybe now all the
pieces are there to do it right. Will look at it...
Anyway, I agree with your points, and will put this on my ToDo. btw, I
still have an item about the parser swallowing multiple SERIAL or
PRIMARY KEY declarations (don't remember which right now); will get to
that also.
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California