r 26, 2005 9:25 AM
Subject: weird innodb foreign key feature
I'm using MySQL Ver 14.7 Distrib 4.1.9, for pc-linux-gnu (i686) on
RedHat Linux 9
and found this weird thing
I create first table
CREATE TABLE `t_keycode` (
`keycode_id` int(11) NOT NULL auto_increment,
`keycode_val
- Original Message -
From: "Ady Wicaksono" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.myodbc
Sent: Wednesday, October 26, 2005 9:25 AM
Subject: weird innodb foreign key feature
I'm using MySQL Ver 14.7 Distrib 4.1.9, for pc-linux-gnu (i686) on
RedHat Linux 9
and f
I'm using MySQL Ver 14.7 Distrib 4.1.9, for pc-linux-gnu (i686) on
RedHat Linux 9
and found this weird thing
I create first table
CREATE TABLE `t_keycode` (
`keycode_id` int(11) NOT NULL auto_increment,
`keycode_val` varchar(255) NOT NULL default '',
`keycode_desc` varchar(255) NOT NULL defa
"Gabriel PREDA" <[EMAIL PROTECTED]> wrote on 03/16/2005 06:12:14 AM:
> > It looks from googling as though I need to drop all foreign key
> constraints on this column, perform the change and then reestablish the
> foreign keys. Could anyone confirm or advise of a better solution?
>
> That is the w
> It looks from googling as though I need to drop all foreign key
constraints on this column, perform the change and then reestablish the
foreign keys. Could anyone confirm or advise of a better solution?
That is the way ! :)
You need to drop the constrains...
Alter `reference` and make `id` INT
Hi,
I have a column 'id' within a table :
CREATE TABLE `reference` (
*`*id*`* smallint(5) unsigned NOT NULL auto_increment,
`study_name` text,
`author` text NOT NULL,
`date` date NOT NULL default '-00-00',
`reference` varchar(250) NOT NULL default '',
`title` varchar(250) NOT NULL default
Hello,
Are there any plans to implement foreign keys with deferred integrity
constraint checking in the InnoDB storage engine in a future release of
the MySQL Server?
Many thanks,
- glenn
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://list
"Tucker, Gabriel" <[EMAIL PROTECTED]> wrote:
> Victoria=20
>
> That seemed to work well, thank you.
>
> However, I received another error that I am not sure how to troubleshoot =
> during the restore:
>ERROR 1114 at line 83 in file: =
> '/bb/bin/mysql/backups/archive_4320.sql': The table
Gabriel,
First of all, sorry for my poor english (I´m just a brazilian guy - eu
quase não sei falar o portugues, imaginem o ingles então.)
Tucker, Gabriel wrote:
Luciano
I am confused... As far as I can tell, the set foreign_key_checks=0; is used with the
load data infile command. I am not us
: InnoDB - Foreign Key - Error 150.
Victoria
That seemed to work well, thank you.
However, I received another error that I am not sure how to troubleshoot during the
restore:
ERROR 1114 at line 83 in file: '/bb/bin/mysql/backups/archive_4320.sql': The
table 'cur_reject_
n I do here?
Thanks - Gabe
-Original Message-
From: Victoria Reznichenko [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 04, 2004 10:35 AM
To: [EMAIL PROTECTED]
Subject: Re: InnoDB - Foreign Key - Error 150.
"Tucker, Gabriel" <[EMAIL PROTECTED]> wrote:
> Marvin
>
> I
On Tue, 4 May 2004 11:01:59 -0400
"Tucker, Gabriel" <[EMAIL PROTECTED]> wrote:
> Luciano
>
> I am confused... As far as I can tell, the set
> foreign_key_checks=0; is used with the load data infile command.
Actually, I think that it is used for all operations on the DBMS, not just load data
: [EMAIL PROTECTED]
Subject: Re: InnoDB - Foreign Key - Error 150.
"Tucker, Gabriel" <[EMAIL PROTECTED]> wrote:
> Marvin
>
> I believe that is the problem with the restore. When I create the =
> archive file using the mysqldump command and options previously listed, =
L PROTECTED]
Sent: Tuesday, May 04, 2004 10:03 AM
To: Tucker, Gabriel
Subject: Re: InnoDB - Foreign Key - Error 150.
Put in your script:
set foreign_key_checks=0;
in the first line.
mysqldump dumps tables in alphabetical order.
Tucker, Gabriel wrote:
> Hello All:
>
> I am having a p
"Tucker, Gabriel" <[EMAIL PROTECTED]> wrote:
> Marvin
>
> I believe that is the problem with the restore. When I create the =
> archive file using the mysqldump command and options previously listed, =
> I get the create table in the order listed below and thus, the foreign =
> key constraint is
e end!
Best of luck,
Ken
- Original Message -
From: "Marvin Wright" <[EMAIL PROTECTED]>
To: "Tucker, Gabriel" <[EMAIL PROTECTED]>; "Mysql General
(E-mail)" <[EMAIL PROTECTED]>
Sent: Tuesday, May 04, 2004 10:22 AM
Subject: RE: InnoDB - Foreign Key -
On Tue, 4 May 2004 09:46:27 -0400
"Tucker, Gabriel" <[EMAIL PROTECTED]> wrote:
> Hello All:
>
> I am having a problem with both V4.0.16 and 4.0.18. Let me explain:
>
> I have a database with two InnoDB tables in v4.0.16. I backup up
> this database every night using the following command:
>
>
---
From: Tucker, Gabriel [mailto:[EMAIL PROTECTED]
Sent: 04 May 2004 15:13
To: Mysql General (E-mail)
Subject: RE: InnoDB - Foreign Key - Error 150.
Marvin
I believe that is the problem with the restore. When I create the archive
file using the mysqldump command and options previously listed, I
l database on its own port and then run from the
prompt "mysql --port= --socket= -p < archive_file.sql"
Thanks again
Gabe
-Original Message-
From: Marvin Wright [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 04, 2004 9:59 AM
To: Tucker, Gabriel; Mysql General (E-mail)
Subject: R
)
Subject: InnoDB - Foreign Key - Error 150.
Hello All:
I am having a problem with both V4.0.16 and 4.0.18. Let me explain:
I have a database with two InnoDB tables in v4.0.16. I backup up this
database every night using the following command:
mysqldump --allow-keywords --host=$HOST --flush
Hello All:
I am having a problem with both V4.0.16 and 4.0.18. Let me explain:
I have a database with two InnoDB tables in v4.0.16. I backup up this database every
night using the following command:
mysqldump --allow-keywords --host=$HOST --flush-logs --disable-keys --opt
--port=$1 -
AM
To: [EMAIL PROTECTED]
Subject: innodb foreign key
Hi,
I have two innodb tables produced as show below
CREATE TABLE `monogenic` (
`id` smallint(5) unsigned NOT NULL default '0',
`exp_design` varchar(50) default NULL,
`disease` varchar(50) default NULL,
`omim` varchar(20) de
One thing that stands out is the data types are different sizes.
http://www.mysql.com/doc/en/InnoDB_foreign_key_constraints.html
-Original Message-
From: R.Dobson [mailto:[EMAIL PROTECTED]
Sent: Wednesday, September 24, 2003 8:42 AM
To: [EMAIL PROTECTED]
Subject: innodb foreign key
Hi
Hi,
I have two innodb tables produced as show below
CREATE TABLE `monogenic` (
`id` smallint(5) unsigned NOT NULL default '0',
`exp_design` varchar(50) default NULL,
`disease` varchar(50) default NULL,
`omim` varchar(20) default NULL,
`phenotype_ID` smallint(5) unsigned NOT NULL default '0',
day, September 18, 2003 11:50 PM
Subject: Re: InnoDB Foreign Key Constraint Issue
> David,
>
> - Original Message -
> From: "David Griffiths" <[EMAIL PROTECTED]>
> Newsgroups: mailing.database.myodbc
> Sent: Friday, September 19, 2003 4:07 AM
> Subject: In
David,
- Original Message -
From: "David Griffiths" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.myodbc
Sent: Friday, September 19, 2003 4:07 AM
Subject: InnoDB Foreign Key Constraint Issue
> The following constraint is failing:
>
> ALTER TABLE address_li
The following constraint is failing:
ALTER TABLE address_list ADD CONSTRAINT FOREIGN KEY (address_type_id)
REFERENCES address_type (address_type_id);
The address_list table looks like:
mysql> desc address_list;
+--+--+--+-+-+---+
| Field
On Wednesday 19 March 2003 15:19, Thorsten Schmidt wrote:
> how can I remove a foreign key in InnoDB?
> ALTER TABLE DROP (FOREIGN) KEY `key`
> isn't working (and also not specified in documentation)... : (
Currently you should recreate table to remove FOREIGN KEY CONSTRAINTS.
--
For technical
>
> > Dear all,
> > how can I remove a foreign key in InnoDB?
> > ALTER TABLE DROP (FOREIGN) KEY `key`
> > isn't working (and also not specified in documentation)... : (
> >
> >
> It's not implemented yet. You can copy your data to another table without
> the foreign key constraint, drop origin
Dear all,
how can I remove a foreign key in InnoDB?
ALTER TABLE DROP (FOREIGN) KEY `key`
isn't working (and also not specified in documentation)... : (
Thanks and best regards
Thorsten
(For bypassing the Filter: Here is sql,query,queries,smallint)
-
nnobase Oy
sql query
Subject: Innodb Foreign Key Problems.
From: Scott Wong
Date: Wed, 5 Feb 2003 10:03:17 -0800
Hi. Simple parent/child table generates some weird output based on the order
possible bug?
Mysql 3.23.54
CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY
Hi. Simple parent/child table generates some weird output based on the order
possible bug?
Mysql 3.23.54
CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB;
CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id),
FOREIGN KEY (parent_id) REFERENCES parent
Muhammed,
- Original Message -
From: "Muhammed Syyid" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.mysql
Sent: Thursday, January 16, 2003 3:06 PM
Subject: Re: InnoDB Foreign Key Questions
> Second the same question :). What does the CONSTRAINT keyword
> reg
Hello
I have two tables of InnoDB type.
CREATE TABLE `ledger` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(50) default NULL,
PRIMARY KEY (`id`),
KEY `id` (`name`)
) TYPE=InnoDB;
CREATE TABLE `voucher` (
`id` int(11) NOT NULL default '0',
`vdate` date NOT NULL default '
hi to all,
is it still true that mysql/innodb needs explicit
index creation on foreign keys?
why can't i use a standard syntax for foreign keys
creations?
i have a database schema (ddl) with over 50 tables and
i was trying to create the database on mysql when i
receive a lot of errors like this:
pickup_points set PP_Code = 'frobboz';
ERROR 1217: Cannot delete a parent row: a foreign key constraint fails
mysql>
mysql> update pickup_points set PP_RecordId = 10;
ERROR 1217: Cannot delete a parent row: a foreign key constraint fails
mysql>
...
From: M
From: Heikki Tuuri
To: Serdioukov Edouard
Cc: [EMAIL PROTECTED]
Sent: Tuesday, October 29, 2002 7:23 PM
Subject: Re: InnoDB, Foreign Key
Edouard,
I tested this on Linux using mysql-max-3.23.51, the .tar.gz distro. It
worked ok.
Are you sure you were using 3.23.51? Starting from 3.23.50, InnoDB
Edouard, you have a foreign key referencing a column in its own table.
PB
-
- Original Message -
From: "Serdioukov Edouard" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, October 29, 2002 3:23 AM
Subject: InnoDB, Foreign Key
Hello
I use Linux Red
Hello
I use Linux Red Hat 7.3 and MySQL-max version 2.23.51. I have one problem.
I created table:
CREATE TABLE Folders (
FOLDER_ID INTEGER NOT NULL,
FOLDER_NAME VARCHAR(50) NOT NULL,
PARENT INTEGER NULL,
PRIMARY KEY (FOLDER_ID),
KEY XIE1_Folders (PARENT),
FOREIGN KEY (PAR
Hi all,
We are using MySQL v. 3.23.49 on both Windows 2K and Solaris 8 with InnoDB
support. We have created many relational tables since InnoDB supports
foreign keys.
After some unidentified time, the foreign keys relating only one table
corrupts but the other foreign keys remain valid. At the c
t; <[EMAIL PROTECTED]>
Newsgroups: mailing.database.mysql
Sent: Monday, June 10, 2002 10:57 AM
Subject: InnoDB foreign key constraints
>
> Hello list!
>
> I'm having a bit of trouble getting foreign key constraints to work.
> I'm running MySQL 2.23.50-Max.
>
>
on't
agree to these terms, you should return this email in no more than 24 hours
stating the reason of disagreement.
-Oorspronkelijk bericht-----
Van: Markus Lervik [mailto:[EMAIL PROTECTED]]
Verzonden: maandag 10 juni 2002 10:45
Aan: Kiss Dániel
CC: [EMAIL PROTECTED]
Onderwerp: Re: Inno
On Monday 10 Jun 2002 11:44 am, Markus Lervik wrote:
> mysql> show create table ip_name_tbl\G
> *** 1. row ***
>Table: ip_name_tbl
> Create Table: CREATE TABLE `ip_name_tbl` (
[snip]
> `name_id` int(11) NOT NULL default '0',
[snip]
> mysq
On Monday 10 Jun 2002 11:17 am, you wrote:
> First of all the referenced key must be on PRIMARY KEY.
...which means my 'id' -field can't be a primary key, right?
> But I've seen in your table definition a quite strange thing. You have a
> UNIQUE and an ORDINARY key definition on the same field.
Heya!
You need an INDEX.
Try doing this first :
alter table ip_name_tbl add INDEX(name_id);
And add then your constraint.
EG
mysql> SHOW CREATE TABLE ip_name_tbl\G
*** 1. row ***
Table: ip_name_tbl
Create Table: CREATE TABLE `ip_name_tbl
First of all the referenced key must be on PRIMARY KEY.
But I've seen in your table definition a quite strange thing. You have a
UNIQUE and an ORDINARY key definition on the same field.
Here:
...
> UNIQUE KEY `name_id` (`name_id`), <- THIS IS THE FIRST DEFINITION
> UNIQUE KEY `comp_name` (`
Hello list!
I'm having a bit of trouble getting foreign key constraints to work.
I'm running MySQL 2.23.50-Max.
Here's what I got:
mysql> SHOW CREATE TABLE conn\G
*** 1. row ***
Table: conn
Create Table: CREATE TABLE `conn` (
`id` int(10
Daniel,
- Original Message -
From: "Daniel Rand" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.mysql
Sent: Monday, May 13, 2002 5:24 PM
Subject: InnoDB Foreign Key Constraints
> Hi,
>
> Does anyone know if it's possible to set up a foreign key constrain
48 matches
Mail list logo
