>>>>> "Bob" == Bob Kline <bkline@stripped> writes:
Bob> On Tue, 12 Oct 1999 sinisa@stripped wrote:
>> Michael Farr writes:
>> > Hopefully this will be the last question, then I'll leave yas alone.
>> > when I do a
>> > CREATE TABLE Patient2 SELECT p.* FROM Patient p LEFT JOIN
>> > DentalScheme d ON p.patientId = d.patientId WHERE NOT
>> > ISNULL(p.patientId) ;
>> > is the new table Patient2 supposed to have all the special
>> > options to it. Because it seams to miss the fact that patientId is
>> > a primary key (I fixed it already with an "alter table etc")
>> I must say that (this time) Bob is right !! You should have order by
>> or group by clauses to indicate something like that !!
Bob> Are you saying that there is a way to induce MySQL to automatically set
Bob> a column as a primary key using the CREATE TABLE ... SELECT ... syntax
Bob> (I assume that's what you mean by "... to indicate something like that"
Bob> since that's what the original poster is asking for)? Is there a
Bob> description of how to control this behavior somewhere in the manual
Bob> (couldn't find any)?
Yep. For example
CREATE TABLE Patient2 (PRIMARY KEY (patientId)) SELECT ...
The idea is that anything between table-name and SELECT is added to
the create statement after all columns that are used in the SELECT.
Bob> If the query as quoted by the original poster is accepted by the parser
Bob> (regardless of whether it's possible to trigger creation of a primary
Bob> key the way the poster wants), the grammar specification for CREATE
Bob> TABLE may need to be modified in section 7.6 of the manual, since the
Bob> way it is currently specified the "(create definition,...)" portion is
Bob> required for all variants.
I shall fix this at once.
Bob> It's always a good idea to change the subject header for a message
Bob> thread when you change the subject. This makes it easier for someone
Bob> who wants to know the same thing you're asking about to find the answers
Bob> in the list's archives. Thanks!