Table creation fail

------_=_NextPart_001_01CB12F0.204066F0
Content-Type: text/plain;
charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable

Hi Folks,

=20

I use an online SQL Design tool to design my tables, etc. This generates
script code that I can run in phpMySQL to create the tables, etc.

=20

The below code is causing an error - see below:

=20

CREATE TABLE `testresults` (=20

`id_employees` INTEGER DEFAULT NULL ,
`id_test_test` INTEGER DEFAULT NULL ,
`testdate` DATE DEFAULT NULL ,
`result` VARCHAR( 10 ) DEFAULT NULL ,
`resultsdescription` MEDIUMTEXT DEFAULT NULL ,
`resultsdate` DATE DEFAULT NULL ,
`nextdate` DATE DEFAULT NULL ,
`ptlevel` VARCHAR( 10 ) DEFAULT NULL ,
`givenhere` INTEGER DEFAULT NULL ,
PRIMARY KEY ( )=20

);

MySQL said:=20

#1064 - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near ')
)' at line 11

=20

The bolded line above (resultsdate line) seems to be causing the
problem....nothing looks wrong to me....

=20

Any ideas?

=20

Thanks!


------_=_NextPart_001_01CB12F0.204066F0--

Re: Table creation fail

--001636d3521fc020b10489b50a44
Content-Type: text/plain; charset=ISO-8859-1

Hi,

default cannot be used with primary key.

mysql> CREATE TABLE `testresults` (
->
-> `id_employees` INTEGER DEFAULT NULL ,
-> `id_test_test` INTEGER DEFAULT NULL ,
-> `testdate` DATE DEFAULT NULL ,
-> `result` VARCHAR( 10 ) DEFAULT NULL ,
-> `resultsdescription` MEDIUMTEXT DEFAULT NULL ,
-> `resultsdate` DATE DEFAULT NULL ,
-> `nextdate` DATE DEFAULT NULL ,
-> `ptlevel` VARCHAR( 10 ) DEFAULT NULL ,
-> `givenhere` INTEGER not null primary key);
Query OK, 0 rows affected (0.11 sec)

Krishna


On Wed, Jun 23, 2010 at 9:51 PM, David Stoltz wrote:

> Hi Folks,
>
>
>
> I use an online SQL Design tool to design my tables, etc. This generates
> script code that I can run in phpMySQL to create the tables, etc.
>
>
>
> The below code is causing an error - see below:
>
>
>
> CREATE TABLE `testresults` (
>
> `id_employees` INTEGER DEFAULT NULL ,
> `id_test_test` INTEGER DEFAULT NULL ,
> `testdate` DATE DEFAULT NULL ,
> `result` VARCHAR( 10 ) DEFAULT NULL ,
> `resultsdescription` MEDIUMTEXT DEFAULT NULL ,
> `resultsdate` DATE DEFAULT NULL ,
> `nextdate` DATE DEFAULT NULL ,
> `ptlevel` VARCHAR( 10 ) DEFAULT NULL ,
> `givenhere` INTEGER DEFAULT NULL ,
> PRIMARY KEY ( )
>
> );
>
> MySQL said:
>
> #1064 - You have an error in your SQL syntax; check the manual that
> corresponds to your MySQL server version for the right syntax to use
> near ')
> )' at line 11
>
>
>
> The bolded line above (resultsdate line) seems to be causing the
> problem....nothing looks wrong to me....
>
>
>
> Any ideas?
>
>
>
> Thanks!
>
>

--001636d3521fc020b10489b50a44--

Re: Table creation fail

David,
You did not define a primary key for the table.

The "PRIMARY KEY ( ) " is expecting a column name inside the "( )"

Mike

At 11:21 AM 6/23/2010, David Stoltz wrote:
>Hi Folks,
>
>
>
>I use an online SQL Design tool to design my tables, etc. This generates
>script code that I can run in phpMySQL to create the tables, etc.
>
>
>
>The below code is causing an error - see below:
>
>
>
>CREATE TABLE `testresults` (
>
>`id_employees` INTEGER DEFAULT NULL ,
>`id_test_test` INTEGER DEFAULT NULL ,
>`testdate` DATE DEFAULT NULL ,
>`result` VARCHAR( 10 ) DEFAULT NULL ,
>`resultsdescription` MEDIUMTEXT DEFAULT NULL ,
>`resultsdate` DATE DEFAULT NULL ,
>`nextdate` DATE DEFAULT NULL ,
>`ptlevel` VARCHAR( 10 ) DEFAULT NULL ,
>`givenhere` INTEGER DEFAULT NULL ,
>PRIMARY KEY ( )
>
>);
>
>MySQL said:
>
>#1064 - You have an error in your SQL syntax; check the manual that
>corresponds to your MySQL server version for the right syntax to use
>near ')
>)' at line 11
>
>
>
>The bolded line above (resultsdate line) seems to be causing the
>problem....nothing looks wrong to me....
>
>
>
>Any ideas?
>
>
>
>Thanks!


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Table creation fail

------_=_NextPart_001_01CB12F1.C98E0628
Content-Type: text/plain;
charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable

Actually,

=20

That table isn't supposed to have a PK, so I removed that, and it
works...same effect you suggested.

=20

Thanks!

=20

From: Krishna Chandra Prajapati [mailto:prajapatikc@gmail.com]=20
Sent: Wednesday, June 23, 2010 12:29 PM
To: David Stoltz
Cc: mysql@lists.mysql.com
Subject: Re: Table creation fail

=20

Hi,=20

default cannot be used with primary key.

mysql> CREATE TABLE `testresults` (
->
-> `id_employees` INTEGER DEFAULT NULL ,
-> `id_test_test` INTEGER DEFAULT NULL ,
-> `testdate` DATE DEFAULT NULL ,
-> `result` VARCHAR( 10 ) DEFAULT NULL ,
-> `resultsdescription` MEDIUMTEXT DEFAULT NULL ,
-> `resultsdate` DATE DEFAULT NULL ,
-> `nextdate` DATE DEFAULT NULL ,
-> `ptlevel` VARCHAR( 10 ) DEFAULT NULL ,
-> `givenhere` INTEGER not null primary key);
Query OK, 0 rows affected (0.11 sec)

Krishna



On Wed, Jun 23, 2010 at 9:51 PM, David Stoltz wrote:

Hi Folks,



I use an online SQL Design tool to design my tables, etc. This generates
script code that I can run in phpMySQL to create the tables, etc.



The below code is causing an error - see below:



CREATE TABLE `testresults` (

`id_employees` INTEGER DEFAULT NULL ,
`id_test_test` INTEGER DEFAULT NULL ,
`testdate` DATE DEFAULT NULL ,
`result` VARCHAR( 10 ) DEFAULT NULL ,
`resultsdescription` MEDIUMTEXT DEFAULT NULL ,
`resultsdate` DATE DEFAULT NULL ,
`nextdate` DATE DEFAULT NULL ,
`ptlevel` VARCHAR( 10 ) DEFAULT NULL ,
`givenhere` INTEGER DEFAULT NULL ,
PRIMARY KEY ( )

);

MySQL said:

#1064 - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near ')
)' at line 11



The bolded line above (resultsdate line) seems to be causing the
problem....nothing looks wrong to me....



Any ideas?



Thanks!

=20


------_=_NextPart_001_01CB12F1.C98E0628--

Re: Table creation fail

Hi David, all!


David Stoltz wrote:
> Actually,
>=20
> =20
>=20
> That table isn't supposed to have a PK, so I removed that, and it
> works...same effect you suggested.

Even if you currently don't need a primary key in that table, IMO you
should still define one. Use some 'id_testresult' column with an
autoincrement clause, so you need not provide a value.

Sooner or later you may (I really think: will) feel the need to uniqu=
ely
identify a row, especially to delete it, and a primary key will be ve=
ry
helpful then.

Your original problem was most likely not due to mentioning "primary
key" but rather to not providing a column name for it.


Regards,
Jörg

--=20
Joerg Bruehe, MySQL Build Team, Joerg.Bruehe@Sun.COM
Sun Microsystems GmbH, Komturstrasse 18a, D-12099 Berlin
Geschaeftsfuehrer: Juergen Kunz
Amtsgericht Muenchen: HRB161028


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Table creation fail

--00c09f93dd40c620500489d49f76
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Correct,
a primary key is used to uniquely identify each row in a table. It can
either be part of the actual record itself , or it can be an artificial
field (one that has nothing to do with the actual record).
A primary key can consist of one or more fields on a table. When multiple
fields are used as a primary key, they are called a composite key.

A Primary key constraint can be defined at various levels:

* Primary key constraint defined at column level
Syntax: () Primary Key
* Primary key constraint defined at table level
Syntax: Primary key (, )

--Prabhat



On Thu, Jun 24, 2010 at 3:03 PM, Joerg Bruehe wrote:

> Hi David, all!
>
>
> David Stoltz wrote:
> > Actually,
> >
> >
> >
> > That table isn't supposed to have a PK, so I removed that, and it
> > works...same effect you suggested.
>
> Even if you currently don't need a primary key in that table, IMO you
> should still define one. Use some 'id_testresult' column with an
> autoincrement clause, so you need not provide a value.
>
> Sooner or later you may (I really think: will) feel the need to uniquely
> identify a row, especially to delete it, and a primary key will be very
> helpful then.
>
> Your original problem was most likely not due to mentioning "primary
> key" but rather to not providing a column name for it.
>
>
> Regards,
> Jörg
>
> --
> Joerg Bruehe, MySQL Build Team, Joerg.Bruehe@Sun.COM
> Sun Microsystems GmbH, Komturstrasse 18a, D-12099 Berlin
> Geschaeftsfuehrer: Juergen Kunz
> Amtsgericht Muenchen: HRB161028
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=3Daim.prabhat@gmail.com
>
>


--=20
Best Regards,

Prabhat Kumar
MySQL DBA
Datavail-India Mumbai
Mobile : 91-9987681929
www.datavail.com

My Blog: http://adminlinux.blogspot.com
My LinkedIn: http://www.linkedin.com/in/profileprabhat

--00c09f93dd40c620500489d49f76--

Re: Table creation fail

--0016e64754cabef6670489d7343f
Content-Type: text/plain; charset=ISO-8859-1

>
> A Primary key constraint can be defined at various levels:
>
> * Primary key constraint defined at column level
> Syntax: () Primary Key
> * Primary key constraint defined at table level
> Syntax: Primary key (, )
>


Aren't those two alternate syntaxes for the exact same thing ?

--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--0016e64754cabef6670489d7343f--

Re: Table creation fail

--001485f921e0e39ba40489d85735
Content-Type: text/plain; charset=ISO-8859-1

Yes, but few exceptions;

Column level constraints are applicable to that column only, whereas table
level constraints are used to define composite keys like primary key for the
combination of two or more columns in a table.

column level constraints contain all types of constraints (like, not
null,primary key,foreign key,unique).but table level except not null
constraint its supports all constraints.

--Prabhat

On Fri, Jun 25, 2010 at 2:45 PM, Johan De Meersman wrote:

> A Primary key constraint can be defined at various levels:
>>
>> * Primary key constraint defined at column level
>> Syntax: () Primary Key
>> * Primary key constraint defined at table level
>> Syntax: Primary key (, )
>>
>
>
> Aren't those two alternate syntaxes for the exact same thing ?
>
> --
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel
>



--
Best Regards,

Prabhat Kumar
MySQL DBA
Datavail-India Mumbai
Mobile : 91-9987681929
www.datavail.com

My Blog: http://adminlinux.blogspot.com
My LinkedIn: http://www.linkedin.com/in/profileprabhat

--001485f921e0e39ba40489d85735--