Creating primary and foreign keys for MySQL
Hi All I am MySQL newby with an Oracle background. Is the following syntax suported? alter table members add primary key (id); alter table members add constraint members_uk unique (name); Thanx Herman __ Do you Yahoo!? Yahoo! Mail Address AutoComplete - You start. We finish. http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Creating primary and foreign keys for MySQL
You haven't found the online manual yet, have you? http://dev.mysql.com/doc/mysql/en/index.html Specifically the documentation for the command you are curious about is found here: http://dev.mysql.com/doc/mysql/en/ALTER_TABLE.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine Herman Scheepers [EMAIL PROTECTED] wrote on 10/13/2004 02:54:17 PM: Hi All I am MySQL newby with an Oracle background. Is the following syntax suported? alter table members add primary key (id); alter table members add constraint members_uk unique (name); Thanx Herman __ Do you Yahoo!? Yahoo! Mail Address AutoComplete - You start. We finish. http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Foreign keys in MySQL 5.0
Hi all! Looking at one of the things that is set to be added to MySQL 5.0 (foreign keys for all table types), I have the following question: Will we be able to have foreign keys that involve tables of different types? Any early indications about the performance implications of having such things in one's schema? Regards, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Primary and Foreign Keys in MySQL
Hello, I am working on MySQL database. I have come across a 'problem' but don't know how to get around that. The problem is the following: I have a table, say TABLE1, with primary key being called as table1_id. Now there are other tables, like TABLE1A, TABLE1B, TABLE1C etc.. where I want to use primary key of TABLE1 (i.e table1_id) as foreign key. I see a problem in using the function LAST_INSERT_ID(). Lets say I use it to insert a record in TABLE1A, that will work fine. But now if I use it to put a record in TABLE1B it would give me the primary key ID of TABLE1A rather than TABLE1. Please help me out on this. Aamer - 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: Primary and Foreign Keys in MySQL
I'm assuming you have AUTO_INCREMENT columns in each table? Try this: INSERT INTO TABLE1(...) VALUES(...); SELECT @t1id := LAST_INSERT_ID(); INSERT INTO TABLE1A(table1_id, ...) VALUES(@t1id, ); INSERT INTO TABLE1B(table1_id, ...) VALUES(@t1id, ); ... -JF -Original Message- From: Aamer Rauf [mailto:[EMAIL PROTECTED]] Sent: Wednesday, July 24, 2002 12:55 PM To: [EMAIL PROTECTED] Subject: Primary and Foreign Keys in MySQL Hello, I am working on MySQL database. I have come across a 'problem' but don't know how to get around that. The problem is the following: I have a table, say TABLE1, with primary key being called as table1_id. Now there are other tables, like TABLE1A, TABLE1B, TABLE1C etc.. where I want to use primary key of TABLE1 (i.e table1_id) as foreign key. I see a problem in using the function LAST_INSERT_ID(). Lets say I use it to insert a record in TABLE1A, that will work fine. But now if I use it to put a record in TABLE1B it would give me the primary key ID of TABLE1A rather than TABLE1. Please help me out on this. Aamer - 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
Foreign keys in MySQL
Hi, In the MySQL documentation, it is stated that foreign keys in SQL are not used to join tables, but are used mostly for checking referential integrity (foreign key constraints). Does it mean that constraints are not supported? I need to have Update and perhaps Delete Referential Integrity. Yours Truly, Hanxue - 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: Foreign keys in MySQL
* Hanxue Lee In the MySQL documentation, it is stated that foreign keys in SQL are not used to join tables, but are used mostly for checking referential integrity (foreign key constraints). This could have been formulated in a better way, IMHO. I would even say the statement is wrong. In RDBMS terminology, a 'foreign key' is one (or more) column(s) in a table identifying records in a different table. Consider this schema: CREATE TABLE owner ( id int primary key, name varchar(30)); CREATE TABLE pet ( id int primary key, owner int, name varchar(30)); The 'owner' field of the 'pet' table is a foreign key if it used to store the primary keys from the 'owner' table. It _is_ a foreign key, even if no foreign key _constraints_ have been defined. The FOREIGN KEY keywords in MySQL is used to define such _constraints_, not to define the foreign key itself. Maybe the phrase in the manual should have been something like: ...the FOREIGN KEY keywords are not used to join tables, but... Does it mean that constraints are not supported? Why did you think that? The statement you quoted says ...not used to join tables... used mostly for... foreign key constraints. Anyway, the answer is 'yes and no', foreign key _constraints_ are supported, but only if you use InnoDB tables. Using the FOREIGN KEY keywords on other table types will not give errors, but it will be ignored. I need to have Update and perhaps Delete Referential Integrity. URL: http://www.mysql.com/doc/S/E/SEC446.html URL: http://www.mysql.com/doc/e/x/example-Foreign_keys.html -- Roger - 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
foreign keys in MySQL-Max-4.0.1
Hi, I use MySQL-Max-4.0.1 and i am wantering if is true that the information about the foreign keys are stored at comment properties of table with maximum length 60 characters? I need to use innodb tables with many f.keys.What I have to do? Thank you. - 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
foreign keys in MySQL-Max-4.0.1
Hi, I use MySQL-Max-4.0.1 and i am wantering if the information about the foreign keys are stored at comment properties of table with maximum length 60 characters? I need to use innodb tables with many f.keys.What I have to do? Thank you. - 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
FW: foreign keys in MySQL-Max-4.0.1
-Original Message- From: victoria papazoi [mailto:[EMAIL PROTECTED]] Sent: Friday, June 07, 2002 11:58 AM To: [EMAIL PROTECTED] Subject: foreign keys in MySQL-Max-4.0.1 Hi, I use MySQL-Max-4.0.1 and i am wantering if the information about the foreign keys are stored at comment properties of table with maximum length 60 characters? I need to use innodb tables with many f.keys.What I have to do? Thank you. - 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
Help - foreign keys in MySQL-Max-4.0.1
Hi, I use MySQL-Max-4.0.1 and i am wantering if the information about the foreign keys are stored at comment properties of table with maximum length 60 characters? I need to use innodb tables with many f.keys.What I have to do? Thank you. - 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
Foreign Keys with MySQL 3.23.49 and 3.23.50
sql,query Hello, i've tried the foreign key-example out of the documentation(3.5.6 Using Foreign Keys) on W2K (3.23.49 + 3.23.50) and LINUX (3.23.49). The tables persons and shirts are created as innodb-tables. I could insert a shirt for a non-existing person. Also i could delete a person with existing shirts. I have experimented with 'ON DELETE' and 'ON UPDATE' with no effects. What's wrong? Is there a switch to activate foreign keys? Heiner Jostkleigrewe - 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: Foreign Keys with MySQL 3.23.49 and 3.23.50
Hi Nothing is wrong! The behaviour of your example is correct, because MySQL does not support the concept of foreign keys. See section 1.4.4.5 of the MySQL manual. In other words data integrity between two tables is not ensured by MySQL. Your application code must solve this. Regards Rene Moonen Jostkleigrewe, Heiner wrote: sql,query Hello, i've tried the foreign key-example out of the documentation(3.5.6 Using Foreign Keys) on W2K (3.23.49 + 3.23.50) and LINUX (3.23.49). The tables persons and shirts are created as innodb-tables. I could insert a shirt for a non-existing person. Also i could delete a person with existing shirts. I have experimented with 'ON DELETE' and 'ON UPDATE' with no effects. What's wrong? Is there a switch to activate foreign keys? Heiner Jostkleigrewe - 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: Foreign Keys with MySQL 3.23.49 and 3.23.50
This example is in InnoDB though, which should support foreign key constraints. Chris Book In other words data integrity between two tables is not ensured by MySQL. Your application code must solve this. - 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
AW: Foreign Keys with MySQL 3.23.49 and 3.23.50
Hello, excuse me. I've looked at the wrong place. I found the solution at www.innodb.com: 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(id) ON DELETE CASCADE ) TYPE=INNODB; This examples works great. Heiner Jostkleigrewe -Ursprüngliche Nachricht- Von: Harald Fuchs [SMTP:[EMAIL PROTECTED]] Gesendet am: Dienstag, 28. Mai 2002 12:46 An: [EMAIL PROTECTED] Betreff: Re: Foreign Keys with MySQL 3.23.49 and 3.23.50 In article [EMAIL PROTECTED], Jostkleigrewe, Heiner [EMAIL PROTECTED] writes: sql,query Hello, i've tried the foreign key-example out of the documentation(3.5.6 Using Foreign Keys) on W2K (3.23.49 + 3.23.50) and LINUX (3.23.49). The tables persons and shirts are created as innodb-tables. I could insert a shirt for a non-existing person. Also i could delete a person with existing shirts. I have experimented with 'ON DELETE' and 'ON UPDATE' with no effects. What's wrong? Is there a switch to activate foreign keys? No. Could you post some SQL code which lets us reproduce the problem? - 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: foreign keys in mysql
Hi, On Thu, 2002-03-07 at 08:20, bin cai wrote: I am encountering one problem. I am using mysql as the dabase for my application. I create a table like takeexam as following:create table takelabexam( sid varchar(10) not null, cid varchar(20) not null, marks integer foreign key (cid) references course on Update cascade , primary key (sid, cid,labsection,year)); cid in takelabexam references cid in table courses. so if i update the cid in courses table. the records in table takelabexam which references to courses should be updated automatically. but it doesn't work. Could any one can help me out. i will appreciate very much http://www.mysql.com/doc/A/N/ANSI_diff_Foreign_Keys.html Regards, Arjen. -- MySQL Training in Brisbane: 18-22 March, http://www.mysql.com/training/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Arjen G. Lentz [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer, Trainer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia ___/ 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: InnoDB foreign keys crash MySQL
Harald, I tested the below with 3.23.48, and it worked ok. Could it be that you have not used DROP TABLE or DROP DATABASE to remove InnoDB tables? Then the internal data dictionary may be out-of-sync from the .frm files of your tables. Please use innodb_table_monitor (section 9.1 in http://www.innodb.com/ibman.html ) to print the contents of the internal data dictionary, and compare it to the .frm files you have for the tables. Please send the output to the mailing list. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB Harald Fuchs wrote in message ... (MySQL 4.0.1-alpha-Max-log, your Linux binary) The following works fine (just a slight variation of what is in the manual): CREATE TABLE t1 ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) ) TYPE=InnoDB; CREATE TABLE t2 ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, t1id INT UNSIGNED NOT NULL, PRIMARY KEY (id), KEY (t1id), FOREIGN KEY (t1id) REFERENCES t1(id) ) TYPE=InnoDB; The following, however, crashes MySQL: CREATE TABLE t1 ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) ) TYPE=InnoDB; CREATE TABLE t2 ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, t1id INT UNSIGNED NOT NULL, PRIMARY KEY (id), KEY (t1id), FOREIGN KEY (t1id) REFERENCES t1(id) ) TYPE=InnoDB; CREATE TABLE t3 ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, t2id INT UNSIGNED NOT NULL, PRIMARY KEY (id), KEY (t2id), FOREIGN KEY (t2id) REFERENCES t2(id) ) TYPE=InnoDB; The log file says: InnoDB: foreign constraint creation failed; InnoDB: internal error number 17 InnoDB: Assertion failure in thread 45067 in file dict0crea.c line 1241 InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to [EMAIL PROTECTED] mysqld got signal 11; I can send you the stack trace if necessary. - 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: InnoDB foreign keys crash MySQL
In article 003b01c1b0a4$351665f0$540ec5c2@omnibook, Heikki Tuuri [EMAIL PROTECTED] writes: Harald, I tested the below with 3.23.48, and it worked ok. Could it be that you have not used DROP TABLE or DROP DATABASE to remove InnoDB tables? Then the internal data dictionary may be out-of-sync from the .frm files of your tables. I just tried it again on a fresh MySQL/InnoDB installation (a new database to be created etc) and it still crashes. Please use innodb_table_monitor (section 9.1 in http://www.innodb.com/ibman.html ) to print the contents of the internal data dictionary, and compare it to the .frm files you have for the tables. Please send the output to the mailing list. Before the crash: === 020208 14:42:39 INNODB TABLE MONITOR OUTPUT === -- TABLE: name SYS_FOREIGN, id 0 11, columns 8, indexes 3, appr.rows 0 COLUMNS: ID: DATA_VARCHAR DATA_ENGLISH len 0 prec 0; FOR_NAME: DATA_VARCHAR DATA_ENGLISH len 0 prec 0; REF_NAME: DATA_VARCHAR DATA_ENGLISH len 0 prec 0; N_COLS: DATA_INT len 4 prec 0; DB_ROW_ID: DATA_SYS DATA_ROW_ID len 6 prec 0; DB_TRX_ID: DATA_SYS DATA_TRX_ID len 6 prec 0; DB_ROLL_PTR: DATA_SYS DATA_ROLL_PTR len 7 prec 0; INDEX: name ID_IND, table name SYS_FOREIGN, id 0 11, fields 1/6, type 3 root page 46, appr.key vals 0, leaf pages 1, size pages 1 FIELDS: ID DB_TRX_ID DB_ROLL_PTR FOR_NAME REF_NAME N_COLS INDEX: name FOR_IND, table name SYS_FOREIGN, id 0 12, fields 1/2, type 0 root page 47, appr.key vals 0, leaf pages 1, size pages 1 FIELDS: FOR_NAME ID INDEX: name REF_IND, table name SYS_FOREIGN, id 0 13, fields 1/2, type 0 root page 48, appr.key vals 0, leaf pages 1, size pages 1 FIELDS: REF_NAME ID -- TABLE: name SYS_FOREIGN_COLS, id 0 12, columns 8, indexes 1, appr.rows 0 COLUMNS: ID: DATA_VARCHAR DATA_ENGLISH len 0 prec 0; POS: DATA_INT len 4 prec 0; FOR_COL_NAME: DATA_VARCHAR DATA_ENGLISH len 0 prec 0; REF_COL_NAME: DATA_VARCHAR DATA_ENGLISH len 0 prec 0; DB_ROW_ID: DATA_SYS DATA_ROW_ID len 6 prec 0; DB_TRX_ID: DATA_SYS DATA_TRX_ID len 6 prec 0; DB_ROLL_PTR: DATA_SYS DATA_ROLL_PTR len 7 prec 0; INDEX: name ID_IND, table name SYS_FOREIGN_COLS, id 0 14, fields 2/6, type 3 root page 49, appr.key vals 0, leaf pages 1, size pages 1 FIELDS: ID POS DB_TRX_ID DB_ROLL_PTR FOR_COL_NAME REF_COL_NAME -- TABLE: name test/innodb_table_monitor, id 0 18, columns 5, indexes 1, appr.rows 0 COLUMNS: a: DATA_INT len 4 prec 0; DB_ROW_ID: DATA_SYS DATA_ROW_ID len 6 prec 0; DB_TRX_ID: DATA_SYS DATA_TRX_ID len 6 prec 0; DB_ROLL_PTR: DATA_SYS DATA_ROLL_PTR len 7 prec 0; INDEX: name GEN_CLUST_INDEX, table name test/innodb_table_monitor, id 0 20, fields 0/4, type 1 root page 51, appr.key vals 0, leaf pages 1, size pages 1 FIELDS: DB_ROW_ID DB_TRX_ID DB_ROLL_PTR a --- END OF INNODB TABLE MONITOR OUTPUT == No tables whatsoever, as you can see. During the crash: InnoDB: foreign constraint creation failed; InnoDB: internal error number 17 InnoDB: Assertion failure in thread 53260 in file dict0crea.c line 1241 InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to [EMAIL PROTECTED] mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=67104768 record_buffer=258048 sort_buffer=2097144 max_used_connections=1 max_connections=100 threads_connected=2 It is possible that mysqld could use up to key_buffer_size + (record_buffer + sort_buffer)*max_connections = 295531 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. InnoDB: Thread 20486 stopped in file os0sync.c line 374 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Stack range sanity check OK, backtrace follows: 0x807db8f 0x82bc35a 0x817c93b 0x817fd18 0x81a2725 0x80d99c3 0x80cd6ed 0x80c1f46 0x80de9c2 0x808662a 0x808a522 0x8085117 0x808a954 0x8084556 Stack trace seems successful - bottom reached Please read http://www.mysql.com/doc/U/s/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at
Re: InnoDB foreign keys crash MySQL
test/t1 ( id ) -- TABLE: name test/t2, id 0 14, columns 6, indexes 2, appr.rows 0 COLUMNS: id: DATA_INT len 4 prec 0; t1id: DATA_INT len 4 prec 0; DB_ROW_ID: DA TA_SYS DATA_ROW_ID len 6 prec 0; DB_TRX_ID: DATA_SYS DATA_TRX_ID len 6 prec 0; D B_ROLL_PTR: DATA_SYS DATA_ROLL_PTR len 7 prec 0; INDEX: name PRIMARY, table name test/t2, id 0 16, fields 1/4, type 3 root page 51, appr.key vals 0, leaf pages 1, size pages 1 FIELDS: id DB_TRX_ID DB_ROLL_PTR t1id INDEX: name t1id, table name test/t2, id 0 17, fields 1/2, type 0 root page 52, appr.key vals 0, leaf pages 1, size pages 1 FIELDS: t1id id FOREIGN KEY CONSTRAINT 0_15: test/t2 ( t1id ) REFERENCES test/t1 ( id ) FOREIGN KEY CONSTRAINT 0_17: test/t3 ( t2id ) REFERENCES test/t2 ( id ) -- TABLE: name test/t3, id 0 16, columns 6, indexes 2, appr.rows 0 COLUMNS: id: DATA_INT len 4 prec 0; t2id: DATA_INT len 4 prec 0; DB_ROW_ID: DA TA_SYS DATA_ROW_ID len 6 prec 0; DB_TRX_ID: DATA_SYS DATA_TRX_ID len 6 prec 0; D B_ROLL_PTR: DATA_SYS DATA_ROLL_PTR len 7 prec 0; INDEX: name PRIMARY, table name test/t3, id 0 18, fields 1/4, type 3 root page 53, appr.key vals 0, leaf pages 1, size pages 1 FIELDS: id DB_TRX_ID DB_ROLL_PTR t2id INDEX: name t2id, table name test/t3, id 0 19, fields 1/2, type 0 root page 54, appr.key vals 0, leaf pages 1, size pages 1 FIELDS: t2id id FOREIGN KEY CONSTRAINT 0_17: test/t3 ( t2id ) REFERENCES test/t2 ( id ) --- END OF INNODB TABLE MONITOR OUTPUT == -Original Message- From: Harald Fuchs [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] [EMAIL PROTECTED] Date: Friday, February 08, 2002 3:50 PM Subject: Re: InnoDB foreign keys crash MySQL In article 003b01c1b0a4$351665f0$540ec5c2@omnibook, Heikki Tuuri [EMAIL PROTECTED] writes: Harald, I tested the below with 3.23.48, and it worked ok. Could it be that you have not used DROP TABLE or DROP DATABASE to remove InnoDB tables? Then the internal data dictionary may be out-of-sync from the .frm files of your tables. I just tried it again on a fresh MySQL/InnoDB installation (a new database to be created etc) and it still crashes. Please use innodb_table_monitor (section 9.1 in http://www.innodb.com/ibman.html ) to print the contents of the internal data dictionary, and compare it to the .frm files you have for the tables. Please send the output to the mailing list. Before the crash: === 020208 14:42:39 INNODB TABLE MONITOR OUTPUT === -- TABLE: name SYS_FOREIGN, id 0 11, columns 8, indexes 3, appr.rows 0 COLUMNS: ID: DATA_VARCHAR DATA_ENGLISH len 0 prec 0; FOR_NAME: DATA_VARCHAR DATA_ENGLISH len 0 prec 0; REF_NAME: DATA_VARCHAR DATA_ENGLISH len 0 prec 0; N_COLS: DATA_INT len 4 prec 0; DB_ROW_ID: DATA_SYS DATA_ROW_ID len 6 prec 0; DB_TRX_ID: DATA_SYS DATA_TRX_ID len 6 prec 0; DB_ROLL_PTR: DATA_SYS DATA_ROLL_PTR len 7 prec 0; INDEX: name ID_IND, table name SYS_FOREIGN, id 0 11, fields 1/6, type 3 root page 46, appr.key vals 0, leaf pages 1, size pages 1 FIELDS: ID DB_TRX_ID DB_ROLL_PTR FOR_NAME REF_NAME N_COLS INDEX: name FOR_IND, table name SYS_FOREIGN, id 0 12, fields 1/2, type 0 root page 47, appr.key vals 0, leaf pages 1, size pages 1 FIELDS: FOR_NAME ID INDEX: name REF_IND, table name SYS_FOREIGN, id 0 13, fields 1/2, type 0 root page 48, appr.key vals 0, leaf pages 1, size pages 1 FIELDS: REF_NAME ID -- TABLE: name SYS_FOREIGN_COLS, id 0 12, columns 8, indexes 1, appr.rows 0 COLUMNS: ID: DATA_VARCHAR DATA_ENGLISH len 0 prec 0; POS: DATA_INT len 4 prec 0; FOR_COL_NAME: DATA_VARCHAR DATA_ENGLISH len 0 prec 0; REF_COL_NAME: DATA_VARCHAR DATA_ENGLISH len 0 prec 0; DB_ROW_ID: DATA_SYS DATA_ROW_ID len 6 prec 0; DB_TRX_ID: DATA_SYS DATA_TRX_ID len 6 prec 0; DB_ROLL_PTR: DATA_SYS DATA_ROLL_PTR len 7 prec 0; INDEX: name ID_IND, table name SYS_FOREIGN_COLS, id 0 14, fields 2/6, type 3 root page 49, appr.key vals 0, leaf pages 1, size pages 1 FIELDS: ID POS DB_TRX_ID DB_ROLL_PTR FOR_COL_NAME REF_COL_NAME -- TABLE: name test/innodb_table_monitor, id 0 18, columns 5, indexes 1, appr.rows 0 COLUMNS: a: DATA_INT len 4 prec 0; DB_ROW_ID: DATA_SYS DATA_ROW_ID len 6 prec 0; DB_TRX_ID: DATA_SYS DATA_TRX_ID len 6 prec 0; DB_ROLL_PTR: DATA_SYS DATA_ROLL_PTR len 7 prec 0; INDEX: name GEN_CLUST_INDEX, table name test/innodb_table_monitor, id 0 20, fields 0/4, type 1 root page 51, appr.key vals 0, leaf pages 1, size pages 1 FIELDS: DB_ROW_ID DB_TRX_ID DB_ROLL_PTR a --- END OF INNODB TABLE MONITOR OUTPUT == No tables whatsoever
Re: InnoDB foreign keys crash MySQL
Harald, I was now able to repeat the error and fixed it. If one defines a non-latin1 character set as the default (german1 in your case), then the sorting order of the InnoDB internal datatype DATA_VARCHAR was undefined, and foreign key system tables did not work, because they contain DATA_VARCHAR columns. The fix probably makes it to upcoming MySQL-4.0.2, if Monty has not yet frozen the 4.0.2 codebase. It will also be in 3.23.49. Best regards, Heikki Innobase Oy -Original Message- From: Harald Fuchs [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED] Date: Saturday, February 09, 2002 3:59 PM Subject: Re: InnoDB foreign keys crash MySQL Harald, Hi, Heikki! ... I tested now with the official Linux binary of 4.0.1 (not -max) and it worked ok on our dual Xeon Linux-2.4.16-SMP-64GB. I did not define any InnoDB startup options in my.cnf. I just downloaded mysql-4.0.1-alpha-pc-linux-gnu-i686.tar.gz (now also not -max) from the German mirror and tried it. I still get the crash. My system is a 550MHz AMD K6-2 running kernel 2.2.18, if this should matter. I don't think this is a hardware bug: I tried the same script on another Linux box (dual 450MHz Pentium II, 2.2.18-SMP) running 4.0.1-max and also get the crash. When you recreated the InnoDB data files, did you remember to remove the .frm files of the InnoDB tables t1, t2, t3 in the database directory 'test'? Yes. Actually, I did rm -rf test/* ib* before starting MySQL. What is your my.cnf like? After removing all InnoDB directives, there's not much left: [client] port = 3306 [mysqld] port = 3306 user = mysql skip-locking set-variable = join_buffer_size=512k set-variable = key_buffer_size=64M set-variable = record_buffer=256k set-variable = sort_buffer=2M set-variable = table_cache=16 set-variable = tmp_table_size=32M log-slow-queries default-character-set=german1 The SQL command sequence causing the crash is as follows: DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2; DROP TABLE IF EXISTS t3; CREATE TABLE t1 ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) ) TYPE=InnoDB; CREATE TABLE t2 ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, t1id INT UNSIGNED NOT NULL, PRIMARY KEY (id), KEY (t1id), FOREIGN KEY (t1id) REFERENCES t1(id) ) TYPE=InnoDB; CREATE TABLE t3 ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, t2id INT UNSIGNED NOT NULL, PRIMARY KEY (id), KEY (t2id), FOREIGN KEY (t2id) REFERENCES t2(id) ) TYPE=InnoDB; DROP TABLE IF EXISTS t3; DROP TABLE IF EXISTS t2; DROP TABLE IF EXISTS t1; Any other information I could supply? - 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
InnoDB foreign keys crash MySQL
(MySQL 4.0.1-alpha-Max-log, your Linux binary) The following works fine (just a slight variation of what is in the manual): CREATE TABLE t1 ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) ) TYPE=InnoDB; CREATE TABLE t2 ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, t1id INT UNSIGNED NOT NULL, PRIMARY KEY (id), KEY (t1id), FOREIGN KEY (t1id) REFERENCES t1(id) ) TYPE=InnoDB; The following, however, crashes MySQL: CREATE TABLE t1 ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) ) TYPE=InnoDB; CREATE TABLE t2 ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, t1id INT UNSIGNED NOT NULL, PRIMARY KEY (id), KEY (t1id), FOREIGN KEY (t1id) REFERENCES t1(id) ) TYPE=InnoDB; CREATE TABLE t3 ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, t2id INT UNSIGNED NOT NULL, PRIMARY KEY (id), KEY (t2id), FOREIGN KEY (t2id) REFERENCES t2(id) ) TYPE=InnoDB; The log file says: InnoDB: foreign constraint creation failed; InnoDB: internal error number 17 InnoDB: Assertion failure in thread 45067 in file dict0crea.c line 1241 InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to [EMAIL PROTECTED] mysqld got signal 11; I can send you the stack trace if necessary. - 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: InnoDB foreign keys crash MySQL
Harald, I was now able to repeat the error and fixed it. If one defines a non-latin1 character set as the default (german1 in your case), then the sorting order of the InnoDB internal datatype DATA_VARCHAR was undefined, and foreign key system tables did not work, because they contain DATA_VARCHAR columns. The fix probably makes it to upcoming MySQL-4.0.2, if Monty has not yet frozen the 4.0.2 codebase. It will also be in 3.23.49. Best regards, Heikki Innobase Oy -Original Message- From: Harald Fuchs [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED] Date: Saturday, February 09, 2002 3:59 PM Subject: Re: InnoDB foreign keys crash MySQL Harald, Hi, Heikki! ... I tested now with the official Linux binary of 4.0.1 (not -max) and it worked ok on our dual Xeon Linux-2.4.16-SMP-64GB. I did not define any InnoDB startup options in my.cnf. I just downloaded mysql-4.0.1-alpha-pc-linux-gnu-i686.tar.gz (now also not -max) from the German mirror and tried it. I still get the crash. My system is a 550MHz AMD K6-2 running kernel 2.2.18, if this should matter. I don't think this is a hardware bug: I tried the same script on another Linux box (dual 450MHz Pentium II, 2.2.18-SMP) running 4.0.1-max and also get the crash. When you recreated the InnoDB data files, did you remember to remove the .frm files of the InnoDB tables t1, t2, t3 in the database directory 'test'? Yes. Actually, I did rm -rf test/* ib* before starting MySQL. What is your my.cnf like? After removing all InnoDB directives, there's not much left: [client] port = 3306 [mysqld] port = 3306 user = mysql skip-locking set-variable = join_buffer_size=512k set-variable = key_buffer_size=64M set-variable = record_buffer=256k set-variable = sort_buffer=2M set-variable = table_cache=16 set-variable = tmp_table_size=32M log-slow-queries default-character-set=german1 The SQL command sequence causing the crash is as follows: DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2; DROP TABLE IF EXISTS t3; CREATE TABLE t1 ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) ) TYPE=InnoDB; CREATE TABLE t2 ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, t1id INT UNSIGNED NOT NULL, PRIMARY KEY (id), KEY (t1id), FOREIGN KEY (t1id) REFERENCES t1(id) ) TYPE=InnoDB; CREATE TABLE t3 ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, t2id INT UNSIGNED NOT NULL, PRIMARY KEY (id), KEY (t2id), FOREIGN KEY (t2id) REFERENCES t2(id) ) TYPE=InnoDB; DROP TABLE IF EXISTS t3; DROP TABLE IF EXISTS t2; DROP TABLE IF EXISTS t1; Any other information I could supply? - 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: InnoDB foreign keys crash MySQL
Harald, I tested the below with 3.23.48, and it worked ok. Could it be that you have not used DROP TABLE or DROP DATABASE to remove InnoDB tables? Then the internal data dictionary may be out-of-sync from the .frm files of your tables. Please use innodb_table_monitor (section 9.1 in http://www.innodb.com/ibman.html ) to print the contents of the internal data dictionary, and compare it to the .frm files you have for the tables. Please send the output to the mailing list. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB Harald Fuchs wrote in message ... (MySQL 4.0.1-alpha-Max-log, your Linux binary) The following works fine (just a slight variation of what is in the manual): CREATE TABLE t1 ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) ) TYPE=InnoDB; CREATE TABLE t2 ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, t1id INT UNSIGNED NOT NULL, PRIMARY KEY (id), KEY (t1id), FOREIGN KEY (t1id) REFERENCES t1(id) ) TYPE=InnoDB; The following, however, crashes MySQL: CREATE TABLE t1 ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) ) TYPE=InnoDB; CREATE TABLE t2 ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, t1id INT UNSIGNED NOT NULL, PRIMARY KEY (id), KEY (t1id), FOREIGN KEY (t1id) REFERENCES t1(id) ) TYPE=InnoDB; CREATE TABLE t3 ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, t2id INT UNSIGNED NOT NULL, PRIMARY KEY (id), KEY (t2id), FOREIGN KEY (t2id) REFERENCES t2(id) ) TYPE=InnoDB; The log file says: InnoDB: foreign constraint creation failed; InnoDB: internal error number 17 InnoDB: Assertion failure in thread 45067 in file dict0crea.c line 1241 InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to [EMAIL PROTECTED] mysqld got signal 11; I can send you the stack trace if necessary. - 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: InnoDB foreign keys crash MySQL
test/t1 ( id ) -- TABLE: name test/t2, id 0 14, columns 6, indexes 2, appr.rows 0 COLUMNS: id: DATA_INT len 4 prec 0; t1id: DATA_INT len 4 prec 0; DB_ROW_ID: DA TA_SYS DATA_ROW_ID len 6 prec 0; DB_TRX_ID: DATA_SYS DATA_TRX_ID len 6 prec 0; D B_ROLL_PTR: DATA_SYS DATA_ROLL_PTR len 7 prec 0; INDEX: name PRIMARY, table name test/t2, id 0 16, fields 1/4, type 3 root page 51, appr.key vals 0, leaf pages 1, size pages 1 FIELDS: id DB_TRX_ID DB_ROLL_PTR t1id INDEX: name t1id, table name test/t2, id 0 17, fields 1/2, type 0 root page 52, appr.key vals 0, leaf pages 1, size pages 1 FIELDS: t1id id FOREIGN KEY CONSTRAINT 0_15: test/t2 ( t1id ) REFERENCES test/t1 ( id ) FOREIGN KEY CONSTRAINT 0_17: test/t3 ( t2id ) REFERENCES test/t2 ( id ) -- TABLE: name test/t3, id 0 16, columns 6, indexes 2, appr.rows 0 COLUMNS: id: DATA_INT len 4 prec 0; t2id: DATA_INT len 4 prec 0; DB_ROW_ID: DA TA_SYS DATA_ROW_ID len 6 prec 0; DB_TRX_ID: DATA_SYS DATA_TRX_ID len 6 prec 0; D B_ROLL_PTR: DATA_SYS DATA_ROLL_PTR len 7 prec 0; INDEX: name PRIMARY, table name test/t3, id 0 18, fields 1/4, type 3 root page 53, appr.key vals 0, leaf pages 1, size pages 1 FIELDS: id DB_TRX_ID DB_ROLL_PTR t2id INDEX: name t2id, table name test/t3, id 0 19, fields 1/2, type 0 root page 54, appr.key vals 0, leaf pages 1, size pages 1 FIELDS: t2id id FOREIGN KEY CONSTRAINT 0_17: test/t3 ( t2id ) REFERENCES test/t2 ( id ) --- END OF INNODB TABLE MONITOR OUTPUT == -Original Message- From: Harald Fuchs [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] [EMAIL PROTECTED] Date: Friday, February 08, 2002 3:50 PM Subject: Re: InnoDB foreign keys crash MySQL In article 003b01c1b0a4$351665f0$540ec5c2@omnibook, Heikki Tuuri [EMAIL PROTECTED] writes: Harald, I tested the below with 3.23.48, and it worked ok. Could it be that you have not used DROP TABLE or DROP DATABASE to remove InnoDB tables? Then the internal data dictionary may be out-of-sync from the .frm files of your tables. I just tried it again on a fresh MySQL/InnoDB installation (a new database to be created etc) and it still crashes. Please use innodb_table_monitor (section 9.1 in http://www.innodb.com/ibman.html ) to print the contents of the internal data dictionary, and compare it to the .frm files you have for the tables. Please send the output to the mailing list. Before the crash: === 020208 14:42:39 INNODB TABLE MONITOR OUTPUT === -- TABLE: name SYS_FOREIGN, id 0 11, columns 8, indexes 3, appr.rows 0 COLUMNS: ID: DATA_VARCHAR DATA_ENGLISH len 0 prec 0; FOR_NAME: DATA_VARCHAR DATA_ENGLISH len 0 prec 0; REF_NAME: DATA_VARCHAR DATA_ENGLISH len 0 prec 0; N_COLS: DATA_INT len 4 prec 0; DB_ROW_ID: DATA_SYS DATA_ROW_ID len 6 prec 0; DB_TRX_ID: DATA_SYS DATA_TRX_ID len 6 prec 0; DB_ROLL_PTR: DATA_SYS DATA_ROLL_PTR len 7 prec 0; INDEX: name ID_IND, table name SYS_FOREIGN, id 0 11, fields 1/6, type 3 root page 46, appr.key vals 0, leaf pages 1, size pages 1 FIELDS: ID DB_TRX_ID DB_ROLL_PTR FOR_NAME REF_NAME N_COLS INDEX: name FOR_IND, table name SYS_FOREIGN, id 0 12, fields 1/2, type 0 root page 47, appr.key vals 0, leaf pages 1, size pages 1 FIELDS: FOR_NAME ID INDEX: name REF_IND, table name SYS_FOREIGN, id 0 13, fields 1/2, type 0 root page 48, appr.key vals 0, leaf pages 1, size pages 1 FIELDS: REF_NAME ID -- TABLE: name SYS_FOREIGN_COLS, id 0 12, columns 8, indexes 1, appr.rows 0 COLUMNS: ID: DATA_VARCHAR DATA_ENGLISH len 0 prec 0; POS: DATA_INT len 4 prec 0; FOR_COL_NAME: DATA_VARCHAR DATA_ENGLISH len 0 prec 0; REF_COL_NAME: DATA_VARCHAR DATA_ENGLISH len 0 prec 0; DB_ROW_ID: DATA_SYS DATA_ROW_ID len 6 prec 0; DB_TRX_ID: DATA_SYS DATA_TRX_ID len 6 prec 0; DB_ROLL_PTR: DATA_SYS DATA_ROLL_PTR len 7 prec 0; INDEX: name ID_IND, table name SYS_FOREIGN_COLS, id 0 14, fields 2/6, type 3 root page 49, appr.key vals 0, leaf pages 1, size pages 1 FIELDS: ID POS DB_TRX_ID DB_ROLL_PTR FOR_COL_NAME REF_COL_NAME -- TABLE: name test/innodb_table_monitor, id 0 18, columns 5, indexes 1, appr.rows 0 COLUMNS: a: DATA_INT len 4 prec 0; DB_ROW_ID: DATA_SYS DATA_ROW_ID len 6 prec 0; DB_TRX_ID: DATA_SYS DATA_TRX_ID len 6 prec 0; DB_ROLL_PTR: DATA_SYS DATA_ROLL_PTR len 7 prec 0; INDEX: name GEN_CLUST_INDEX, table name test/innodb_table_monitor, id 0 20, fields 0/4, type 1 root page 51, appr.key vals 0, leaf pages 1, size pages 1 FIELDS: DB_ROW_ID DB_TRX_ID DB_ROLL_PTR a --- END OF INNODB TABLE MONITOR OUTPUT == No tables whatsoever
Making Foreign Keys within MySQL?
I realized I made my previous post look like it was a reply and not the
original question, so here I go again...=o)
Original message below
=
Hello,
I have tables in a user administration database and need to link different
tables with FK's for integrity.
How do I do this?
for example:
I have two tables, one called applications, the other called
forum_moderators.
Applications Catches all form submissions.
Forum_Moderators hold the details of active/current moderators
I want to link these two tables by rsm_id in applications, and fk_rsm_id in
forum_mod's.
How would i do this in MySQL and or PHP?
Table Structure:
for those that need a clearer picture of my layout, I have supplied the SQL
statement to create the tables
#
#
# Table structure for table 'applications'
#
DROP TABLE IF EXISTS applications;
CREATE TABLE `applications` (
`rsm_id` tinyint(4) NOT NULL auto_increment,
`first_name` varchar(20) NOT NULL default '',
`last_name` varchar(20) NOT NULL default '',
`city` varchar(10) NOT NULL default '',
`state` char(2) NOT NULL default '',
`dob` varchar(10) NOT NULL default '',
`forum_nick` varchar(20) NOT NULL default '',
`forum_select1` varchar(30) NOT NULL default '',
`forum_select2` varchar(30) NOT NULL default '',
`forum_select3` varchar(30) NOT NULL default '',
`bio` blob NOT NULL,
`email_addr` varchar(100) NOT NULL default '',
`processed` set('0','1') NOT NULL default '',
`add_date` date NOT NULL default '-00-00',
`accept_denied` set('A','D') NOT NULL default '',
`acc_dec_date` date NOT NULL default '-00-00',
PRIMARY KEY (`rsm_id`),
UNIQUE KEY `email_addr` (`email_addr`),
UNIQUE KEY `forum_nick` (`forum_nick`),
UNIQUE KEY `rsm_id` (`rsm_id`),
KEY `rsm_id_2` (`rsm_id`),
KEY `forum_nick_2` (`forum_nick`),
KEY `email_addr_2` (`email_addr`)
#
#
# Table structure for table 'forum_moderators'
#
DROP TABLE IF EXISTS forum_moderators;
CREATE TABLE `forum_moderators` (
`formod_id` tinyint(4) NOT NULL auto_increment,
`name` varchar(40) NOT NULL default '',
`city` varchar(10) NOT NULL default '',
`state` char(2) NOT NULL default '',
`forum_nick` varchar(20) NOT NULL default '',
`mod_forum_1` varchar(30) NOT NULL default '',
`mod_forum_2` varchar(30) NOT NULL default '',
`mod_forum_3` varchar(30) NOT NULL default '',
`email_addr` varchar(100) NOT NULL default '',
`tremination_date` date default NULL,
`fk_rsm_id` tinyint(4) NOT NULL default '0',
PRIMARY KEY (`formod_id`),
UNIQUE KEY `formod_id` (`formod_id`,`forum_nick`),
KEY `formod_id_2` (`formod_id`)
) TYPE=MyISAM;
#
#
Regards,
Charlie
-
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: Making Foreign Keys within MySQL?
The only table type at this moment that supports foreign keys is innodb.
it is distributed with mysql 4.0.
However you need to take the source files or the tar file to instal, the
rpm's have an older version of innodb.
On creation of the table you have to define your constraints (cf manual at
www.innodb.com)
For the moment it is nog possible to do it with an alter table after the
table is created.
- Original Message -
From: McGrotty, Charles [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Friday, October 26, 2001 3:10 PM
Subject: Making Foreign Keys within MySQL?
I realized I made my previous post look like it was a reply and not the
original question, so here I go again... =o)
Original message below
=
Hello,
I have tables in a user administration database and need to link different
tables with FK's for integrity.
How do I do this?
for example:
I have two tables, one called applications, the other called
forum_moderators.
Applications Catches all form submissions.
Forum_Moderators hold the details of active/current moderators
I want to link these two tables by rsm_id in applications, and fk_rsm_id
in
forum_mod's.
How would i do this in MySQL and or PHP?
Table Structure:
for those that need a clearer picture of my layout, I have supplied the
SQL
statement to create the tables
#
#
# Table structure for table 'applications'
#
DROP TABLE IF EXISTS applications;
CREATE TABLE `applications` (
`rsm_id` tinyint(4) NOT NULL auto_increment,
`first_name` varchar(20) NOT NULL default '',
`last_name` varchar(20) NOT NULL default '',
`city` varchar(10) NOT NULL default '',
`state` char(2) NOT NULL default '',
`dob` varchar(10) NOT NULL default '',
`forum_nick` varchar(20) NOT NULL default '',
`forum_select1` varchar(30) NOT NULL default '',
`forum_select2` varchar(30) NOT NULL default '',
`forum_select3` varchar(30) NOT NULL default '',
`bio` blob NOT NULL,
`email_addr` varchar(100) NOT NULL default '',
`processed` set('0','1') NOT NULL default '',
`add_date` date NOT NULL default '-00-00',
`accept_denied` set('A','D') NOT NULL default '',
`acc_dec_date` date NOT NULL default '-00-00',
PRIMARY KEY (`rsm_id`),
UNIQUE KEY `email_addr` (`email_addr`),
UNIQUE KEY `forum_nick` (`forum_nick`),
UNIQUE KEY `rsm_id` (`rsm_id`),
KEY `rsm_id_2` (`rsm_id`),
KEY `forum_nick_2` (`forum_nick`),
KEY `email_addr_2` (`email_addr`)
#
#
# Table structure for table 'forum_moderators'
#
DROP TABLE IF EXISTS forum_moderators;
CREATE TABLE `forum_moderators` (
`formod_id` tinyint(4) NOT NULL auto_increment,
`name` varchar(40) NOT NULL default '',
`city` varchar(10) NOT NULL default '',
`state` char(2) NOT NULL default '',
`forum_nick` varchar(20) NOT NULL default '',
`mod_forum_1` varchar(30) NOT NULL default '',
`mod_forum_2` varchar(30) NOT NULL default '',
`mod_forum_3` varchar(30) NOT NULL default '',
`email_addr` varchar(100) NOT NULL default '',
`tremination_date` date default NULL,
`fk_rsm_id` tinyint(4) NOT NULL default '0',
PRIMARY KEY (`formod_id`),
UNIQUE KEY `formod_id` (`formod_id`,`forum_nick`),
KEY `formod_id_2` (`formod_id`)
) TYPE=MyISAM;
#
#
Regards,
Charlie
-
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: Making Foreign Keys within MySQL?
you could dump the data in an .sql file (mysqldump)
edit the .sql file to make the necessary changes
import the .sql file
- Original Message -
From: McGrotty, Charles [EMAIL PROTECTED]
To: 'Tore Van Grembergen' [EMAIL PROTECTED]
Sent: Friday, October 26, 2001 8:38 PM
Subject: RE: Making Foreign Keys within MySQL?
my tables are currently MyISAM,
is it possible to do a dump (inc data) and recreate the tables with the
following?
DROP TABLE IF EXISTS table name;
CREATE TABLE table name (
field names
) TYPE=innodb;
INSERT INTO table name VALUES (
values
);
regards,
Charlie
-Original Message-
From: Tore Van Grembergen [mailto:[EMAIL PROTECTED]]
Sent: Friday, October 26, 2001 2:03 PM
To: McGrotty, Charles; [EMAIL PROTECTED]
Subject: Re: Making Foreign Keys within MySQL?
The only table type at this moment that supports foreign keys is
innodb.
it is distributed with mysql 4.0.
However you need to take the source files or the tar file to instal, the
rpm's have an older version of innodb.
On creation of the table you have to define your constraints (cf manual at
www.innodb.com)
For the moment it is nog possible to do it with an alter table after the
table is created.
- Original Message -
From: McGrotty, Charles [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Friday, October 26, 2001 3:10 PM
Subject: Making Foreign Keys within MySQL?
I realized I made my previous post look like it was a reply and not the
original question, so here I go again... =o)
Original message below
=
Hello,
I have tables in a user administration database and need to link
different
tables with FK's for integrity.
How do I do this?
for example:
I have two tables, one called applications, the other called
forum_moderators.
Applications Catches all form submissions.
Forum_Moderators hold the details of active/current moderators
I want to link these two tables by rsm_id in applications, and fk_rsm_id
in
forum_mod's.
How would i do this in MySQL and or PHP?
Table Structure:
for those that need a clearer picture of my layout, I have supplied the
SQL
statement to create the tables
#
#
# Table structure for table 'applications'
#
DROP TABLE IF EXISTS applications;
CREATE TABLE `applications` (
`rsm_id` tinyint(4) NOT NULL auto_increment,
`first_name` varchar(20) NOT NULL default '',
`last_name` varchar(20) NOT NULL default '',
`city` varchar(10) NOT NULL default '',
`state` char(2) NOT NULL default '',
`dob` varchar(10) NOT NULL default '',
`forum_nick` varchar(20) NOT NULL default '',
`forum_select1` varchar(30) NOT NULL default '',
`forum_select2` varchar(30) NOT NULL default '',
`forum_select3` varchar(30) NOT NULL default '',
`bio` blob NOT NULL,
`email_addr` varchar(100) NOT NULL default '',
`processed` set('0','1') NOT NULL default '',
`add_date` date NOT NULL default '-00-00',
`accept_denied` set('A','D') NOT NULL default '',
`acc_dec_date` date NOT NULL default '-00-00',
PRIMARY KEY (`rsm_id`),
UNIQUE KEY `email_addr` (`email_addr`),
UNIQUE KEY `forum_nick` (`forum_nick`),
UNIQUE KEY `rsm_id` (`rsm_id`),
KEY `rsm_id_2` (`rsm_id`),
KEY `forum_nick_2` (`forum_nick`),
KEY `email_addr_2` (`email_addr`)
#
#
# Table structure for table 'forum_moderators'
#
DROP TABLE IF EXISTS forum_moderators;
CREATE TABLE `forum_moderators` (
`formod_id` tinyint(4) NOT NULL auto_increment,
`name` varchar(40) NOT NULL default '',
`city` varchar(10) NOT NULL default '',
`state` char(2) NOT NULL default '',
`forum_nick` varchar(20) NOT NULL default '',
`mod_forum_1` varchar(30) NOT NULL default '',
`mod_forum_2` varchar(30) NOT NULL default '',
`mod_forum_3` varchar(30) NOT NULL default '',
`email_addr` varchar(100) NOT NULL default '',
`tremination_date` date default NULL,
`fk_rsm_id` tinyint(4) NOT NULL default '0',
PRIMARY KEY (`formod_id`),
UNIQUE KEY `formod_id` (`formod_id`,`forum_nick`),
KEY `formod_id_2` (`formod_id`)
) TYPE=MyISAM;
#
#
Regards,
Charlie
-
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
