Re: Create Table from Results of SQL Query

[O. Olson]

Thanks Dan for your quick response - it works now. I am new to SQL in general 
and MySQL in particular. 
O.O. 



--- Ven 4/7/08, Dan Nelson <[EMAIL PROTECTED]> ha scritto:

> Da: Dan Nelson <[EMAIL PROTECTED]>
> Oggetto: Re: Create Table from Results of SQL Query
> A: "O. Olson" <[EMAIL PROTECTED]>
> Cc: mysql@lists.mysql.com
> Data: Venerdì 4 luglio 2008, 05:11
> In the last episode (Jul 04), O. Olson said: I have a SQL
> Query that
> > has an inner join and it is taking too long to
> execute. I am thinking
> > of speeding this up by dumping the result into a
> separate table - as
> > I would be requiring the result of this query many
> times in the
> > future.  I am wondering if someone could show me how
> to create a
> > table from the results of the SQL Query.
> 
> CREATE TABLE table2 SELECT field1,field2 FROM table1
> 
> http://dev.mysql.com/doc/refman/5.0/en/create-table.html
> 
> Note that you may need to add appropriate indexes after
> this, to make
> your later queries on the temp table faster.
> 
> -- 
>   Dan Nelson
>   [EMAIL PROTECTED]
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:   
> http://lists.mysql.com/[EMAIL PROTECTED]


  Posta, news, sport, oroscopo: tutto in una sola pagina. 
Crea l'home page che piace a te!
www.yahoo.it/latuapagina

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Create Table from Results of SQL Query

[Dan Nelson]

In the last episode (Jul 04), O. Olson said: I have a SQL Query that
> has an inner join and it is taking too long to execute. I am thinking
> of speeding this up by dumping the result into a separate table - as
> I would be requiring the result of this query many times in the
> future.  I am wondering if someone could show me how to create a
> table from the results of the SQL Query.

CREATE TABLE table2 SELECT field1,field2 FROM table1

http://dev.mysql.com/doc/refman/5.0/en/create-table.html

Note that you may need to add appropriate indexes after this, to make
your later queries on the temp table faster.

-- 
Dan Nelson
[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Create table

[Rob Wultsch]

On Tue, Apr 15, 2008 at 7:27 AM, Paul DuBois <[EMAIL PROTECTED]> wrote:
>
> At 10:32 AM +0530 4/15/08, Krishna Chandra Prajapati wrote:
>
> > Hi,
> >
> > I have created a table name group.
> >
> > CREATE TABLE `group` (
> >  `group_id` int(11) NOT NULL,
> >  `group_name` varchar(128) NOT NULL,
> >  `date_created` datetime NOT NULL,
> >  `created_by` int(11) NOT NULL,
> >  `modified_by` int(11) default NULL,
> >  `status` char(1) NOT NULL default 'Y',
> >  PRIMARY KEY  (`group_id`)
> > ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
> >
> > The table was successfully created without any errors.
> > After that when i try to describe it. Its giving error.
> >
> > mysql> desc group;
> > ERROR 1064 (42000): 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 'group' at line 1
> > mysql> drop table group;
> > ERROR 1064 (42000): 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 'group' at line 1
> >
> > Does the problem is with table name.
> >
>
>  "group" is a reserved word.  See this page:
>
>  http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html
>
>  The page also describes how to quote identifiers to avoid
>  this problem.

Better yet, don't use reserved words...

-- 
Rob Wultsch
[EMAIL PROTECTED]
wultsch (aim)

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Create table

[Velen]

Hi,

If you really need to call it group, try using :
`group` in your query (use the ` thing)

Regards,

Velen



- Original Message -
From: "Paul DuBois" <[EMAIL PROTECTED]>
To: "Krishna Chandra Prajapati" <[EMAIL PROTECTED]>; "mysql"

Sent: Tuesday, April 15, 2008 6:27 PM
Subject: Re: Create table


> At 10:32 AM +0530 4/15/08, Krishna Chandra Prajapati wrote:
> >Hi,
> >
> >I have created a table name group.
> >
> >CREATE TABLE `group` (
> >   `group_id` int(11) NOT NULL,
> >   `group_name` varchar(128) NOT NULL,
> >   `date_created` datetime NOT NULL,
> >   `created_by` int(11) NOT NULL,
> >   `modified_by` int(11) default NULL,
> >   `status` char(1) NOT NULL default 'Y',
> >   PRIMARY KEY  (`group_id`)
> >) ENGINE=MyISAM DEFAULT CHARSET=latin1;
> >
> >The table was successfully created without any errors.
> >After that when i try to describe it. Its giving error.
> >
> >mysql> desc group;
> >ERROR 1064 (42000): 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 'group' at line 1
> >mysql> drop table group;
> >ERROR 1064 (42000): 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 'group' at line 1
> >
> >Does the problem is with table name.
>
> "group" is a reserved word.  See this page:
>
> http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html
>
> The page also describes how to quote identifiers to avoid
> this problem.
>
> --
> Paul DuBois, MySQL Documentation Team
> Madison, Wisconsin, USA
> MySQL AB, www.mysql.com
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Create table

[Paul DuBois]

At 10:32 AM +0530 4/15/08, Krishna Chandra Prajapati wrote:

Hi,

I have created a table name group.

CREATE TABLE `group` (
  `group_id` int(11) NOT NULL,
  `group_name` varchar(128) NOT NULL,
  `date_created` datetime NOT NULL,
  `created_by` int(11) NOT NULL,
  `modified_by` int(11) default NULL,
  `status` char(1) NOT NULL default 'Y',
  PRIMARY KEY  (`group_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

The table was successfully created without any errors.
After that when i try to describe it. Its giving error.

mysql> desc group;
ERROR 1064 (42000): 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 'group' at line 1
mysql> drop table group;
ERROR 1064 (42000): 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 'group' at line 1

Does the problem is with table name.


"group" is a reserved word.  See this page:

http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html

The page also describes how to quote identifiers to avoid
this problem.

--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Create table

[Carlos Proal]

Hi.

group is a reserved word
i think you must enclose the table name with single quotes (as when you 
created the table) in order to avoid the confusion.


Carlos


Krishna Chandra Prajapati wrote:

Hi,

I have created a table name group.

CREATE TABLE `group` (
  `group_id` int(11) NOT NULL,
  `group_name` varchar(128) NOT NULL,
  `date_created` datetime NOT NULL,
  `created_by` int(11) NOT NULL,
  `modified_by` int(11) default NULL,
  `status` char(1) NOT NULL default 'Y',
  PRIMARY KEY  (`group_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

The table was successfully created without any errors.
After that when i try to describe it. Its giving error.

mysql> desc group;
ERROR 1064 (42000): 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 'group' at line 1
mysql> drop table group;
ERROR 1064 (42000): 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 'group' at line 1

Does the problem is with table name.

Thanks,
  



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Create table

[Ananda Kumar]

Do a "show tables" and see what is the actual table name.
I think "group" is a key work and hence its giving you the error.

See the table names in that database.

regards
anandkl


On 4/15/08, Krishna Chandra Prajapati <[EMAIL PROTECTED]> wrote:
>
> Hi,
>
> I have created a table name group.
>
> CREATE TABLE `group` (
> `group_id` int(11) NOT NULL,
> `group_name` varchar(128) NOT NULL,
> `date_created` datetime NOT NULL,
> `created_by` int(11) NOT NULL,
> `modified_by` int(11) default NULL,
> `status` char(1) NOT NULL default 'Y',
> PRIMARY KEY  (`group_id`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
>
> The table was successfully created without any errors.
> After that when i try to describe it. Its giving error.
>
> mysql> desc group;
> ERROR 1064 (42000): 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 'group' at line 1
> mysql> drop table group;
> ERROR 1064 (42000): 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 'group' at line 1
>
> Does the problem is with table name.
>
> Thanks,
> --
> Krishna Chandra Prajapati
> Email-id: [EMAIL PROTECTED]
>

Re: Create Table Warning

[dpgirago]

> This is a create table statement output from mysqldump from a 4.0.24 
> installation. 
> 
> Restoring on 5.0.22 gives a warning. 
> 
> Can anyone enlighten me?
> 
> I guess I'm a little behind on my reading...
> 
> David
> 
> mysql> CREATE TABLE `container` (
> ->   `carrier` varchar(128) NOT NULL default '',
> ->   `lock1` varchar(7) NOT NULL default '',
> ->   `lock2` varchar(7) NOT NULL default '',
> ->   `lock3` varchar(7) NOT NULL default '',
> ->   `lock4` varchar(7) NOT NULL default '',
> ->   `lock5` varchar(7) NOT NULL default '',
> ->   `lock6` varchar(7) NOT NULL default '',
> ->   `lock7` varchar(7) NOT NULL default '',
> ->   `lock8` varchar(7) NOT NULL default '',
> ->   `lock9` varchar(7) NOT NULL default '',
> ->   `lock10` varchar(7) NOT NULL default '',
> ->   `lock11` varchar(7) NOT NULL default '',
> ->   `lock12` varchar(7) NOT NULL default '',
> ->   `lock13` varchar(7) NOT NULL default '',
> ->   `lock14` varchar(7) NOT NULL default '',
> ->   `lock15` varchar(7) NOT NULL default '',
> ->   `lock16` varchar(7) NOT NULL default '',
> ->   `lock17` varchar(7) NOT NULL default '',
> ->   `lock18` varchar(7) NOT NULL default '',
> ->   `lock19` varchar(7) NOT NULL default '',
> ->   `lock20` varchar(7) NOT NULL default '',
> ->   `lock21` varchar(7) NOT NULL default '',
> ->   `lock22` varchar(7) NOT NULL default '',
> ->   `lock23` varchar(7) NOT NULL default '',
> ->   `lock24` varchar(7) NOT NULL default '',
> ->   PRIMARY KEY  (`carrier`)
> -> ) TYPE=MyISAM;
> Query OK, 0 rows affected, 1 warning (0.00 sec)

I would say the warning is caused by TYPE=MyISAM.  TYPE= is deprecated in 
5.x, and is 
being replaced by ENGINE=.  If you execute SHOW WARNINGS right after a 
statement that 
generates warnings, you will see the warning text.

Cheers
Baron

%%

mysql> show warnings;
+-+--+--+
| Level   | Code | Message  |
+-+--+--+
| Warning | 1287 | 'TYPE=storage_engine' is deprecated; use 
'ENGINE=storage_engine' instead |
+-+--+--+
1 row in set (0.00 sec)

%%

Yup, that's it.

Thanks, Baron and Rhys.

Kind Regards,

David

RE: Create Table Warning

[Rhys Campbell]

Do a "SHOW WARNINGS" at the command line.

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: 13 June 2007 16:11
To: mysql@lists.mysql.com
Subject: Create Table Warning


This is a create table statement output from mysqldump from a 4.0.24 
installation. 

Restoring on 5.0.22 gives a warning. 

Can anyone enlighten me?

I guess I'm a little behind on my reading...

David

mysql> CREATE TABLE `container` (
->   `carrier` varchar(128) NOT NULL default '',
->   `lock1` varchar(7) NOT NULL default '',
->   `lock2` varchar(7) NOT NULL default '',
->   `lock3` varchar(7) NOT NULL default '',
->   `lock4` varchar(7) NOT NULL default '',
->   `lock5` varchar(7) NOT NULL default '',
->   `lock6` varchar(7) NOT NULL default '',
->   `lock7` varchar(7) NOT NULL default '',
->   `lock8` varchar(7) NOT NULL default '',
->   `lock9` varchar(7) NOT NULL default '',
->   `lock10` varchar(7) NOT NULL default '',
->   `lock11` varchar(7) NOT NULL default '',
->   `lock12` varchar(7) NOT NULL default '',
->   `lock13` varchar(7) NOT NULL default '',
->   `lock14` varchar(7) NOT NULL default '',
->   `lock15` varchar(7) NOT NULL default '',
->   `lock16` varchar(7) NOT NULL default '',
->   `lock17` varchar(7) NOT NULL default '',
->   `lock18` varchar(7) NOT NULL default '',
->   `lock19` varchar(7) NOT NULL default '',
->   `lock20` varchar(7) NOT NULL default '',
->   `lock21` varchar(7) NOT NULL default '',
->   `lock22` varchar(7) NOT NULL default '',
->   `lock23` varchar(7) NOT NULL default '',
->   `lock24` varchar(7) NOT NULL default '',
->   PRIMARY KEY  (`carrier`)
-> ) TYPE=MyISAM;
Query OK, 0 rows affected, 1 warning (0.00 sec)

This email is confidential and may also be privileged. If you are not the 
intended recipient please notify us immediately by telephoning +44 (0)20 7452 
5300 or email [EMAIL PROTECTED] You should not copy it or use it for any 
purpose nor disclose its contents to any other person. Touch Local cannot 
accept liability for statements made which are clearly the sender's own and are 
not made on behalf of the firm.

Touch Local Limited
Registered Number: 2885607
VAT Number: GB896112114
Cardinal Tower, 12 Farringdon Road, London EC1M 3NN
+44 (0)20 7452 5300


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Create Table Warning

[Baron Schwartz]

Hi ,

[EMAIL PROTECTED] wrote:
This is a create table statement output from mysqldump from a 4.0.24 
installation. 

Restoring on 5.0.22 gives a warning. 


Can anyone enlighten me?

I guess I'm a little behind on my reading...

David

mysql> CREATE TABLE `container` (
->   `carrier` varchar(128) NOT NULL default '',
->   `lock1` varchar(7) NOT NULL default '',
->   `lock2` varchar(7) NOT NULL default '',
->   `lock3` varchar(7) NOT NULL default '',
->   `lock4` varchar(7) NOT NULL default '',
->   `lock5` varchar(7) NOT NULL default '',
->   `lock6` varchar(7) NOT NULL default '',
->   `lock7` varchar(7) NOT NULL default '',
->   `lock8` varchar(7) NOT NULL default '',
->   `lock9` varchar(7) NOT NULL default '',
->   `lock10` varchar(7) NOT NULL default '',
->   `lock11` varchar(7) NOT NULL default '',
->   `lock12` varchar(7) NOT NULL default '',
->   `lock13` varchar(7) NOT NULL default '',
->   `lock14` varchar(7) NOT NULL default '',
->   `lock15` varchar(7) NOT NULL default '',
->   `lock16` varchar(7) NOT NULL default '',
->   `lock17` varchar(7) NOT NULL default '',
->   `lock18` varchar(7) NOT NULL default '',
->   `lock19` varchar(7) NOT NULL default '',
->   `lock20` varchar(7) NOT NULL default '',
->   `lock21` varchar(7) NOT NULL default '',
->   `lock22` varchar(7) NOT NULL default '',
->   `lock23` varchar(7) NOT NULL default '',
->   `lock24` varchar(7) NOT NULL default '',
->   PRIMARY KEY  (`carrier`)
-> ) TYPE=MyISAM;
Query OK, 0 rows affected, 1 warning (0.00 sec)


I would say the warning is caused by TYPE=MyISAM.  TYPE= is deprecated in 5.x, and is 
being replaced by ENGINE=.  If you execute SHOW WARNINGS right after a statement that 
generates warnings, you will see the warning text.


Cheers
Baron

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Re: CREATE TABLE Inv_Id

[Jerry Schwartz]

How about the UUID() function. As I recall, the differences between a UUID
and Microsoft's GUID are trivial.

INSERT INTO Inv_Id SET id = UUID();

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341


> -Original Message-
> From: Martijn Tonies [mailto:[EMAIL PROTECTED]
> Sent: Thursday, October 19, 2006 9:45 AM
> To: Dan Buettner; Scott Hamm
> Cc: Mysql
> Subject: Re: Re: CREATE TABLE Inv_Id
>
>
>
>
> > Scott, what's wrong with 'PRIMARY KEY' ?
>
> A PRIMARY KEY has nothing to do with the "uniqueidentifier" datatype.
>
> A "uniqueidentifier" is a GUID.
>
> Martijn Tonies
> Database Workbench - development tool for MySQL, and more!
> Upscene Productions
> http://www.upscene.com
> My thoughts:
> http://blog.upscene.com/martijn/
> Database development questions? Check the forum!
> http://www.databasedevelopmentforum.com
>
> >
> > On 10/19/06, Scott Hamm <[EMAIL PROTECTED]> wrote:
> > > Wish MySQL would have something like what Microsoft use,
> "uniqueidentifier"
> > > as datatype.
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
>
>




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Re: CREATE TABLE Inv_Id

[Martijn Tonies]

> Scott, what's wrong with 'PRIMARY KEY' ?

A PRIMARY KEY has nothing to do with the "uniqueidentifier" datatype.

A "uniqueidentifier" is a GUID.

Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

>
> On 10/19/06, Scott Hamm <[EMAIL PROTECTED]> wrote:
> > Wish MySQL would have something like what Microsoft use,
"uniqueidentifier"
> > as datatype.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Re: CREATE TABLE Inv_Id

[Dan Buettner]

Scott, what's wrong with 'PRIMARY KEY' ?

Dan

On 10/19/06, Scott Hamm <[EMAIL PROTECTED]> wrote:

Wish MySQL would have something like what Microsoft use, "uniqueidentifier"
as datatype.




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: CREATE TABLE Inv_Id

[Scott Hamm]

Wish MySQL would have something like what Microsoft use, "uniqueidentifier"
as datatype.

Re: CREATE TABLE Inv_Id

[Scott Hamm]

Thanks!



On 10/16/06, Gabriel PREDA <[EMAIL PROTECTED]> wrote:


I would try:

CREATE TABLE Inv_Id (
ID INT(12) UNSIGNED ZEROFILL AUTO_INCREMENT PRIMARY KEY,
MID INT NOT NULL,
FOREIGN (MID) REFERENCES 'Model' (ID)
);

Note the UNSIGNED and ZEROFILL flags !

-- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Gabriel PREDA
Senior Web Developer





--

`Twas brillig, and the slithy toves
Did gyre and gimble in the wabe:
All mimsy were the borogoves,
And the mome raths outgrabe.

Re: CREATE TABLE Inv_Id

[Gabriel PREDA]

I would try:

CREATE TABLE Inv_Id (
ID INT(12) UNSIGNED ZEROFILL AUTO_INCREMENT PRIMARY KEY,
MID INT NOT NULL,
FOREIGN (MID) REFERENCES 'Model' (ID)
);

Note the UNSIGNED and ZEROFILL flags !

-- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Gabriel PREDA
Senior Web Developer

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: CREATE TABLE Inv_Id

[Rolando Edwards]

You may want to simulate autoincrementing
by means of Triggers

You may need to make ID a 12 character string first.

Then create a trigger something like this:

CREATE TRIGGER addingInv BEFORE INSERT ON Inv_Id
FOR EACH ROW SET @ID= '...'

You will have to also add what Gerald mentioned (casecode prefix,
a manufacturers id, a partnumber, and a check digit) to the table definition.

- Original Message -
From: Gerald L. Clark <[EMAIL PROTECTED]>
To: Scott Hamm <[EMAIL PROTECTED]>
Cc: Mysql  
Sent: Monday, October 16, 2006 2:31:47 PM GMT-0500 US/Eastern
Subject: Re: CREATE TABLE Inv_Id

Scott Hamm wrote:
> I'm trying to create a table as follows:
> 
> CREATE TABLE Inv_Id (
> ID INT(12) AUTO_INCREMENT PRIMARY KEY,
> MID INT NOT NULL,
> FOREIGN (MID) REFERENCES 'Model' (ID)
> );
> 
> How do I make ID to start out as '0001' for UPC barcode assignment?

UPC barcodes are not sequential numbers.
They are composed of a casecode prefix, a manufacturers id, a 
partnumber, and a check digit.

You cannot use an autoincrement number as a UPC.

-- 
Gerald L. Clark
Supplier Systems Corporation

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: CREATE TABLE Inv_Id

[Scott Hamm]

Alright, I'll abandon UPC, I'm trying to serialize all components i.e. video
cards, keyboards, sound cards, etc and when I process these info into
database I would like for it to give me the image of bar code with number on
bottom and small enough to put on the metal side of components i.e. ethernet
card, sound card and so on. Here is what I found so far:

http://www.ashberg.de/php-barcode/index.php?code=01234567890&scale=2&bar=UPC

The reason why I select UPC is because it is almost impossible to become
unreadable compared to other encodes.

Any better suggestions?

On 10/16/06, Gerald L. Clark <[EMAIL PROTECTED]> wrote:


Scott Hamm wrote:
> I'm trying to create a table as follows:
>
> CREATE TABLE Inv_Id (
> ID INT(12) AUTO_INCREMENT PRIMARY KEY,
> MID INT NOT NULL,
> FOREIGN (MID) REFERENCES 'Model' (ID)
> );
>
> How do I make ID to start out as '0001' for UPC barcode
assignment?

UPC barcodes are not sequential numbers.
They are composed of a casecode prefix, a manufacturers id, a
partnumber, and a check digit.

You cannot use an autoincrement number as a UPC.

--
Gerald L. Clark
Supplier Systems Corporation





--

`Twas brillig, and the slithy toves
Did gyre and gimble in the wabe:
All mimsy were the borogoves,
And the mome raths outgrabe.

Re: CREATE TABLE Inv_Id

[Dan Buettner]

Hmmm.  When the table is first created, the id number should default
to starting at 1.  Once the table has had entries added and deleted
you can run an ALTER TABLE to reset it to 1, provided it is empty I
think.

There's no way to store an integer left-padded with zeros like that, however.

What you can do is left pad it with zeros whenever you select it:
select LPAD(id,12,0) from Inv_Id order by id;

HTH,
Dan

On 10/16/06, Scott Hamm <[EMAIL PROTECTED]> wrote:

I'm trying to create a table as follows:

CREATE TABLE Inv_Id (
ID INT(12) AUTO_INCREMENT PRIMARY KEY,
MID INT NOT NULL,
FOREIGN (MID) REFERENCES 'Model' (ID)
);

How do I make ID to start out as '0001' for UPC barcode assignment?
--

`Twas brillig, and the slithy toves
Did gyre and gimble in the wabe:
All mimsy were the borogoves,
And the mome raths outgrabe.





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: CREATE TABLE Inv_Id

[Gerald L. Clark]

Scott Hamm wrote:

I'm trying to create a table as follows:

CREATE TABLE Inv_Id (
ID INT(12) AUTO_INCREMENT PRIMARY KEY,
MID INT NOT NULL,
FOREIGN (MID) REFERENCES 'Model' (ID)
);

How do I make ID to start out as '0001' for UPC barcode assignment?


UPC barcodes are not sequential numbers.
They are composed of a casecode prefix, a manufacturers id, a 
partnumber, and a check digit.


You cannot use an autoincrement number as a UPC.

--
Gerald L. Clark
Supplier Systems Corporation

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: CREATE TABLE Failure

[Martijn Tonies]

In addition to Ben's answer...

> Hi All,
>
> I have been repeatedly trying to create the following table - without
> __ANY__ success.  Looks like there is REALLY something wrong with the
> MySQL engine or something!  Here is the script:

Posting the error itself would help.

Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

> CREATE TABLE Order (
> DID int not null,
> DeskNo int not null,
> OrderDate datetime not null, /* KOT Date */
> OrderNo int not null, /* KOT No */
> CRMID int not null, /* Customer Relationship Management ID B2PID */
> CustomerID int not null, /* PersonID of Customer, Member, etc. */
> TableNo int not null,
> WaiterID int not null, /* PersonID of Waiter */
> OrderClerkID int not null, /* PersonID of Order-Clerk */
> Status int not null, /* Order Status - Open;1;In-Process;2;
> Cooked/Ready;3;Delivered;4;
> Cancelled;-1, -2, -3, ..  */
> CONSTRAINT Order_UQ UNIQUE INDEX (DID, DeskNo, OrderDate, OrderNo),
> INDEX CRMID_NDX (CRMID),
> FOREIGN KEY CRM_FK_NDX (CRMID) REFERENCES RelationB2P (ID),
> INDEX CustomerID_NDX (CustomerID),
> FOREIGN KEY Customer_FK_NDX (PersonID) REFERENCES Person (PersonID),
> INDEX DID_NDX (DID),
> FOREIGN KEY Department_FK_NDX (DID) REFERENCES EntityBranchDept
> (DepartmentID),
> IDNEX WaiterID_NDX (WaiterID),
> FOREIGN KEY WaiterID_FK_NDX (WaiterID) REFERENCES Person (PersonID),
> INDEX OrderClerkID (OrderClerkID),
> FOREIGN KEY OrderClerk_FK_NDX (OrderClerkID) REFERENCES Person (PersonID)
> ) ENGINE=InnoDB;
>
>
>
>
>
> You guys can delete all the index/references and other clauses!  Even
> then this table doesn't get created!
>
> I'd appreciate your help.
>
> --
> Asif
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>
>


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: CREATE TABLE Failure

[Dan Buettner]

Asif, I note 3 problems:

1 - your table is named 'order', which is a reserved word in MySQL and 
most other db engines.  I'd suggest using a different name.  If you must 
use 'order' for the name, enclose it in backticks, a la `order`, in the 
create statement.


2 - you have IDNEX instead of INDEX on the WaiterID column.

3 - you reference a constraint on the PersonID column, which is not a 
column in this table.


Good luck.

Dan


Asif Lodhi wrote:

Hi All,

I have been repeatedly trying to create the following table - without
__ANY__ success.  Looks like there is REALLY something wrong with the
MySQL engine or something!  Here is the script:

CREATE TABLE Order (
DIDint not null,
DeskNoint not null,
OrderDatedatetimenot null,/* KOT Date */
OrderNoint not null,/* KOT No */
CRMIDint not null,/* Customer Relationship 
Management ID B2PID */
CustomerIDint not null,/* PersonID of Customer, 
Member, etc. */

TableNoint not null,
WaiterIDint not null,/* PersonID of Waiter */
OrderClerkIDint not null,/* PersonID of Order-Clerk */
Statusintnot null,/* Order Status - 
Open;1;In-Process;2;

Cooked/Ready;3;Delivered;4;
Cancelled;-1, -2, -3, ..  */
CONSTRAINT Order_UQ UNIQUE INDEX (DID, DeskNo, OrderDate, 
OrderNo),

INDEX CRMID_NDX (CRMID),
FOREIGN KEY CRM_FK_NDX (CRMID) REFERENCES RelationB2P (ID),
INDEX CustomerID_NDX (CustomerID),
FOREIGN KEY Customer_FK_NDX (PersonID) REFERENCES Person 
(PersonID),

INDEX DID_NDX (DID),
FOREIGN KEY Department_FK_NDX (DID) REFERENCES 
EntityBranchDept

(DepartmentID),
IDNEX WaiterID_NDX (WaiterID),
FOREIGN KEY WaiterID_FK_NDX (WaiterID) REFERENCES Person 
(PersonID),

INDEX OrderClerkID (OrderClerkID),
FOREIGN KEY OrderClerk_FK_NDX (OrderClerkID) REFERENCES 
Person (PersonID)

) ENGINE=InnoDB;





You guys can delete all the index/references and other clauses!  Even
then this table doesn't get created!

I'd appreciate your help.

--
Asif



--
Dan Buettner

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: CREATE TABLE x AS with ENGINE = ARCHIVE

[Terry Burton]

On 3/28/06, Peter Brawley <[EMAIL PROTECTED]> wrote:
> >I require something along the lines of this:
>
> >CREATE TABLE old AS SELECT * FROM request_log ENGINE=ARCHIVE
>
> CREATE TABLE tblname ENGINE=archive SELECT * FROM request_log;
>
> PB

Excellent. Exactly what I need. Thanks :-)

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: CREATE TABLE x AS with ENGINE = ARCHIVE

[Peter Brawley]

Terry


I require something along the lines of this:



CREATE TABLE old AS SELECT * FROM request_log ENGINE=ARCHIVE


CREATE TABLE tblname ENGINE=archive SELECT * FROM request_log;

PB

-

Terry Burton wrote:

Hi,

I'm using MySQL 4.11. Is it possible to use the CREATE TABLE x AS
syntax alongside the ENGINE = x pragma, since this would make
archiving of tables very simple.

I require something along the lines of this:

CREATE TABLE old AS SELECT * FROM request_log ENGINE=ARCHIVE

If this cannot be done then I can always get equivalent functionality
by performing CREATE TABLE x (...) ENGINE=x followed by INSERT INTO x
SELECT * FROM x, however this is not so neat since it require
knowledge of the source table structure which makes it less ideal for
automation.


Many thanks,

Tez

  



--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.3.2/293 - Release Date: 3/26/2006


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: CREATE TABLE x AS with ENGINE = ARCHIVE

[Terry Burton]

On 3/28/06, Pure Web Solution <[EMAIL PROTECTED]> wrote:
> I dont think that it is possible to specify the engine when creating a table
> this way, you could however create the table using the:
>
> CREATE TABLE old AS SELECT * FROM request_log
>
> and then issue an alter table command setting the engine to whatever you like.

Thanks Paul,

That solution seems a little wasteful in terms of resources though.

If there is no nice way to achieve this operation then do people think
that it would be a good idea to push for the inclusion of this
functionality.

If more folks than myself can see the benefit in this then please
speak up, in which can I'll cross post this to the development list to
see what the devs make of it.


Warm regards,

Tez

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: CREATE TABLE x AS with ENGINE = ARCHIVE

[Pure Web Solution]

I dont think that it is possible to specify the engine when creating a table
this way, you could however create the table using the:

CREATE TABLE old AS SELECT * FROM request_log

and then issue an alter table command setting the engine to whatever you like.

Pure Web Solution
http://www.purewebsolution.co.uk
PHP, MYSQL, Web Design & Web Services

"Terry Burton" <[EMAIL PROTECTED]> wrote:

> Hi,
> 
> I'm using MySQL 4.11. Is it possible to use the CREATE TABLE x AS
> syntax alongside the ENGINE = x pragma, since this would make
> archiving of tables very simple.
> 
> I require something along the lines of this:
> 
> CREATE TABLE old AS SELECT * FROM request_log ENGINE=ARCHIVE
> 
> If this cannot be done then I can always get equivalent functionality
> by performing CREATE TABLE x (...) ENGINE=x followed by INSERT INTO x
> SELECT * FROM x, however this is not so neat since it require
> knowledge of the source table structure which makes it less ideal for
> automation.
> 
> 
> Many thanks,
> 
> Tez
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:   
> http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 

Pure Web Solution
http://www.purewebsolution.co.uk
PHP, MYSQL, Web Design & Web Services



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: CREATE TABLE LIKE in MySQL 3.23

[Konrad Billewicz]

Jigal van Hemert  spill.nl> writes:

> http://dev.mysql.com/doc/mysql/en/show-create-table.html
> 
> Use output of SHOW CREATE TABLE, modify name and execute that query.

Superb solution. I didn't know that MySQL has such a command. My question was 
very basic. Sorry for bothering.

Best regards,
Konrad Billewicz






-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: CREATE TABLE LIKE in MySQL 3.23

[Jigal van Hemert]

Konrad Billewicz wrote:

Hello,

I would like to do thing exactly like CREATE TABLE a LIKE b. But... I have MySQL 
3.23 and this command is available since 4.1. How would you handle this task 
using this, older MySQL?


http://dev.mysql.com/doc/mysql/en/show-create-table.html

Use output of SHOW CREATE TABLE, modify name and execute that query.

Regards, Jigal.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: CREATE TABLE and specifying DEFAULT

[Danny Stolle]

Les Schaffer wrote:

Gordon Bruce wrote:


I just ran the creates on 4.0.20 on LINUX and 5.0.6 on Windows
 



upgrading to 5.0.6 solved the problem.

do i need to let MySQL developers know about this or do they monitor the 
list or once a release is gone, i can assume THIS problem was fixed?


thanks to all for the fast help. i am new to the mysql list and i can 
see it "works".


les schaffer


Les,

i don't know if you have managed to solve your problem. it took a while 
but i asked my brother to try it on his mysql which is on a windows 
platform. the result was that he also got the nulls after

ID_streettype_spec_hosp. he is working with version 5.0 mysql server.

this has got to be a bug, or there must be something else going on.

Danny Stolle

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: CREATE TABLE and specifying DEFAULT

[Les Schaffer]

Gordon Bruce wrote:


I just ran the creates on 4.0.20 on LINUX and 5.0.6 on Windows
 



upgrading to 5.0.6 solved the problem.

do i need to let MySQL developers know about this or do they monitor the 
list or once a release is gone, i can assume THIS problem was fixed?


thanks to all for the fast help. i am new to the mysql list and i can 
see it "works".


les schaffer

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: CREATE TABLE and specifying DEFAULT

[Danny Stolle]

Les Schaffer wrote:

Gordon Bruce wrote:


I just ran the creates on 4.0.20 on LINUX and 5.0.6 on Windows



i was running 5.0.4 on Windows:

mysql> \s
--
mysql  Ver 14.9 Distrib 5.0.4-beta, for Win32 (ia32)


switching to 5.0.6 now. anyone wanna make a bet  ;-)

les schaffer



euh .. would that be on a windows platform ;^) ?? i am running the 14.7 
on a linux distro and i have no trouble since ...


danny

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: CREATE TABLE and specifying DEFAULT

[Danny Stolle]

Les Schaffer wrote:

Gordon Bruce wrote:


I just ran the creates on 4.0.20 on LINUX and 5.0.6 on Windows



i was running 5.0.4 on Windows:

mysql> \s
--
mysql  Ver 14.9 Distrib 5.0.4-beta, for Win32 (ia32)


switching to 5.0.6 now. anyone wanna make a bet  ;-)

les schaffer



euh .. would that be on a windows platform ;^) ?? i am running the 14.7 
on a linux distro and i have no trouble since ...


danny

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: CREATE TABLE and specifying DEFAULT

[Les Schaffer]

Gordon Bruce wrote:

I just ran the creates on 4.0.20 on LINUX and 5.0.6 on Windows 



i was running 5.0.4 on Windows:

mysql> \s
--
mysql  Ver 14.9 Distrib 5.0.4-beta, for Win32 (ia32)


switching to 5.0.6 now. anyone wanna make a bet  ;-)

les schaffer

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: CREATE TABLE and specifying DEFAULT

[Gordon Bruce]

I just ran the creates on 4.0.20 on LINUX and 5.0.6 on Windows and they
both seem to look fine {see the SHOW CREATE TABLE's following the CREATE
TABLE statements}

RUN ON 4.0.20

mysql> CREATE TABLE ID (
-> mat INT UNIQUE PRIMARY KEY,
-> ID_firstname CHAR(35) DEFAULT 'filler',
-> ID_lastname CHAR(35) DEFAULT 'filler',
-> ID_ramqnb CHAR(12) DEFAULT 'filler',
-> ID_numciv_hosp CHAR(10) DEFAULT '-9',
-> ID_appt_hosp CHAR(10) DEFAULT '-9',
-> ID_streetname_hosp CHAR(75) DEFAULT '-9',
-> ID_streettype_hosp CHAR(6) DEFAULT '-9',
-> ID_streettype_spec_hosp CHAR(25) DEFAULT 'humbug',
-> ID_direction_hosp CHAR(2) DEFAULT '-9',
-> ID_city_hosp CHAR(50) DEFAULT '-9',
-> ID_city_spec_hosp CHAR(150) DEFAULT 'filler',
-> ID_province_hosp CHAR(2) DEFAULT 'QC',
-> ID_postal_code_hosp CHAR(7) DEFAULT '-9',
-> ID_phone_number_hosp CHAR(12) DEFAULT '-9',
-> ID_work_number_hosp CHAR(20) DEFAULT '-9',
-> ID_cell_number_hosp CHAR(12) DEFAULT '-9',
-> ID_numciv_study CHAR(10) DEFAULT '-9'
-> );
Query OK, 0 rows affected (0.03 sec)


mysql> show create table ID;
+---+---
-

-
| Table | Create Table

+---+---
-

-
| ID| CREATE TABLE `ID` (
  `mat` int(11) NOT NULL default '0',
  `ID_firstname` char(35) default 'filler',
  `ID_lastname` char(35) default 'filler',
  `ID_ramqnb` char(12) default 'filler',
  `ID_numciv_hosp` char(10) default '-9',
  `ID_appt_hosp` char(10) default '-9',
  `ID_streetname_hosp` char(75) default '-9',
  `ID_streettype_hosp` char(6) default '-9',
  `ID_streettype_spec_hosp` char(25) default 'humbug',
  `ID_direction_hosp` char(2) default '-9',
  `ID_city_hosp` char(50) default '-9',
  `ID_city_spec_hosp` char(150) default 'filler',
  `ID_province_hosp` char(2) default 'QC',
  `ID_postal_code_hosp` char(7) default '-9',
  `ID_phone_number_hosp` char(12) default '-9',
  `ID_work_number_hosp` char(20) default '-9',
  `ID_cell_number_hosp` char(12) default '-9',
  `ID_numciv_study` char(10) default '-9',
  PRIMARY KEY  (`mat`),
  UNIQUE KEY `mat` (`mat`)
) TYPE=MyISAM |
+---+---
-

-
1 row in set (0.00 sec)


___
RUN ON 5.0.6


mysql> CREATE TABLE ID (
-> mat INT UNIQUE PRIMARY KEY,
-> ID_firstname CHAR(35) DEFAULT 'filler',
-> ID_lastname CHAR(35) DEFAULT 'filler',
-> ID_ramqnb CHAR(12) DEFAULT 'filler',
-> ID_numciv_hosp CHAR(10) DEFAULT '-9',
-> ID_appt_hosp CHAR(10) DEFAULT '-9',
-> ID_streetname_hosp CHAR(75) DEFAULT '-9',
-> ID_streettype_hosp CHAR(6) DEFAULT '-9',
-> ID_streettype_spec_hosp CHAR(25) DEFAULT 'humbug',
-> ID_direction_hosp CHAR(2) DEFAULT '-9',
-> ID_city_hosp CHAR(50) DEFAULT '-9',
-> ID_city_spec_hosp CHAR(150) DEFAULT 'filler',
-> ID_province_hosp CHAR(2) DEFAULT 'QC',
-> ID_postal_code_hosp CHAR(7) DEFAULT '-9',
-> ID_phone_number_hosp CHAR(12) DEFAULT '-9',
-> ID_work_number_hosp CHAR(20) DEFAULT '-9',
-> ID_cell_number_hosp CHAR(12) DEFAULT '-9',
-> ID_numciv_study CHAR(10) DEFAULT '-9'
-> );
Query OK, 0 rows affected (0.91 sec)

mysql>
mysql> CREATE TABLE ID1 (
-> mat INT PRIMARY KEY UNIQUE,
-> ID_firstname CHAR(35),
-> ID_lastname CHAR(35),
-> ID_ramqnb CHAR(12),
-> ID_numciv_hosp CHAR(10) DEFAULT '-9',
-> ID_appt_hosp CHAR(10) DEFAULT '-9',
-> ID_streetname_hosp CHAR(75) DEFAULT '-9',
->  ID_streettype_hosp CHAR(6) DEFAULT '-9',
->  ID_streettype_spec_hosp CHAR(25),
-> ID_direction_hosp CHAR(2) DEFAULT '-9',
-> ID_city_hosp CHAR(50) DEFAULT '-9',
-> ID_city_spec_hosp CHAR(150),
-> ID_province_hosp CHAR(2) DEFAULT 'QC',
-> ID_postal_code_hosp CHAR(7) DEFAULT '-9',
-> ID_phone_number_hosp CHAR(12) DEFAULT '-9',
-> ID_work_number_hosp CHAR(20) DEFAULT '-9',
-> ID_cell_number_hosp CHAR(12) DEFAULT '-9'
->
-> );
Query OK, 0 rows affected (0.16 sec)

mysql> show create table ID;
+---+---
-

-
| Table | Create Table

+---+

Re: create table with foreign key

[mfatene]

Hi,
I think it's because you added a comma after the second line in the "create
table item". So this table was not created.

manufacturer_id varchar(50),  must be manufacturer_id varchar(50))

When done, i had no problem to create my FK (4.1.11)


Mathias


Selon Scott Purcell <[EMAIL PROTECTED]>:

> Hello,
>
> I have this simple table of unique items.
>
> CREATE TABLE ITEM (
>id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
>manufacturer_id varchar(50),
> ) TYPE=InnoDB;
>
> Foreach item, I can have 1 to many assets. So I am trying to use this:
> CREATE TABLE ITEM_ASSET_REL (
>id INT,
>asset_id VARCHAR(10),
>FOREIGN KEY(id) references ITEM(id) on DELETE CASCADE
> ) TYPE=InnoDB;
>
> But I get the error 150. Now if I make the 'item_asset_rel' id UNIQUE, then
> it is happy. But that defeats my purpose of having many asset_id to one item.
>
> Can anyone please, please help.
>
> Thanks,
>
>
> Scott K Purcell | Developer | VERTIS |
> 555 Washington Ave. 4th Floor | St. Louis, MO 63101 |
> 314.588.0720 Ext:1320 | [EMAIL PROTECTED] | http://www.vertisinc.com
>
> Vertis is the premier provider of targeted advertising, media, and
> marketing services that drive consumers to marketers more effectively.
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: create table with foreign key

[SGreen]

Re-read the section on foreign keys 
(http://dev.mysql.com/doc/mysql/en/innodb-foreign-key-constraints.html). 
It clearly says (in the second and third bullets) that you must index the 
columns on both ends of a FK. 

Add an index to ITEM_ASSET_REL that contains id as its first term then you 
will be able to setup the FK relationship.  If you are doing it from 
scratch, here is what the CREATE TABLE would look like:

CREATE TABLE ITEM_ASSET_REL (
   id INT,
   asset_id VARCHAR(10),
   KEY(id),
   FOREIGN KEY(id) references ITEM(id) on DELETE CASCADE
) TYPE=InnoDB;


And, if you have another table of assets (called ASSET) and this table is 
part of a many-to-many relationship (because each asset can be assigned to 
more than one item at a time):

CREATE TABLE ITEM_ASSET_REL (
   id INT,
   asset_id VARCHAR(10),
   KEY(id),
   KEY(asset_id),
   FOREIGN KEY(asset_id) references ASSET(id) on DELETE CASCADE
   FOREIGN KEY(id) references ITEM(id) on DELETE CASCADE
) TYPE=InnoDB;

Make sense?

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

"Scott Purcell" <[EMAIL PROTECTED]> wrote on 04/29/2005 12:32:56 PM:

> Hello,
> 
> I have this simple table of unique items.
> 
> CREATE TABLE ITEM (
>id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
>manufacturer_id varchar(50),
> ) TYPE=InnoDB;
> 
> Foreach item, I can have 1 to many assets. So I am trying to use this:
> CREATE TABLE ITEM_ASSET_REL (
>id INT,
>asset_id VARCHAR(10),
>FOREIGN KEY(id) references ITEM(id) on DELETE CASCADE
> ) TYPE=InnoDB;
> 
> But I get the error 150. Now if I make the 'item_asset_rel' id 
> UNIQUE, then it is happy. But that defeats my purpose of having many
> asset_id to one item.
> 
> Can anyone please, please help.
> 
> Thanks,
> 
> 
> Scott K Purcell | Developer | VERTIS |
> 555 Washington Ave. 4th Floor | St. Louis, MO 63101 |
> 314.588.0720 Ext:1320 | [EMAIL PROTECTED] | 
http://www.vertisinc.com
> 
> Vertis is the premier provider of targeted advertising, media, and 
> marketing services that drive consumers to marketers more effectively. 
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> 

Re: Create table

[Michael Stassen]

rahul pathak wrote:
Hi,
  I am using the "Create table select * into ..." query to create a 
table using two exsisting tables
Do you mean "CREATE TABLE ... SELECT..." or "INSERT INTO table SELECT ..."?
But the table is created with only 1000 rows, which should not be the 
case.Is there a config setting which defaults the rowcount to 1000?
  I am using the MySQL control center.
MySQL Control Center has a default LIMIT 1000, which probably shouldn't 
apply to "CREATE  SELECT" or "INSERT ... SELECT".  I believe you can 
change that from within mysqlcc.  You can also change it when starting from 
the command line (select_limit option) 
, or set it in your option 
file (my.cnf|.ini) .

thanks,
r
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: create table....select * from table anomaly..

[Egor Egorov]

It's rather a different behaviour. We suggest to upgrade to 4.0.21 in this
case. 





-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   <___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Create Table Error 1071

[Anil Doppalapudi]

There might be limitation on length of index in mysql

try this

CREATE TABLE `adminpages` (

`adminpageid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT ,
`file_name` VARCHAR( 250 ) NOT NULL ,
`page_title` VARCHAR( 250 ) NOT NULL ,
PRIMARY KEY ( `adminpageid` ) ,
INDEX ( `file_name` , `page_title` )
) COMMENT = 'Listing of all pages the administration module'

just change the data length of columns from 255 to 250 for both the columns.
I tried it on my local server. is is ok

Anil
DBA




-Original Message-
From: GH [mailto:[EMAIL PROTECTED]
Sent: Monday, October 11, 2004 12:16 AM
To: [EMAIL PROTECTED]
Subject: Create Table Error 1071


How can I fix the following error? I got this via phpMyAdmin



Error

SQL-query :

CREATE TABLE `adminpages` (

`adminpageid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT ,
`file_name` VARCHAR( 255 ) NOT NULL ,
`page_title` VARCHAR( 255 ) NOT NULL ,
PRIMARY KEY ( `adminpageid` ) ,
INDEX ( `file_name` , `page_title` )
) COMMENT = 'Listing of all pages the administration module'

MySQL said:


#1071 - Specified key was too long. Max key length is 500

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Create table syntax question

[Michael Stassen]

eifion herbert (IAH-C) wrote:
Thanks for that. I've got it to accept it now, and also realised that
mySQL will let me insert anything I like into columns that are
supposedly foreign keys.
That depends on your table type.  MySQL will enforce your foreign keys in 
tables that support them. (i.e. InnoDB, not MyISAM).

Guess it's a toss up between creating InnoDB tables and the associated
indices, or doing something application side to enforce RI.

-Original Message-
From: Michael Stassen [mailto:[EMAIL PROTECTED] 
Sent: 16 June 2004 16:43
To: eifion herbert (IAH-C)
Cc: [EMAIL PROTECTED]
Subject: Re: Create table syntax question


eifion herbert (IAH-C) wrote:

Hi,
Probably a very basic question.
I'm trying to a create a table in mySQL 4.0.15 thus:
create table VACANCIES(
VACREF char(6) NOT NULL PRIMARY KEY,
TITLE varchar(60),
LOC varchar(9),
DESC text,
STARTDATE date,
GROUP varchar(25),
CONSTRAINT fk_grp FOREIGN KEY (GROUP) REFERENCES GROUPS(GPNAME), 
CONSTRAINT chk_loc CHECK (LOC in ('Loc1', 'Loc2', 'Loc3'));

And it says I have a syntax error. I've been through the manual and 
can't spot what I've done wrong. Anyone?
DESC is a reserved word.  Best bet would be to choose a 
different name, but 
if you must name this column DESC, you will have to quote the 
name with 
backticks in the CREATE statement, and every time you use it.

Same goes for GROUP.
You're short a ) at the end.
The CONSTRAINTs will be parsed but silently ignored unless 
you make this an 
InnoDB table by adding TYPE=InnoDB at the end {after the last 
')'}.  If you 
do make it an InnoDB table, you can't make a FOREIGN KEY 
constraint on GROUP 
unless both GROUP and GROUPS.GPNAME are indexed.

So, assuming GROUPS.GPNAME is already indexed and you want to 
keep the 
reserved words DESC and GROUP as column names, you'd need

  CREATE TABLE VACANCIES(
  VACREF char(6) NOT NULL PRIMARY KEY,
  TITLE varchar(60),
  LOC varchar(9),
  `DESC` text,
  STARTDATE date,
  `GROUP` varchar(25),
  INDEX gp_idx (`GROUP`),
  CONSTRAINT fk_grp FOREIGN KEY (`GROUP`) REFERENCES GROUPS(GPNAME),
  CONSTRAINT chk_loc CHECK (LOC IN ('Loc1', 'Loc2', 'Loc3')))
  TYPE = InnoDB;
Michael



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Create table syntax question

[eifion herbert (IAH-C)]

Thanks for that. I've got it to accept it now, and also realised that
mySQL will let me insert anything I like into columns that are
supposedly foreign keys.

Guess it's a toss up between creating InnoDB tables and the associated
indeces, or doing something application side to enforce RI.

> -Original Message-
> From: Michael Stassen [mailto:[EMAIL PROTECTED] 
> Sent: 16 June 2004 16:43
> To: eifion herbert (IAH-C)
> Cc: [EMAIL PROTECTED]
> Subject: Re: Create table syntax question
> 
> 
> 
> eifion herbert (IAH-C) wrote:
> 
> > Hi,
> > 
> > Probably a very basic question.
> > 
> > I'm trying to a create a table in mySQL 4.0.15 thus:
> > 
> > create table VACANCIES(
> > VACREF char(6) NOT NULL PRIMARY KEY,
> > TITLE varchar(60),
> > LOC varchar(9),
> > DESC text,
> > STARTDATE date,
> > GROUP varchar(25),
> > CONSTRAINT fk_grp FOREIGN KEY (GROUP) REFERENCES GROUPS(GPNAME), 
> > CONSTRAINT chk_loc CHECK (LOC in ('Loc1', 'Loc2', 'Loc3'));
> > 
> > And it says I have a syntax error. I've been through the manual and 
> > can't spot what I've done wrong. Anyone?
> 
> DESC is a reserved word.  Best bet would be to choose a 
> different name, but 
> if you must name this column DESC, you will have to quote the 
> name with 
> backticks in the CREATE statement, and every time you use it.
> 
> Same goes for GROUP.
> 
> You're short a ) at the end.
> 
> The CONSTRAINTs will be parsed but silently ignored unless 
> you make this an 
> InnoDB table by adding TYPE=InnoDB at the end {after the last 
> ')'}.  If you 
> do make it an InnoDB table, you can't make a FOREIGN KEY 
> constraint on GROUP 
> unless both GROUP and GROUPS.GPNAME are indexed.
> 
> So, assuming GROUPS.GPNAME is already indexed and you want to 
> keep the 
> reserved words DESC and GROUP as column names, you'd need
> 
>CREATE TABLE VACANCIES(
>VACREF char(6) NOT NULL PRIMARY KEY,
>TITLE varchar(60),
>LOC varchar(9),
>`DESC` text,
>STARTDATE date,
>`GROUP` varchar(25),
>INDEX gp_idx (`GROUP`),
>CONSTRAINT fk_grp FOREIGN KEY (`GROUP`) REFERENCES GROUPS(GPNAME),
>CONSTRAINT chk_loc CHECK (LOC IN ('Loc1', 'Loc2', 'Loc3')))
>TYPE = InnoDB;
> 
> Michael
> 
> 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Create table syntax question

[Michael Stassen]

eifion herbert (IAH-C) wrote:
Hi,
Probably a very basic question.
I'm trying to a create a table in mySQL 4.0.15 thus:
create table VACANCIES(
VACREF char(6) NOT NULL PRIMARY KEY,
TITLE varchar(60),
LOC varchar(9),
DESC text,
STARTDATE date,
GROUP varchar(25),
CONSTRAINT fk_grp FOREIGN KEY (GROUP) REFERENCES GROUPS(GPNAME),
CONSTRAINT chk_loc CHECK (LOC in ('Loc1', 'Loc2', 'Loc3'));
And it says I have a syntax error. I've been through the manual and
can't spot what I've done wrong. Anyone?
DESC is a reserved word.  Best bet would be to choose a different name, but 
if you must name this column DESC, you will have to quote the name with 
backticks in the CREATE statement, and every time you use it.

Same goes for GROUP.
You're short a ) at the end.
The CONSTRAINTs will be parsed but silently ignored unless you make this an 
InnoDB table by adding TYPE=InnoDB at the end {after the last ')'}.  If you 
do make it an InnoDB table, you can't make a FOREIGN KEY constraint on GROUP 
unless both GROUP and GROUPS.GPNAME are indexed.

So, assuming GROUPS.GPNAME is already indexed and you want to keep the 
reserved words DESC and GROUP as column names, you'd need

  CREATE TABLE VACANCIES(
  VACREF char(6) NOT NULL PRIMARY KEY,
  TITLE varchar(60),
  LOC varchar(9),
  `DESC` text,
  STARTDATE date,
  `GROUP` varchar(25),
  INDEX gp_idx (`GROUP`),
  CONSTRAINT fk_grp FOREIGN KEY (`GROUP`) REFERENCES GROUPS(GPNAME),
  CONSTRAINT chk_loc CHECK (LOC IN ('Loc1', 'Loc2', 'Loc3')))
  TYPE = InnoDB;
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Create table syntax question

[Mike Johnson]

From: eifion herbert (IAH-C) [mailto:[EMAIL PROTECTED]

> Hi,
> 
> Probably a very basic question.
> 
> I'm trying to a create a table in mySQL 4.0.15 thus:
> 
> create table VACANCIES(
> VACREF char(6) NOT NULL PRIMARY KEY,
> TITLE varchar(60),
> LOC varchar(9),
> DESC text,
> STARTDATE date,
> GROUP varchar(25),
> CONSTRAINT fk_grp FOREIGN KEY (GROUP) REFERENCES GROUPS(GPNAME),
> CONSTRAINT chk_loc CHECK (LOC in ('Loc1', 'Loc2', 'Loc3'));
> 
> And it says I have a syntax error. I've been through the manual and
> can't spot what I've done wrong. Anyone?

I'm willing to bet that it's because DESC is a reserved word (an abbreviation of 
DESCRIBE, used to show a table structure).

Either change the name of that column or wrap it in backticks (`DESC`) whenever it's 
referenced.

HTH!


-- 
Mike Johnson
Web Developer
Smarter Living, Inc.
phone (617) 886-5539

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Create table results in (errno: 121)

[Adam]

Tom,

More specifically, find out what the error messages means by using the 
`SHOW INNODB STATUS` command. This will give a description of the last 
error message generated by InnoDb. In your case, its definitely the 
foreign key that is causing the problem. As far as what it is 
specifically, I'd reference the message, and if that doesn't help bring 
the message to the group.

Regards,
Adam
On Apr 22, 2004, at 9:29 AM, Tom Brown wrote:

Hi,

I'm trying to create a table with the following syntax

CREATE TABLE TNSession
(
SessionID int(11) NOT NULL auto_increment,
IPAddress varchar(50) default NULL,
Created datetime default NULL,
Expired tinyint(4) default '0',
AccountID int(11) default '0',
PRIMARY KEY (SessionID),
KEY FK_AccountID (AccountID),
CONSTRAINT `0_20` FOREIGN KEY (`AccountID`) REFERENCES `TNAccount`
(`AccountID`)
) TYPE=InnoDB ROW_FORMAT=DYNAMIC;
it results in a

ERROR 1005: Can't create table './TNMailServer/TNSession.frm' (errno: 
121)

This is on version mysql-standard-4.0.18-pc-linux-i686

Can anyone shed any light on this as it has me stumped - There is a
TNAccount table with a AccountID column?
thanks for any assistance

Tom



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]



This electronic mail transmission contains confidential information intended only for the person(s) named. Any use, distribution, copying or disclosure by another person is strictly prohibited.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Create table results in (errno: 121)

[beacker]

> ERROR 1005: Can't create table './TNMailServer/TNSession.frm' (errno: 121)
>
> This is on version mysql-standard-4.0.18-pc-linux-i686

If this is on a typical linux box, errno 121 is

#define EREMOTEIO   121 /* Remote I/O error */

typically relate to an NFS mounted file system.  Does the MySQL server have
permission to write on this mounted file system?  Or might there be an issue
with the way the remote system is mounted?
 Brad Eacker ([EMAIL PROTECTED])



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Create table results in (errno: 121)

[Tom Brown]

> Is it indexed?
>
> And don't forget you can use the command
> SHOW INNODB STATUS\G
>
> to show the last FK related error.

ahh yes - i did not know about this command


LATEST FOREIGN KEY ERROR

040422 14:39:09 Error in foreign key constraint creation for table
TNMailServer/TNSession.
A foreign key constraint of name TNMailServer/0_20
already exists (note that internally InnoDB adds 'databasename/'
in front of the user-defined constraint name).

thankyou for your quick response as this has fixed my error

Tom



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Create table results in (errno: 121)

[Martijn Tonies]

Hi,

> I'm trying to create a table with the following syntax
>
> CREATE TABLE TNSession
> (
> SessionID int(11) NOT NULL auto_increment,
> IPAddress varchar(50) default NULL,
> Created datetime default NULL,
> Expired tinyint(4) default '0',
> AccountID int(11) default '0',
> PRIMARY KEY (SessionID),
> KEY FK_AccountID (AccountID),
> CONSTRAINT `0_20` FOREIGN KEY (`AccountID`) REFERENCES `TNAccount`
> (`AccountID`)
> ) TYPE=InnoDB ROW_FORMAT=DYNAMIC;
>
> it results in a
>
> ERROR 1005: Can't create table './TNMailServer/TNSession.frm' (errno: 121)
>
> This is on version mysql-standard-4.0.18-pc-linux-i686
>
> Can anyone shed any light on this as it has me stumped - There is a
> TNAccount table with a AccountID column?

Is it indexed?

And don't forget you can use the command
SHOW INNODB STATUS\G

to show the last FK related error.


With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: create table problems??

[Paul DuBois]

At 19:09 -0400 4/19/04, Andy B wrote:
hi..

i am trying to create this table:
CREATE TABLE rnjresort_events (
  Id int(25) NOT NULL auto_increment,
  Type enum('Annual','OneTime') binary NOT NULL default 'Annual',
  StartDate varchar(200) binary NOT NULL default '',
  EndDate varchar(200) binary NOT NULL default '',
  Name varchar(100) binary NOT NULL default '',
  County varchar(50) binary NOT NULL default '',
  Notes varchar(255) binary NOT NULL default '',
  StartingDay varchar(30) NOT NULL default '',
  StartingMonth int(2) default NULL,
  StartingYear year(4) default NULL,
  EndingDay varchar(30) NOT NULL default '',
  EndingMonth int(2) default NULL,
  EndingYear year(4) default NULL,
  PRIMARY KEY  (Id)
) TYPE=MyISAM CHARSET=latin1;
it was created on a test server (mysql 4.1-alpha) and needs to be created on
the real server (mysql 4.0.18)...
i try and do a \. events2.sql to run the script its in to create it and all
i end up getting on the real server is this:
error 1064: syntax error. check the manual for the correct syntax to use for
your version of mysql near default 'Annual',   StartDate varchar(200) binary
NOT NULL default '',
  EndDate varchar(200) bina
any ideas what could be the problem with this? the table unfortunately needs
to be created exactly as it is shown above...
Why is that?

If that's really true, then you cannot do it.  ENUM does not support
BINARY in 4.0.  (In addition, the CHARSET table option at the end is 
not supported, though that will just be ignored.)

If you have to create the table *exactly* as shown above, you're requiring
backward compatibility that does not exist.  If you can make some changes,
then declare the table like this:
CREATE TABLE rnjresort_events (
  Id int(25) NOT NULL auto_increment,
  Type enum('Annual','OneTime') NOT NULL default 'Annual',
  StartDate varchar(200) binary NOT NULL default '',
  EndDate varchar(200) binary NOT NULL default '',
  Name varchar(100) binary NOT NULL default '',
  County varchar(50) binary NOT NULL default '',
  Notes varchar(255) binary NOT NULL default '',
  StartingDay varchar(30) NOT NULL default '',
  StartingMonth int(2) default NULL,
  StartingYear year(4) default NULL,
  EndingDay varchar(30) NOT NULL default '',
  EndingMonth int(2) default NULL,
  EndingYear year(4) default NULL,
  PRIMARY KEY  (Id)
) TYPE=MyISAM;

 i wondering if the "default
''" sections of the column defines are giving it the problem...shrug


--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Create Table Syntax

[Khashan]

Thanks a lot, I think I have enough to validate the syntax (sort of).
 
Ahmad Khashan

Michael Stassen <[EMAIL PROTECTED]> wrote:

Khashan wrote:

> **None of these are "table level" modifiers**
> 
> Sorry, I guess I used the wrong terminology. I am aware of the table
> options available. What I meant is “additional definitions” after the
> columns have been defined. 

Sorry I misunderstood you.

> Now, do I understand that you are saying that a user can specify any of :
> 
> 
> | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
> 
> | KEY [index_name] [index_type] (index_col_name,...)
> 
> | INDEX [index_name] [index_type] (index_col_name,...)
> 
> | [CONSTRAINT [symbol]] UNIQUE [INDEX]
> 
> [index_name] [index_type] (index_col_name,...)
> 
> | FULLTEXT [INDEX] [index_name] (index_col_name,...)
> 
> | [CONSTRAINT [symbol]] FOREIGN KEY
> 
> [index_name] (index_col_name,...) [reference_definition]
> 
> | CHECK (expr)
> 
> after the column definitions, except for the primary key(if it is already defined )? 

Sort of. You can mix column and index definitions, so long as the columns 
referenced in the index creation are defined. Order doesn't matter. For 
example:

mysql> CREATE TABLE keytest (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> INDEX idn (id, name),
-> name CHAR(10) NOT NULL, flag INT);
Query OK, 0 rows affected (0.01 sec)

mysql> show create table keytest;
+-+---+
| Table | Create Table 

|
+-+---+
| keytest | CREATE TABLE `keytest` (
`id` int(11) NOT NULL auto_increment,
`name` char(10) NOT NULL default '',
`flag` int(11) default NULL,
PRIMARY KEY (`id`),
KEY `idn` (`id`,`name`)
) TYPE=MyISAM |

(I'm using 4.0.17.) Note that you can define an INDEX before you are done 
defining COLUMNs, and you can even include a column you haven't defined yet 
in an index, so long as you do define it. Note also that if you ask MySQL 
for the create definitions, it lists the indexes, including the primary key, 
after the columns.

Michael


Do you Yahoo!?
Yahoo! Mail - More reliable, more storage, less spam

Re: Create Table Syntax

[Michael Stassen]

Khashan wrote:

**None of these are "table level" modifiers**

Sorry, I guess I used the wrong terminology. I am aware of the table
options available. What I meant is “additional definitions” after the
columns have been defined. 
Sorry I misunderstood you.

Now, do I understand that you are saying that a user can specify  any of :

| [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)

  | KEY [index_name] [index_type] (index_col_name,...)

  | INDEX [index_name] [index_type] (index_col_name,...)

  | [CONSTRAINT [symbol]] UNIQUE [INDEX]

[index_name] [index_type] (index_col_name,...)

  | FULLTEXT [INDEX] [index_name] (index_col_name,...)

  | [CONSTRAINT [symbol]] FOREIGN KEY

[index_name] (index_col_name,...) [reference_definition]

  | CHECK (expr)

after the column definitions, except for the primary key(if it is already defined )? 
Sort of.  You can mix column and index definitions, so long as the columns 
referenced in the index creation are defined.  Order doesn't matter.  For 
example:

mysql> CREATE TABLE keytest (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> INDEX idn (id, name),
-> name CHAR(10) NOT NULL, flag INT);
Query OK, 0 rows affected (0.01 sec)
mysql> show create table keytest;
+-+---+
| Table   | Create Table 

   |
+-+---+
| keytest | CREATE TABLE `keytest` (
  `id` int(11) NOT NULL auto_increment,
  `name` char(10) NOT NULL default '',
  `flag` int(11) default NULL,
  PRIMARY KEY  (`id`),
  KEY `idn` (`id`,`name`)
) TYPE=MyISAM |
(I'm using 4.0.17.)  Note that you can define an INDEX before you are done 
defining COLUMNs, and you can even include a column you haven't defined yet 
in an index, so long as you do define it.  Note also that if you ask MySQL 
for the create definitions, it lists the indexes, including the primary key, 
 after the columns.

Michael



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Create Table Syntax

[Khashan]

**None of these are "table level" modifiers**

Sorry, I guess I used the wrong terminology. I am aware of the table options 
available. What I meant is “additional definitions” after the columns have been 
defined.  

Now, do I understand that you are saying that a user can specify  any of :

 

| [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)

  | KEY [index_name] [index_type] (index_col_name,...)

  | INDEX [index_name] [index_type] (index_col_name,...)

  | [CONSTRAINT [symbol]] UNIQUE [INDEX]

[index_name] [index_type] (index_col_name,...)

  | FULLTEXT [INDEX] [index_name] (index_col_name,...)

  | [CONSTRAINT [symbol]] FOREIGN KEY

[index_name] (index_col_name,...) [reference_definition]

  | CHECK (expr)

 

after the column definitions, except for the primary key(if it is already defined )? 



Michael Stassen <[EMAIL PROTECTED]> wrote:

Khashan wrote:

> Thanks for the reply. 
> It seems like the syntax for create_table/create_definition
> should have 2 part-2 modifiers

I think you are misreading the syntax. That is, I think you are confusing 
the index-related create_definitions with the table_options.

> 1- one part-2 'column' modifiers for the each column.
> and 

The column modifiers are

[NOT NULL | NULL] [DEFAULT default_value]
[AUTO_INCREMENT] [[PRIMARY] KEY] [COMMENT 'string']
[reference_definition]

So, you can effectively create an index on a column as you define the column 
by declaring it as the PRIMARY KEY. (UNIQUE seems to work, as well.)

> 2- another part-2 table modifiers for the table as a whole ?
> ( at the end of column definitions).
> two questions:
> -- Are any of the these modifiers:
> - Primary Key
> - Index
> - KEY 
> - Unique 
> mutually exclusive at the column or table level? 
> ( INDEX and KEY are the same, so only one is allowed) 

None of these are "table level" modifiers. They are additional 
"create_definitions", similar to the column create definition, except that 
they create indexes on already defined columns. The table_options, "table 
level modifiers" as you call them, are further down on the manual page 
. You cannot create indexes 
in the table_options section.

> -- Does it make sense for a user to specify 'Primary Key' as part of
> a column modifiers and then again specify 'Primary Key' as part of 
> the table modifiers?

No. You can only have one PRIMARY KEY. That's what primary means.

Michael

> Ahmad Khashan
> 
> Rhino wrote:
> 
> - Original Message - 
> From: "Khashan" 
> 
>>I am looking at the syntax for creating table( section 6.5.3 of the
> 
> manual):
> 
>>CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
> 
> [(create_definition,...)] [table_options] [select_statement]
> 
>>
>>
>>and:
>>
>>create_definition:
>>
>>part-1 (<- inserted by me for reference)
>>
>>col_name type [NOT NULL | NULL] etc...[reference_definition]
>>
>>part-2 (<- inserted by me for reference)
>>
>>| PRIMARY KEY (index_col_name,...)
>>
>>| KEY [index_name] (index_col_name,...)
>>
>>| INDEX [index_name] (index_col_name,...)
>>
>>| UNIQUE [INDEX] [index_name] (index_col_name,...)
>>
>>| FULLTEXT [INDEX] [index_name] (index_col_name,...)
>>
>>| [CONSTRAINT symbol] FOREIGN KEY .. [reference_definition]
>>
>>| CHECK (expr)
>>
>>
>>
>>The syntax suggests that for each column, in addition to specifying
> 
> part-1, I can specify any or all of the options in part-2 ???.
> 
>>1- Is this true.
>>
>>
> 
> Yes.
> 
>>2- Can I specify the options in part-2 after I defined all my columns. I
> 
> have used things like:
> 
>>CREATE TABLE animals (
>>
>>id MEDIUMINT NOT NULL AUTO_INCREMENT,
>>
>>name CHAR(30) NOT NULL,
>>
>>PRIMARY KEY (id)
>>
>>);
>>
> 
> Yes, this is the way a CREATE TABLE normally looks: after each column
> name/datatype/null specification ("part 1" in your terminology) come the
> various attributes that further define the column ("part 2" in your
> terminology). A given column can have one or more of these attributes.
> 
> Rhino



Do you Yahoo!?
Yahoo! Mail - More reliable, more storage, less spam

Re: Create Table Syntax

[Michael Stassen]

Khashan wrote:

 Thanks for the reply. 
It seems like the syntax for create_table/create_definition
 should have 2 part-2 modifiers
I think you are misreading the syntax.  That is, I think you are confusing 
the index-related create_definitions with the table_options.

1- one part-2 'column' modifiers for the each column.
and 
The column modifiers are

[NOT NULL | NULL] [DEFAULT default_value]
[AUTO_INCREMENT] [[PRIMARY] KEY] [COMMENT 'string']
[reference_definition]
So, you can effectively create an index on a column as you define the column 
by declaring it as the PRIMARY KEY.  (UNIQUE seems to work, as well.)

2- another part-2 table modifiers for the table as a whole ?
 ( at the end of column definitions).
two questions:
 --  Are any of the these modifiers:
 - Primary Key
 - Index
 - KEY 
 - Unique 
 mutually exclusive at the column or table level? 
   ( INDEX and KEY are the same, so only one is allowed) 
None of these are "table level" modifiers.  They are additional 
"create_definitions", similar to the column create definition, except that 
they create indexes on already defined columns.  The table_options, "table 
level modifiers" as you call them, are further down on the manual page 
.  You cannot create indexes 
in the table_options section.

 -- Does it make sense for a user to specify 'Primary Key' as part of
 a column modifiers and then again specify 'Primary Key' as part of 
the table modifiers?
No.  You can only have one PRIMARY KEY.  That's what primary means.

Michael

Ahmad Khashan

Rhino <[EMAIL PROTECTED]> wrote:

- Original Message - 
From: "Khashan" 

I am looking at the syntax for creating table( section 6.5.3 of the
manual):

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)] [table_options] [select_statement]



and:

create_definition:

part-1 (<- inserted by me for reference)

col_name type [NOT NULL | NULL] etc...[reference_definition]

part-2 (<- inserted by me for reference)

| PRIMARY KEY (index_col_name,...)

| KEY [index_name] (index_col_name,...)

| INDEX [index_name] (index_col_name,...)

| UNIQUE [INDEX] [index_name] (index_col_name,...)

| FULLTEXT [INDEX] [index_name] (index_col_name,...)

| [CONSTRAINT symbol] FOREIGN KEY .. [reference_definition]

| CHECK (expr)



The syntax suggests that for each column, in addition to specifying
part-1, I can specify any or all of the options in part-2 ???.

1- Is this true.


Yes.

2- Can I specify the options in part-2 after I defined all my columns. I
have used things like:

CREATE TABLE animals (

id MEDIUMINT NOT NULL AUTO_INCREMENT,

name CHAR(30) NOT NULL,

PRIMARY KEY (id)

);

Yes, this is the way a CREATE TABLE normally looks: after each column
name/datatype/null specification ("part 1" in your terminology) come the
various attributes that further define the column ("part 2" in your
terminology). A given column can have one or more of these attributes.
Rhino


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Create Table Syntax

[Khashan]

 Thanks for the reply. 
It seems like the syntax for create_table/create_definition
 should have 2 part-2 modifiers
1- one part-2 'column' modifiers for the each column.
and 
2- another part-2 table modifiers for the table as a whole ?
 ( at the end of column definitions).
two questions:
 --  Are any of the these modifiers:
 - Primary Key
 - Index
 - KEY 
 - Unique 
 mutually exclusive at the column ot table level? 
   ( INDEX and KEY are the same, so only one is allowed) 
 -- Does it make sense for a user to specify 'Primary Key' as part of
 a column modifiers and then again specify 'Primary Key' as part of 
the table modifiers?
Ahmad Khashan

Rhino <[EMAIL PROTECTED]> wrote:

- Original Message - 
From: "Khashan" 
To: 
Sent: Thursday, April 01, 2004 1:04 PM
Subject: Create Table Syntax


>
> I am looking at the syntax for creating table( section 6.5.3 of the
manual):
>
> CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)] [table_options] [select_statement]
>
>
>
> and:
>
> create_definition:
>
> part-1 (<- inserted by me for reference)
>
> col_name type [NOT NULL | NULL] etc...[reference_definition]
>
> part-2 (<- inserted by me for reference)
>
> | PRIMARY KEY (index_col_name,...)
>
> | KEY [index_name] (index_col_name,...)
>
> | INDEX [index_name] (index_col_name,...)
>
> | UNIQUE [INDEX] [index_name] (index_col_name,...)
>
> | FULLTEXT [INDEX] [index_name] (index_col_name,...)
>
> | [CONSTRAINT symbol] FOREIGN KEY .. [reference_definition]
>
> | CHECK (expr)
>
>
>
> The syntax suggests that for each column, in addition to specifying
part-1, I can specify any or all of the options in part-2 ???.
>
> 1- Is this true.
>
>
Yes.
>
> 2- Can I specify the options in part-2 after I defined all my columns. I
have used things like:
>
> CREATE TABLE animals (
>
> id MEDIUMINT NOT NULL AUTO_INCREMENT,
>
> name CHAR(30) NOT NULL,
>
> PRIMARY KEY (id)
>
> );
>
Yes, this is the way a CREATE TABLE normally looks: after each column
name/datatype/null specification ("part 1" in your terminology) come the
various attributes that further define the column ("part 2" in your
terminology). A given column can have one or more of these attributes.

Rhino

Do you Yahoo!?
Yahoo! Mail - More reliable, more storage, less spam

Re: Create Table Syntax

[Rhino]

- Original Message - 
From: "Khashan" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, April 01, 2004 1:04 PM
Subject: Create Table Syntax


>
> I am looking at the syntax for creating table( section 6.5.3 of the
manual):
>
> CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)] [table_options] [select_statement]
>
>
>
> and:
>
> create_definition:
>
>   part-1 (<- inserted by me for reference)
>
>   col_name type [NOT NULL | NULL] etc...[reference_definition]
>
>  part-2 (<- inserted by me for reference)
>
>   | PRIMARY KEY (index_col_name,...)
>
>   | KEY [index_name] (index_col_name,...)
>
>   | INDEX [index_name] (index_col_name,...)
>
>   | UNIQUE [INDEX] [index_name] (index_col_name,...)
>
>   | FULLTEXT [INDEX] [index_name] (index_col_name,...)
>
>   | [CONSTRAINT symbol] FOREIGN KEY ..   [reference_definition]
>
>   | CHECK (expr)
>
>
>
> The syntax suggests that for each column, in addition to specifying
part-1, I can specify any or all of the options in part-2 ???.
>
> 1- Is this true.
>
>
Yes.
>
> 2- Can I specify the options in part-2 after I defined all my columns. I
have used things like:
>
> CREATE TABLE animals (
>
>  id MEDIUMINT NOT NULL AUTO_INCREMENT,
>
>  name CHAR(30) NOT NULL,
>
>  PRIMARY KEY (id)
>
>  );
>
Yes, this is the way a CREATE TABLE normally looks: after each column
name/datatype/null specification ("part 1" in your terminology) come the
various attributes that further define the column ("part 2" in your
terminology). A given column can have one or more of these attributes.

Rhino


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Create table 'LIKE' ....Error 1064

[Paul DuBois]

At 7:48 -0800 3/28/04, Khashan wrote:
Hello All,
I have MySQL 4.0.16 on Windows 2000 pro.
I am trying to create a table in an existing database.
according to the manual, I can use one of these statements:
6.5.3 CREATE TABLE Syntax
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)]
[table_options] [select_statement]
or
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(] LIKE old_tbl_name [)];
I am trying to use the second option. I have a table students and i 
want to create another table students2 using the 'LIKE'  students. 
Here are the results:

mysql> CREATE TABLE students2 LIKE students;
ERROR 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 'LIKE students' at line 1
mysql> CREATE TABLE students2 (LIKE students);
ERROR 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 'LIKE students)' at line 1

1064 is ER_PARSE_ERROR
I tried all the tricks i know to no avail. I really don't see the 
error in this simple command,!!!.   Any help would be appricated.
Farther down the manual page:

In MySQL 4.1, you can also use LIKE to create a table based on the 
definition of another table, including any column attributes and 
indexes the original table has:
CREATE TABLE new_tbl LIKE orig_tbl;

So you need 4.1 for this to work.

--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
MySQL Users Conference: April 14-16, 2004
http://www.mysql.com/uc2004/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: CREATE TABLE IF NOT EXISTS behavior (should it add rows if the table doesn't exist?)

[Paul DuBois]

At 14:42 -0800 3/2/04, Mark Brunkhart wrote:
I apologize if this has been discussed or reported as a bug (I searched a bit
but couldn't find mention of it).
When you create a table using the CREATE TABLE IF NOT EXISTS...SELECT syntax,
what is the correct behavior if the table already exists?
It should add rows.  IF NOT EXISTS applies to the CREATE, not to the SELECT.

--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
MySQL Users Conference: April 14-16, 2004
http://www.mysql.com/uc2004/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Create Table

[Victoria Reznichenko]

Cassiano Prado Oliveira <[EMAIL PROTECTED]> wrote:
> [-- text/plain, encoding 8bit, charset: iso-8859-1, 22 lines --]
> 
> Why when i create a table like this:
> CREATE TABLE `prmsnctabelas2` (
>  `cdempresa` varchar(5) NOT NULL DEFAULT '',
>  `cdfilial` CHAR(2) NOT NULL DEFAULT '',
>  `nometabela` char(50) NOT NULL DEFAULT '',
>  PRIMARY KEY  (`cdempresa`,`cdfilial`,`nometabela`)
> ) TYPE=MyISAM CHARSET=latin1
> 
> the result is:
> CREATE TABLE `prmsnctabelas3` (
>  `cdempresa` varchar(5) NOT NULL DEFAULT '',
>  `cdfilial` CHAR(2) NOT NULL DEFAULT '',
>  `nometabela` varchar(50) NOT NULL DEFAULT '',
>  PRIMARY KEY  (`cdempresa`,`cdfilial`,`nometabela`)
> ) TYPE=MyISAM CHARSET=latin1
> 
> Why the type of field nometabela was changed to varchar???
> Just 'cause the structure has a field with this type?

It's a known issue. If table has VARCHAR, TEXT or BLOB column all CHAR columns longer 
than 3 characters are changed to VARCHAR:
http://www.mysql.com/doc/en/Silent_column_changes.html


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   <___/   www.mysql.com





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: CREATE TABLE

[Heikki Tuuri]

Jeremiah,

if you are using a fairly recent version of MySQL, you can print with

SHOW INNODB STATUS\G

a detailed explanation of the latest foreign key error.

Best regards,

Heikki Tuuri
Innobase Oy
http://www.innodb.com
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables

Order MySQL technical support from https://order.mysql.com/


- Original Message - 
From: "Aleksandar Bradaric" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.myodbc
Sent: Friday, January 23, 2004 9:23 PM
Subject: Re: CREATE TABLE


> Hi,
>
> > Error:
> > ERROR 1005: Can't create table
> > './tamiyausa/user_shipping_info.frm' (errno:
> > 150)
>
> C:\mysql\bin>perror 150
> Error code 150:  Unknown error
> 150 = Foreign key constraint is incorrectly formed
>
> Look  like  your  foreign  keys are not properly defined. Do
> both tables exist? And the fields you are referencing?
>
>
> Take care,
> Aleksandar
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: CREATE TABLE

[Aleksandar Bradaric]

Hi,

> Error:
> ERROR 1005: Can't create table
> './tamiyausa/user_shipping_info.frm' (errno:
> 150)

C:\mysql\bin>perror 150
Error code 150:  Unknown error
150 = Foreign key constraint is incorrectly formed

Look  like  your  foreign  keys are not properly defined. Do
both tables exist? And the fields you are referencing?


Take care,
Aleksandar


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: CREATE TABLE and CHECK Clause

[Egor Egorov]

"Kraan Deufe" <[EMAIL PROTECTED]> wrote:
> ok here we are,
> 
> first of all, hi to every one since i'm new to this list.
> 
> then it's my first real advanced experience with mysql, even if i've already
> used SQL Server, PostgreSQL and (kof kof) Access.
> 
> well, then i'm trying to create a heavy database with many relations and
> check clauses.
> 
> And i'm blocking on the check clause constraint.
> 
> So i'm asking here for precisions:
> is the check clause working ? (i've seen some post saying that it is not
> working)

No, Currently CHECK doesn't work.

> Wich kind of tables (BDB, InnoDB, ) is supporting CHECK clauses, CASCADE
> DELETE, CASCADE UPGRADE, REFERENCES.

FOREIN KEY CONSTRAINTS with ON DELETE CASCADE, ON UPDATE CASCADE is supported on 
InnoDB tables.

> and what is the problem with the following Query ? :
> 
> CREATE TABLE IF NOT EXISTS definitions_avantages (
>id_avantage BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
>nom_avantage VARCHAR(128) NOT NULL UNIQUE CONSTRAINT CHECK (<> ''),



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   <___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: CREATE TABLE and CHECK Clause

[Diana Soares]

Hi,
Check this first:
http://www.mysql.com/doc/en/CREATE_TABLE.html


On Mon, 2003-09-08 at 18:34, Kraan Deufe wrote:
> hi, it's the third time i'm trying to post this message to the list.. so
> please reply , even if it is for saying nothing.. only to be sure that this
> mail have been distributed.
> 
> Thanx
> ---
> 
> 
> 
> ok here we are,
> 
> first of all, hi to every one since i'm new to this list.
> 
> then it's my first real advanced experience with mysql, even if i've already
> used SQL Server, PostgreSQL and (kof kof) Access.
> 
> well, then i'm trying to create a heavy database with many relations and
> check clauses.
> 
> And i'm blocking on the check clause constraint.
> 
> So i'm asking here for precisions:
> is the check clause working ? (i've seen some post saying that it is not
> working)
> Wich kind of tables (BDB, InnoDB, ) is supporting CHECK clauses, CASCADE
> DELETE, CASCADE UPGRADE, REFERENCES.
> and what is the problem with the following Query ? :
> 
> CREATE TABLE IF NOT EXISTS definitions_avantages (
> id_avantage BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
> nom_avantage VARCHAR(128) NOT NULL UNIQUE CONSTRAINT CHECK (<> ''),
> description_avantage TEXT)
> 
> thanx for your help.
> 
> Kraan Deufe
> even more than Kojak ;)
> 
> 
-- 
Diana Soares


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: CREATE TABLE, INSERT INTO with SELECT in parentheses

[James Fryer]

At 10:17 am 14/08/03, Nils Valentin wrote:
The simple answer would be that the syntax says:

http://www.mysql.com/doc/en/UNION.html

SELECT...
UNION...
SELECT...
Further down the page it says

If you want to use an ORDER BY for the total UNION result, you should use 
parentheses:

(SELECT a FROM table_name WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM table_name WHERE a=11 AND B=2 ORDER BY a LIMIT 10)
ORDER BY a;
which is what I am doing.

The problem seems to be with the syntax of CREATE TABLE and INSERT INTO, 
not the syntax of UNION.

Jim
--
James Fryer  /  [EMAIL PROTECTED]  /  [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: CREATE TABLE, INSERT INTO with SELECT in parentheses

[Sergei Golubchik]

Hi!

On Aug 14, James Fryer wrote:
> Assuming a legal SELECT statement, this works fine:
> 
>   CREATE TABLE Foo SELECT ...
> 
> but this does not:
> 
>   CREATE TABLE Foo (SELECT ...)
> 
> This is a problem for me as I'd like to use the output of a 
> SELECT...UNION...ORDER BY statement as input to CREATE TABLE.
> 
> Same holds for INSERT INTO.
> 
> Is this a bug? I can't find it documented anywhere.

It's in the 4.0.14 changelog, and thus, is available in 4.0.14
 
Regards,
Sergei

-- 
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik <[EMAIL PROTECTED]>
 / /|_/ / // /\ \/ /_/ / /__  MySQL AB, Senior Software Developer
/_/  /_/\_, /___/\___\_\___/  Osnabrueck, Germany
   <___/  www.mysql.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: CREATE TABLE, INSERT INTO with SELECT in parentheses

[Nils Valentin]

Hi J smith,

Below  was my sample not James. For my sample ou will need the brackets or it 
will give you an error.

Hi James,

You are right it gives an error, but as J Smith also pointed out in your case 
you dont need the brackets, in my case you would need it. ;-)

Best regards

Nils Valentin
Tokyo/Japan




2003年 8月 14日 木曜日 17:47、Jim Smith さんは書きました:
> But why do you need the parentheses?
>
> What is wrong with
>
> CREATE TABLE Foo2
>   SELECT * FROM sensei
>   WHERE last_name IN (SELECT last_name
> FROM sensei UNION ALL
> select last_name
> FROM sensei)
>   ORDER BY e_mail;
>
> > -Original Message-
> > From: James Fryer [mailto:[EMAIL PROTECTED]
> > Sent: 14 August 2003 09:41
> > To: [EMAIL PROTECTED]
> > Subject: Re: CREATE TABLE, INSERT INTO with SELECT in parentheses
> >
> > At 09:22 am 14/08/03, Nils Valentin wrote:
> > >I don' t understand your question. Why do you hink you that
> >
> > the brackets
> >
> > >would't work ?
> > >
> > >I tried that without any problems. Please post your code.
> > >
> > >Here is mine:
> > >
> > >SELECT * FROM sensei WHERE last_name IN (SELECT last_name
> >
> > FROM sensei UNION
> >
> > >ALL select last_name FROM sensei) ORDER BY e_mail;
> >
> > Here's some code that demonstrates what I mean:
> >
> > -- Cut Here --
> > CREATE DATABASE Foobar;
> > USE Foobar;
> > CREATE TABLE Bar (id int(10) unsigned);
> >
> > # Works
> > SELECT * FROM Bar;
> >
> > # Works
> > CREATE TABLE Foo1 SELECT * FROM Bar;
> >
> > # Works
> > (SELECT * FROM Bar);
> >
> > # Fails (syntax error)
> > CREATE TABLE Foo2 (SELECT * FROM Bar);
> >
> > DROP DATABASE Foobar;
> > -- Cut Here --
> >
> > Using MacOS so please forgive any errors due to case-sensitivity.
> >
> > Jim
> > --
> > James Fryer  /  [EMAIL PROTECTED]  /  [EMAIL PROTECTED]
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> > http://lists.mysql.com/[EMAIL PROTECTED]

-- 
---
Valentin Nils
Internet Technology

 E-Mail: [EMAIL PROTECTED]
 URL: http://www.knowd.co.jp
 Personal URL: http://www.knowd.co.jp/staff/nils


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: CREATE TABLE, INSERT INTO with SELECT in parentheses

[James Fryer]

At 10:19 am 14/08/03, Jim Smith wrote:
Are you saying that any query containing parentheses fails?
No. Any query beginning with a SELECT statement in parentheses.

Jim
--
James Fryer  /  [EMAIL PROTECTED]  /  [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: CREATE TABLE, INSERT INTO with SELECT in parentheses

[Nils Valentin]

Hi James,

I don' t understand your question. Why do you hink you that the brackets 
would't work ?

I tried that without any problems. Please post your code.

Here is mine:

SELECT * FROM sensei WHERE last_name IN (SELECT last_name FROM sensei UNION 
ALL select last_name FROM sensei) ORDER BY e_mail;

Best regards

Nils Valentin
Tokyo/Japan




2003年 8月 14日 木曜日 17:04、James Fryer さんは書きました:
> Assuming a legal SELECT statement, this works fine:
>
>CREATE TABLE Foo SELECT ...
>
> but this does not:
>
>CREATE TABLE Foo (SELECT ...)
>
> This is a problem for me as I'd like to use the output of a
> SELECT...UNION...ORDER BY statement as input to CREATE TABLE.
>
> Same holds for INSERT INTO.
>
> Is this a bug? I can't find it documented anywhere.
>
> Jim
> --
> James Fryer  /  [EMAIL PROTECTED]  /  [EMAIL PROTECTED]

-- 
---
Valentin Nils
Internet Technology

 E-Mail: [EMAIL PROTECTED]
 URL: http://www.knowd.co.jp
 Personal URL: http://www.knowd.co.jp/staff/nils


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: CREATE TABLE, INSERT INTO with SELECT in parentheses

[Jim Smith]

> 
> Sorry, missed this.
> 
> They do need them if you want to use ORDER BY on the result 
> of the UNION.
> 

Only if you are also ordering the component parts of the union. 
This works

create table y
select * from x 
union
select * from x 
order by 1 desc

but this doesn't

create table y
select * from x order by a
union
select * from x order by a
order by 1 desc

This strikes me as a peculiar thing to want to do. Is it ANSI compliant?


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: CREATE TABLE, INSERT INTO with SELECT in parentheses

[James Fryer]

At 09:22 am 14/08/03, Nils Valentin wrote:
I don' t understand your question. Why do you hink you that the brackets
would't work ?
I tried that without any problems. Please post your code.

Here is mine:

SELECT * FROM sensei WHERE last_name IN (SELECT last_name FROM sensei UNION
ALL select last_name FROM sensei) ORDER BY e_mail;
Here's some code that demonstrates what I mean:

-- Cut Here --
CREATE DATABASE Foobar;
USE Foobar;
CREATE TABLE Bar (id int(10) unsigned);
# Works
SELECT * FROM Bar;
# Works
CREATE TABLE Foo1 SELECT * FROM Bar;
# Works
(SELECT * FROM Bar);
# Fails (syntax error)
CREATE TABLE Foo2 (SELECT * FROM Bar);
DROP DATABASE Foobar;
-- Cut Here --
Using MacOS so please forgive any errors due to case-sensitivity.

Jim
--
James Fryer  /  [EMAIL PROTECTED]  /  [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: CREATE TABLE, INSERT INTO with SELECT in parentheses

[James Fryer]

At 10:28 am 14/08/03, Sergei Golubchik wrote:
It's in the 4.0.14 changelog, and thus, is available in 4.0.14
Hmm, I am using

  Ver 13.5 Distrib 4.1.0-alpha, for apple-darwin6.4 (powerpc)

which I should have said in the first place, sorry. So it ought to be 
working?!?

Jim
--
James Fryer  /  [EMAIL PROTECTED]  /  [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: CREATE TABLE, INSERT INTO with SELECT in parentheses

[Nils Valentin]

Hi James,

The simple answer would be that the syntax says:

http://www.mysql.com/doc/en/UNION.html

SELECT...
UNION...
SELECT...

And thats all there is to it ;-) ... perhaps...


Exception:

The only reason to use the parenthis would be with ORDER BY (as you do), but 
than you will need to use this also in each SELECT statement.



Best regards

Nils Valentin
Tokyo/Japan


2003年 8月 14日 木曜日 18:06、James Fryer さんは書きました:
> At 09:47 am 14/08/03, Jim Smith wrote:
> >But why do you need the parentheses?
>
> This was a simplified query for example purposes.
>
> The real query looks more like
>
>(SELECT ...) UNION (SELECT ...) ORDER BY ...
>
> I could rewrite this with subselects but I have found them to be slower.
>
> My question is, why do the parentheses cause a syntax error and is this a
> bug?
>
> Jim
> --
> James Fryer  /  [EMAIL PROTECTED]  /  [EMAIL PROTECTED]

-- 
---
Valentin Nils
Internet Technology

 E-Mail: [EMAIL PROTECTED]
 URL: http://www.knowd.co.jp
 Personal URL: http://www.knowd.co.jp/staff/nils


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: CREATE TABLE, INSERT INTO with SELECT in parentheses

[Jim Smith]

But why do you need the parentheses?

What is wrong with 

CREATE TABLE Foo2
SELECT * FROM sensei 
WHERE last_name IN (SELECT last_name 
  FROM sensei UNION ALL
  select last_name 
  FROM sensei) 
  ORDER BY e_mail;
 

> -Original Message-
> From: James Fryer [mailto:[EMAIL PROTECTED]
> Sent: 14 August 2003 09:41
> To: [EMAIL PROTECTED]
> Subject: Re: CREATE TABLE, INSERT INTO with SELECT in parentheses
> 
> 
> At 09:22 am 14/08/03, Nils Valentin wrote:
> >I don' t understand your question. Why do you hink you that 
> the brackets
> >would't work ?
> >
> >I tried that without any problems. Please post your code.
> >
> >Here is mine:
> >
> >SELECT * FROM sensei WHERE last_name IN (SELECT last_name 
> FROM sensei UNION
> >ALL select last_name FROM sensei) ORDER BY e_mail;
> 
> Here's some code that demonstrates what I mean:
> 
> -- Cut Here --
> CREATE DATABASE Foobar;
> USE Foobar;
> CREATE TABLE Bar (id int(10) unsigned);
> 
> # Works
> SELECT * FROM Bar;
> 
> # Works
> CREATE TABLE Foo1 SELECT * FROM Bar;
> 
> # Works
> (SELECT * FROM Bar);
> 
> # Fails (syntax error)
> CREATE TABLE Foo2 (SELECT * FROM Bar);
> 
> DROP DATABASE Foobar;
> -- Cut Here --
> 
> Using MacOS so please forgive any errors due to case-sensitivity.
> 
> Jim
> -- 
> James Fryer  /  [EMAIL PROTECTED]  /  [EMAIL PROTECTED]
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
> 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: CREATE TABLE, INSERT INTO with SELECT in parentheses

[Jim Smith]

Are you saying that any query containing parentheses fails?
In that case sub selects won't work either.


> -Original Message-
> From: James Fryer [mailto:[EMAIL PROTECTED]
> Sent: 14 August 2003 10:07
> To: [EMAIL PROTECTED]
> Subject: RE: CREATE TABLE, INSERT INTO with SELECT in parentheses
>
>
> At 09:47 am 14/08/03, Jim Smith wrote:
> >But why do you need the parentheses?
>
> This was a simplified query for example purposes.
>
> The real query looks more like
>
>(SELECT ...) UNION (SELECT ...) ORDER BY ...

I repeat. Why do you need the parentheses? Union queries don't require them.
>
> I could rewrite this with subselects but I have found them to
> be slower.
>
> My question is, why do the parentheses cause a syntax error
> and is this a bug?
>
> Jim
> --
> James Fryer  /  [EMAIL PROTECTED]  /  [EMAIL PROTECTED]
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
>


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: CREATE TABLE, INSERT INTO with SELECT in parentheses

[Nils Valentin]

James Fryer wrote:
At 10:17 am 14/08/03, Nils Valentin wrote:

The simple answer would be that the syntax says:

http://www.mysql.com/doc/en/UNION.html

SELECT...
UNION...
SELECT...


Further down the page it says

If you want to use an ORDER BY for the total UNION result, you should 
use parentheses:

(SELECT a FROM table_name WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM table_name WHERE a=11 AND B=2 ORDER BY a LIMIT 10)
ORDER BY a;
Yup James,

Well spotted, thats the exeption which I meant. See that each SELECT 
statement has a ORDER BY clause attached, the single SELECTS as well as 
the final one ?

That seeems to be the only exception so far which I know off..

Sorry if this didn't came out so clear in my wordinbg in the last e-mail.

Best regards

Nils Valentin
Tokyo/Japan


which is what I am doing.

The problem seems to be with the syntax of CREATE TABLE and INSERT INTO, 
not the syntax of UNION.

Jim


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: CREATE TABLE, INSERT INTO with SELECT in parentheses

[James Fryer]

At 09:47 am 14/08/03, Jim Smith wrote:
But why do you need the parentheses?
This was a simplified query for example purposes.

The real query looks more like

  (SELECT ...) UNION (SELECT ...) ORDER BY ...

I could rewrite this with subselects but I have found them to be slower.

My question is, why do the parentheses cause a syntax error and is this a bug?

Jim
--
James Fryer  /  [EMAIL PROTECTED]  /  [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: CREATE TABLE, INSERT INTO with SELECT in parentheses

[Sergei Golubchik]

Hi!

On Aug 14, James Fryer wrote:
> At 10:28 am 14/08/03, Sergei Golubchik wrote:
> >It's in the 4.0.14 changelog, and thus, is available in 4.0.14
> 
> Hmm, I am using
> 
>   Ver 13.5 Distrib 4.1.0-alpha, for apple-darwin6.4 (powerpc)
> 
> which I should have said in the first place, sorry. So it ought to be 
> working?!?

No.

4.1.0 was released before 4.0.14.

All bugfixes from 4.0 are applied to the 4.1 as well, of course,
but it only means that this particular bugfix will be in 4.1.1.
 
Regards,
Sergei

-- 
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik <[EMAIL PROTECTED]>
 / /|_/ / // /\ \/ /_/ / /__  MySQL AB, Senior Software Developer
/_/  /_/\_, /___/\___\_\___/  Osnabrueck, Germany
   <___/  www.mysql.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: CREATE TABLE, INSERT INTO with SELECT in parentheses

[James Fryer]

At 10:19 am 14/08/03, Jim Smith wrote:
I repeat. Why do you need the parentheses? Union queries don't require them.
Sorry, missed this.

They do need them if you want to use ORDER BY on the result of the UNION.

Jim
--
James Fryer  /  [EMAIL PROTECTED]  /  [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: create table...checksum = 1

[Paul DuBois]

At 9:35 -0400 7/8/03, DePhillips, Michael P wrote:
Hey Folks



I'm having a tough time finding any information in the available
documentation about this feature.  Does anyone out there have a bit of
insight as to what exactly it does.


Docs says MySQL will store a checksum for each row if this is 'on'  - that's
about all I can find.
That's correct.

It causes a slight slowdown for updates (the checksum must be recalculated)
but improves table checking operations (e.g., CHECK TABLE).  It's only for
MyISAM tables.


Some general questions are:



Where is it stored?

Is it assessable?
No, which means the answer to the question before this one is "it doesn't
matter because you can't get at it." :-)
How is the checksum verified  between two databases - e.g., in a
master/slave environment?
Which technology is used (MD5 etc.)?
I'm not sure.  I believe it may be a CRC algorithm.  MD5 would be
overkill for this kind of thing.


An even more general is, what would be a scenario in which I would use this
feature?  I'm thinking it would be nice to verify data traveling across a
client server environmentbut I may be wrong.
It's for internal use only.  If you want to verify data, you can institute
your own application-level checksums.


Any insight?



Thanks

Mike


--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified?  http://www.mysql.com/certification/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: CREATE TABLE problem ..

[Nils Valentin]

2003年 7月 7日 月曜日 18:02、Rafal Kedziorski さんは書きました:
> At 17:58 07.07.2003 +0900, Nils Valentin wrote:
> >2003年 7月 7日 月曜日 17:43、Rafal Kedziorski 
さんは書きました:
> > > hi,
> > >
> > > I'm porting an PostgreSQL script to MySQL. I get an error:
> > >
> > > Can't create table '.\mmcms\mandant.frm' (errno: 150)
> > >
> > > for this tables:
> > >
> > > CREATE TABLE `firm` (
> > >`firm_id` SMALLINT  NOT NULL AUTO_INCREMENT,
> > >`name`VARCHAR  (40) NOT NULL,
> > >#
> > >PRIMARY KEY (`firm_id`)
> > > ) TYPE=InnoDB;
> > >
> > >
> > > CREATE TABLE `mandant` (
> > >`mandant_id`SMALLINT   NOT NULL AUTO_INCREMENT,
> > >`firm_id`   SMALLINT   NOT NULL,
> > >`parent_id` SMALLINT,
> > >`name`  VARCHAR   (20) NOT NULL,
> > >`creation_date` TIMESTAMP  NOT NULL,
> > >#
> > >PRIMARY KEY (`mandant_id`),
> > >FOREIGN KEY (`firm_id`)
> > >  REFERENCES `firm` (`firm_id`),
> > >FOREIGN KEY (`parent_id`)
> > >  REFERENCES `mandant` (`mandant_id`)
> > > ) TYPE=InnoDB;
> >
> >The foreign key points to the table mandant. But the column mandant_id is
> > not indexed. (f.e no Primary key)
>
> dasn't PRIMARY KEY create an INDEX?

Yes thats correct, both attempts lead to the wanted result. I just received 
your second post (solution) actually after I send my first reply.

Best regards

Nils Valentin
Tokyo/Japan
>
>
> Rafal

-- 
---
Valentin Nils
Internet Technology

 E-Mail: [EMAIL PROTECTED]
 URL: http://www.knowd.co.jp
 Personal URL: http://www.knowd.co.jp/staff/nils


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: CREATE TABLE problem ..

[Rafal Kedziorski]

At 17:58 07.07.2003 +0900, Nils Valentin wrote:
2003年 7月 7日 月曜日 17:43、Rafal Kedziorski さんは書きました:
> hi,
>
> I'm porting an PostgreSQL script to MySQL. I get an error:
>
> Can't create table '.\mmcms\mandant.frm' (errno: 150)
>
> for this tables:
>
> CREATE TABLE `firm` (
>`firm_id` SMALLINT  NOT NULL AUTO_INCREMENT,
>`name`VARCHAR  (40) NOT NULL,
>#
>PRIMARY KEY (`firm_id`)
> ) TYPE=InnoDB;
>
>
> CREATE TABLE `mandant` (
>`mandant_id`SMALLINT   NOT NULL AUTO_INCREMENT,
>`firm_id`   SMALLINT   NOT NULL,
>`parent_id` SMALLINT,
>`name`  VARCHAR   (20) NOT NULL,
>`creation_date` TIMESTAMP  NOT NULL,
>#
>PRIMARY KEY (`mandant_id`),
>FOREIGN KEY (`firm_id`)
>  REFERENCES `firm` (`firm_id`),
>FOREIGN KEY (`parent_id`)
>  REFERENCES `mandant` (`mandant_id`)
> ) TYPE=InnoDB;
The foreign key points to the table mandant. But the column mandant_id is not
indexed. (f.e no Primary key)
dasn't PRIMARY KEY create an INDEX?

Rafal 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: CREATE TABLE problem ..

[Nils Valentin]

2003年 7月 7日 月曜日 17:43、Rafal Kedziorski さんは書きました:
> hi,
>
> I'm porting an PostgreSQL script to MySQL. I get an error:
>
> Can't create table '.\mmcms\mandant.frm' (errno: 150)
>
> for this tables:
>
> CREATE TABLE `firm` (
>`firm_id` SMALLINT  NOT NULL AUTO_INCREMENT,
>`name`VARCHAR  (40) NOT NULL,
>#
>PRIMARY KEY (`firm_id`)
> ) TYPE=InnoDB;
>
>
> CREATE TABLE `mandant` (
>`mandant_id`SMALLINT   NOT NULL AUTO_INCREMENT,
>`firm_id`   SMALLINT   NOT NULL,
>`parent_id` SMALLINT,
>`name`  VARCHAR   (20) NOT NULL,
>`creation_date` TIMESTAMP  NOT NULL,
>#
>PRIMARY KEY (`mandant_id`),
>FOREIGN KEY (`firm_id`)
>  REFERENCES `firm` (`firm_id`),
>FOREIGN KEY (`parent_id`)
>  REFERENCES `mandant` (`mandant_id`)
> ) TYPE=InnoDB;

The foreign key points to the table mandant. But the column mandant_id is not 
indexed. (f.e no Primary key)

Best regards

Nils Valentin
Tokyo/Japan



>
>
> Regards,
> Rafal

-- 
---
Valentin Nils
Internet Technology

 E-Mail: [EMAIL PROTECTED]
 URL: http://www.knowd.co.jp
 Personal URL: http://www.knowd.co.jp/staff/nils


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: CREATE TABLE problem ..

[Rafal Kedziorski]

hi,

I added two indexes in mandant table

CREATE TABLE `mandant` (
  `mandant_id`SMALLINT   NOT NULL AUTO_INCREMENT,
  `firm_id`   SMALLINT   NOT NULL,
  `parent_id` SMALLINT,
  `name`  VARCHAR   (20) NOT NULL,
  `creation_date` TIMESTAMP  NOT NULL,
  #
  PRIMARY KEY (`mandant_id`),
  INDEX (`firm_id`),
  INDEX (`parent_id`),
  FOREIGN KEY (`firm_id`)
REFERENCES `firm` (`firm_id`),
  FOREIGN KEY (`parent_id`)
REFERENCES `mandant` (`mandant_id`)
) TYPE=InnoDB;
and it works.

Regards,
Rafal
At 10:43 07.07.2003 +0200, Rafal Kedziorski wrote:
hi,

I'm porting an PostgreSQL script to MySQL. I get an error:

Can't create table '.\mmcms\mandant.frm' (errno: 150)

for this tables:

CREATE TABLE `firm` (
  `firm_id` SMALLINT  NOT NULL AUTO_INCREMENT,
  `name`VARCHAR  (40) NOT NULL,
  #
  PRIMARY KEY (`firm_id`)
) TYPE=InnoDB;
CREATE TABLE `mandant` (
  `mandant_id`SMALLINT   NOT NULL AUTO_INCREMENT,
  `firm_id`   SMALLINT   NOT NULL,
  `parent_id` SMALLINT,
  `name`  VARCHAR   (20) NOT NULL,
  `creation_date` TIMESTAMP  NOT NULL,
  #
  PRIMARY KEY (`mandant_id`),
  FOREIGN KEY (`firm_id`)
REFERENCES `firm` (`firm_id`),
  FOREIGN KEY (`parent_id`)
REFERENCES `mandant` (`mandant_id`)
) TYPE=InnoDB;
Regards,
Rafal
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: CREATE TABLE with date as part of table name

[Egor Egorov]

"Me" <[EMAIL PROTECTED]> wrote:
> 
> I would like to know how to create a table within MySQL at the command
> prompt where the table name will be something like:
> 
> 
> MyTableName_TodaysDate
> 
> 
> Is this possible while at the command prompt? I am trying to create a batch
> file that will create a table in MySQL based on todays date. The system I am
> working on at the moment is Win2k.

You can do it with MySQL only.



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   <___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

re: Create table with IF condition not working properly

[Victoria Reznichenko]

On Friday 28 February 2003 22:00, Scott wrote:

> I have a client word database and I am attempting to create a database with
> the results from a select that splits the description field based on the
> spaces.
>
> When just executing the select command, the output is correct.  When using
> the create command with the select command, apparently the IF statement
> result (0/1) is being placed into the field instead of the word.
>
> My Mysql version is: mysql  Ver 12.12 Distrib 4.0.3-beta, for pc-linux-gnu
> (i686)
>
> clientid  description
>   -
> client1   chair couch piano
> client2   bed dresser
> client3   chair
> client4   table stove couch
>
>
>
> drop table if exists wordsplit;
>
> create table wordsplit
>
> select clientid,
> if(locate(' ',description)=0,trim(substring(description,1)),
>   trim(substring(description,1,locate(' ',description as firstword,
> if(locate(' ',trim(substring(description,locate(' ',description=0,
>   substring(trim(substring(description,locate(' ',description))),1),
>   substring(trim(substring(description,locate(' ',description))),1,
>   locate(' ',trim(substring(description,locate(' ',description)) as
> secondword,
> (trim(substring(ltrim(substring(description,locate(' ',description))),
> locate(' ',ltrim(substring(description,locate(' ',description))) as
> thirdword
> from clientword order by clientid;

Could you test it on 4.0.11 version of MySQL server? If you get the same 
result, please, provide a repeatable test case with table structure (output 
of SHOW CREATE TABLE or mysqldump).


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   <___/   www.mysql.com





-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

re: Create table, wrong datatype

[Victoria Reznichenko]

On Wednesday 12 February 2003 10:21, Ulla Wensman wrote:

> When I run this script the char-fields is converted to
> varchar. Row-format is dynamic. I think it is because i have a text-field
> in the
> script. When I change the text field to somthing else I got the datatype
> char in the char-columns. I have tried to alter the colums to char but it
> doesn't work.
> How do I get around this problem?

It's a known behaviour and described here:
http://www.mysql.com/doc/en/Silent_column_changes.html


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   <___/   www.mysql.com





-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Create table, wrong datatype

[Stefan Hinz]

Ulla,

> When I run this script the char-fields is converted to
> varchar. Row-format is dynamic. I think it is because i have a text-field in
> the script.

This MySQL feature (silent column type change) is documented here:
http://www.mysql.com/doc/en/Silent_column_changes.html 

Regards,
--
  Stefan Hinz <[EMAIL PROTECTED]>
  iConnect GmbH 
  Heesestr. 6, 12169 Berlin (Germany)
  Telefon: +49 30 7970948-0  Fax: +49 30 7970948-3


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Create table, wrong datatype

[Doug Thompson]

Hello:

Because you have a variable length column type (text) in the row, mysql
changes char() types  to varchar.  This behavior is discussed in the
manual in Silent Column Change Specifications under CREATE TABLE.

If the stored length of these columns is an issue, you can move the
variable length fields to another table.  However, the varchar() type
reduces storage requirements without a performance penalty.  If the
retrieved/displayed length is the issue, you can use the LPAD or RPAD
functions as appropriate.

Doug

On Wed, 12 Feb 2003 09:21:31 +0100, Ulla Wensman wrote:

>Hello!
>
>When I run this script the char-fields is converted to
>varchar. Row-format is dynamic. I think it is because i have a text-field in
>the
>script. When I change the text field to somthing else I got the datatype
>char in the char-columns. I have tried to alter the colums to char but it
>doesn't work.
>How do I get around this problem?
>
>I use mysql-3.23.55-win and win2000.
>
>Regards Ulla
>
>#=
>#   Table: BasAtgard
>#=
>create table BasAtgard
>(
>AtgIdint   not null,
>ArbBeskrId   int   not null,
>Rubrik   char(40)  null,
>Beskrivning  text  null,
>VardeTillBakasmallint  not null,
>RegDatum datetime  null,
>RegAvchar(20)  null,
>AndradDatum  datetime  null,
>AndradAv char(20)  null,
>Raknare  int   null,
>
>PRIMARY KEY (AtgId)
>)
>TYPE=InnoDB;



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: CREATE TABLE, NOT NULL fields, and empty strings (auto)

[Andy Smith]

On Fri, Feb 07, 2003 at 04:33:15PM -0700, [EMAIL PROTECTED] wrote:
> Hi "Doug Beyer" <[EMAIL PROTECTED]>,
> 
> I'm not sure if you have checked the manual yet, but following
> links seem to be somewhat related to your query:

Please could these stop.  Already I have noticed various people
reposting old mails back to the list, someone rejecting mails that
don't have mysql/query in them which leads some people to repost
even though the list has already seen it, and a couple of other
scripts gone mad.  All of this seems to vreate double or more
traffic to the list which is pure waste.

If this script must be run on emails coming in, why not send the
results only to the poster?

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: CREATE TABLE, NOT NULL fields, and empty strings

[Stefan Hinz]

Doug,

> create table t1 ( id varchar(5) not null, name varchar(5) not null );
> insert into t1 ( id ) values ( "1234" );
> select count(*) from t1 where name = "";--> Result = 1
> 1) Why did the insert succeed since the "name" field is not null and I didn't 
>provide a value?
> 2) Why does MySql think it's correct to substitute an empty string for a 
>non-provided value?

It's in the documentation, although I found no better explanation than
this one:

NULLs must be indicated as "NULL" values, not left blank. (User
comment)

MySQL always has a default value for all fields. This is something
that is imposed on MySQL to be able to work with both transactional
and not transactional tables. (Manual text)

http://www.mysql.com/doc/en/INSERT.html

Regards,
--
  Stefan Hinz <[EMAIL PROTECTED]>
  iConnect GmbH 
  Heesestr. 6, 12169 Berlin (Germany)
  Telefon: +49 30 7970948-0  Fax: +49 30 7970948-3


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: CREATE TABLE, NOT NULL fields, and empty strings

[Doug Thompson]

On Thu, 6 Feb 2003 12:05:03 -0500, Doug Beyer wrote:
>
>I create the following table:
>
>create table t1 ( id varchar(5) not null, name varchar(5) not null );
>
>I insert the following row:
>
>insert into t1 ( id ) values ( "1234" );
>
>I do the following selects:
>
>select count(*) from t1 where name is null;--> Result = 0
>
>select count(*) from t1 where name = "";   --> Result = 1
>
>
>Questions:
>1) Why did the insert succeed since the "name" field is not null and I didn't provide 
>a value?
>2) Why does MySql think it's correct to substitute an empty string for a non-provided 
>value?

1) MySQL inserts the default value(s) to complete the row.

2) Because you specified the column to be NOT NULL.

hth,
Doug


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: CREATE TABLE, NOT NULL fields, and empty strings

[R. Hannes Niedner]

On 2/6/03 9:05 AM, "Doug Beyer" <[EMAIL PROTECTED]> wrote:

> I attempted to search various places for my answer but with no luck. So I'm
> posting here.
> 
> 
> 
> I create the following table:
> 
> create table t1 ( id varchar(5) not null, name varchar(5) not null );
> 
> 
> I insert the following row:
> 
> insert into t1 ( id ) values ( "1234" );
> 
> 
> I do the following selects:
> 
> select count(*) from t1 where name is null; --> Result = 0
> 
> select count(*) from t1 where name = "";--> Result = 1
> 
> 
> Questions:
> 1) Why did the insert succeed since the "name" field is not null and I didn't
> provide a value?
> 2) Why does MySql think it's correct to substitute an empty string for a
> non-provided value?
>

This was discussed recently on the list, but there is also an answer in the
manual, but as it so often happens to my self I miss to look at the right
place - thus here it his:

You defined the column as NOT NULL and VARCHAR, then you inserted a NULL
into that field, thus mysql DEFAULTs to an empty string.
All together you get exactly what you asked for, if you don't like this
behavior you need to script around it in your application layer.

Best/h



-cited from above URL -
# A DEFAULT value has to be a constant, it can not be a function or an
expression. If no DEFAULT value is specified for a column, MySQL
automatically assigns one. If the column may take NULL as a value, the
default value is NULL. If the column is declared as NOT NULL, the default
value depends on the column type:

* For numeric types other than those declared with the AUTO_INCREMENT
attribute, the default is 0. For an AUTO_INCREMENT column, the default value
is the next value in the sequence.
* For date and time types other than TIMESTAMP, the default is the
appropriate zero value for the type. For the first TIMESTAMP column in a
table, the default value is the current date and time. See section 6.2.2
Date and Time Types.
* For string types other than ENUM, the default value is the empty string.
For ENUM, the default is the first enumeration value (if you haven't
explicitly specified another default value with the DEFAULT directive).
end of cite




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: CREATE TABLE, NOT NULL fields, and empty strings

[Philipp Specht]

Doug Beyer wrote:

create table t1 ( id varchar(5) not null, name varchar(5) not null );
insert into t1 ( id ) values ( "1234" );
select count(*) from t1 where name is null; 	--> Result = 0
select count(*) from t1 where name = "";		--> Result = 1



Questions:
1) Why did the insert succeed since the "name" field is not null and I didn't provide a value?


MySQL uses the default value of this column if you don't provide a value 
and the column is defined "not null".

2) Why does MySql think it's correct to substitute an empty string for a non-provided value?


http://www.mysql.com/doc/en/CREATE_TABLE.html :

If no DEFAULT value is specified for a column, MySQL automatically 
assigns one. If the column may take NULL as a value, the default value 
is NULL. If the column is declared as NOT NULL, the default value 
depends on the column type:
* For string types other than ENUM, the default value is the empty 
string. For ENUM, the default is the first enumeration value (if you 
haven't explicitly specified another default value with the DEFAULT 
directive).

Good night,
Philipp


-
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: CREATE TABLE, NOT NULL fields, and empty strings (auto)

[mysql-list]

Hi "Doug Beyer" <[EMAIL PROTECTED]>,

I'm not sure if you have checked the manual yet, but following
links seem to be somewhat related to your query:

* http://www.mysql.com/doc/en/Problems_with_NULL.html
* http://www.mysql.com/doc/en/CREATE_TABLE.html
* http://www.mysql.com/doc/en/SHOW.html
* http://www.mysql.com/doc/en/SHOW_CREATE_TABLE.html
* http://www.mysql.com/doc/en/ODBC_administrator.html

This was an automated response to your email 'CREATE TABLE, NOT NULL fields, and empty 
strings'.
Final search keyword used to query the manual was 'CREATE TABLE NOT NULL fields  empty 
strings'.

Comming soon...
  
  * Support for mysql archives

Feedbacks, suggestions and complaints should be directed to 
[EMAIL PROTECTED]


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: CREATE TABLE issue?

[Adolfo Bello]

Missing TYPE=INNODB in create table statement.

MySQL parses references for type MyISAM but doesn't use them.

Adolfo

> -Original Message-
> From: Jeff Snoxell [mailto:[EMAIL PROTECTED]] 
> Sent: Friday, December 20, 2002 1:14 PM
> To: [EMAIL PROTECTED]
> Subject: CREATE TABLE issue?
> 
> 
> Hi again :)
> 
> I found the following mysql stuff in the MySQL manual in a section 
> explaining how to do relationships.
> 
> CREATE TABLE persons (
>  id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
>  name CHAR(60) NOT NULL,
>  PRIMARY KEY (id)
> );
> 
> CREATE TABLE shirts (
>  id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
>  style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
>  color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
>  owner SMALLINT UNSIGNED NOT NULL REFERENCES persons,
>  PRIMARY KEY (id)
> );
> 
> What's the point of the "REFERENCES persons" bit at the end of the 
> definition of table "Shirts"? It doesn't seem to do anything.
> 
> Jeff
> 
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail 
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 
> 
> 
> 


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: CREATE TABLE issue?

[Paul DuBois]

At 17:13 + 12/20/02, Jeff Snoxell wrote:

Hi again :)

I found the following mysql stuff in the MySQL manual in a section 
explaining how to do relationships.

CREATE TABLE persons (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
name CHAR(60) NOT NULL,
PRIMARY KEY (id)
);

CREATE TABLE shirts (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
owner SMALLINT UNSIGNED NOT NULL REFERENCES persons,
PRIMARY KEY (id)
);

What's the point of the "REFERENCES persons" bit at the end of the 
definition of table "Shirts"? It doesn't seem to do anything.

Right, you can leave it out.


-
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

re: create table ... select ... w/ index

[Egor Egorov]

On Thursday 12 December 2002 05:52, Amittai Aviram wrote:

> When you copy a table using CREATE TABLE ... SELECT ..., the indexes do not
> get copied automatically.  Is there a convenient way to transfer all
> indexes, other than looking them up with DESCRIBE or SHOW INDEX and then
> using either ALTER TABLE ADD INDEX, etc., or CREATE INDEX?

You can get output of SHOW CREATE TABLE, create table with indexes, then 
INSERT .. SELECT
or do as you've described :)



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   <___/   www.mysql.com




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: CREATE TABLE and CHECK clausole

[Victor Pendleton]

You are correct. The 'CHECK` clause does not do anything. The syntax is
provided only for compatibility and to aid in porting code from other DBMSs.

-Original Message-
From: Jon Frisby [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, December 03, 2002 12:43 PM
To: 'MySQL Mailinglist'
Subject: RE: CREATE TABLE and CHECK clausole


I've attempted to get the CHECK clause to work as well, and have had no
luck.  I don't think it's actually supported by MySQL yet.

-JF

> -Original Message-
> From: R. Hannes Niedner [mailto:[EMAIL PROTECTED]] 
> Sent: Tuesday, December 03, 2002 7:42 AM
> To: Achille M. Luongo; MySQL Mailinglist
> Subject: Re: CREATE TABLE and CHECK clausole
> 
> 
> On 12/3/02 4:32 AM, "Achille M. Luongo" 
> <[EMAIL PROTECTED]> wrote:
> 
> > Dear Sir,
> > 
> > I have tried with FLOAT or DECIMAL data types, but MySQL 
> always returns the
> > same error. After your suggestion, I double checked the 
> MySQL data types
> > definition list and the INTEGER (alias INT) data type is supported.
> > 
> > Thanks in advance for any other suggestion.
> > 
> > Achille.
> > 
> > "R. Hannes Niedner" wrote:
> >> 
> >> Maybe it's just to late for me to think clearly but there 
> are NO INTEGERS
> >> between 0 and 1, try FLOAT or DECIMAL.
> >> 
> >> Best/h
> 
> You are certainly correct, I tried all kinds of expressions 
> and datatypes
> and couldn't get it to work, but my point was rather that there are no
> INTEGERs between 0 and 1. In any case the manual entry is 
> very slim for the
> CHECK clause in the CREATE TABLE statement, and somebody 
> already asked for
> and example. Where are the experts on this list?
> 
> /h
> 
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail 
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 
> 


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: CREATE TABLE and CHECK clausole

[Jon Frisby]

I've attempted to get the CHECK clause to work as well, and have had no
luck.  I don't think it's actually supported by MySQL yet.

-JF

> -Original Message-
> From: R. Hannes Niedner [mailto:[EMAIL PROTECTED]] 
> Sent: Tuesday, December 03, 2002 7:42 AM
> To: Achille M. Luongo; MySQL Mailinglist
> Subject: Re: CREATE TABLE and CHECK clausole
> 
> 
> On 12/3/02 4:32 AM, "Achille M. Luongo" 
> <[EMAIL PROTECTED]> wrote:
> 
> > Dear Sir,
> > 
> > I have tried with FLOAT or DECIMAL data types, but MySQL 
> always returns the
> > same error. After your suggestion, I double checked the 
> MySQL data types
> > definition list and the INTEGER (alias INT) data type is supported.
> > 
> > Thanks in advance for any other suggestion.
> > 
> > Achille.
> > 
> > "R. Hannes Niedner" wrote:
> >> 
> >> Maybe it's just to late for me to think clearly but there 
> are NO INTEGERS
> >> between 0 and 1, try FLOAT or DECIMAL.
> >> 
> >> Best/h
> 
> You are certainly correct, I tried all kinds of expressions 
> and datatypes
> and couldn't get it to work, but my point was rather that there are no
> INTEGERs between 0 and 1. In any case the manual entry is 
> very slim for the
> CHECK clause in the CREATE TABLE statement, and somebody 
> already asked for
> and example. Where are the experts on this list?
> 
> /h
> 
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail 
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 
> 


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: CREATE TABLE and CHECK clausole

[R. Hannes Niedner]

On 12/3/02 4:32 AM, "Achille M. Luongo" <[EMAIL PROTECTED]> wrote:

> Dear Sir,
> 
> I have tried with FLOAT or DECIMAL data types, but MySQL always returns the
> same error. After your suggestion, I double checked the MySQL data types
> definition list and the INTEGER (alias INT) data type is supported.
> 
> Thanks in advance for any other suggestion.
> 
> Achille.
> 
> "R. Hannes Niedner" wrote:
>> 
>> Maybe it's just to late for me to think clearly but there are NO INTEGERS
>> between 0 and 1, try FLOAT or DECIMAL.
>> 
>> Best/h

You are certainly correct, I tried all kinds of expressions and datatypes
and couldn't get it to work, but my point was rather that there are no
INTEGERs between 0 and 1. In any case the manual entry is very slim for the
CHECK clause in the CREATE TABLE statement, and somebody already asked for
and example. Where are the experts on this list?

/h


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

re: CREATE TABLE and CHECK clausole

[Egor Egorov]

Achille,
Tuesday, December 03, 2002, 11:08:19 AM, you wrote:

AML> MySQL seems to support the CHECK() clausole in the implementation of CREATE
AML> TABLE statement, but MySQL (Ver 11.15 Distrib 3.23.39, for pc-linux-gnu)
AML> returns a syntax error on such a command. For example:

AML> mysql> CREATE TABLE example (var INTEGER, CHECK(var BETWEEN 0 AND 1));
AML> ERROR 1064: You have an error in your SQL syntax near '(var BETWEEN 0 AND
AML> 1))' at line 1

AML> If I remove the CHECK clausole, the sql statement works:

AML> mysql> CREATE TABLE example (var INTEGER);
AML> Query OK, 0 rows affected (0.01 sec)

AML> Can anyone suggest me how to correctly use the CHECK clausole in MySQL
AML> (even if for compatibility issues with other sql databases) ?

Here is:

mysql> CREATE TABLE example (var INTEGER, constraint var CHECK( var BETWEEN 0 AND 1));
Query OK, 0 rows affected (0.01 sec)

But CHECK clause does nothing in MySQL.




-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   <___/   www.mysql.com




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: CREATE TABLE and CHECK clausole

[R. Hannes Niedner]

Maybe it's just to late for me to think clearly but there are NO INTEGERS
between 0 and 1, try FLOAT or DECIMAL.

Best/h


On 12/3/02 1:08 AM, "Achille M. Luongo" <[EMAIL PROTECTED]> wrote:

> Dear subscribers,
> 
> MySQL seems to support the CHECK() clausole in the implementation of CREATE
> TABLE statement, but MySQL (Ver 11.15 Distrib 3.23.39, for pc-linux-gnu)
> returns a syntax error on such a command. For example:
> 
> mysql> CREATE TABLE example (var INTEGER, CHECK(var BETWEEN 0 AND 1));
> ERROR 1064: You have an error in your SQL syntax near '(var BETWEEN 0 AND
> 1))' at line 1
> 
> If I remove the CHECK clausole, the sql statement works:
> 
> mysql> CREATE TABLE example (var INTEGER);
> Query OK, 0 rows affected (0.01 sec)
> 
> Can anyone suggest me how to correctly use the CHECK clausole in MySQL
> (even if for compatibility issues with other sql databases) ?
> 
> Thanks in advance for your suggestions.
> 
> Achille


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php