×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Cannot create new table in mySQL
3

Cannot create new table in mySQL

Cannot create new table in mySQL

(OP)
Hi everyone,
This is the code to create a new table:
CREATE TABLE melClans (
  id INT NOT NULL AUTO_INCREMENT, 
  name VARCHAR(255) NOT NULL,
  KEY (id)
); 
]
This is an error message I get in response to creating table with the above code:
#1813 - Tablespace for table '`skullcrashingwords`.`melclans`' exists. Please DISCARD the tablespace before IMPORT 
Since table already exists I type:
use skullcrashingwords;
 select * from melClans; 
But I get the following error:
#1146 - Table 'skullcrashingwords.melclans' doesn't exist 
Any idea why I'm recieving these contradictory errors ?
Thanks

RE: Cannot create new table in mySQL

With

CODE -->

use skullcrashingwords; 
You change the database.

Well, melClans still can exist in the database you were befoer switching to skullcrashingwords. and so it's not contradictory.

Chriss

RE: Cannot create new table in mySQL

You probably try to create a table which was once already created and then dropped
Does your database version support CREATE OR REPLACE ? When yes then try to create the table with

CODE

CREATE OR REPLACE TABLE melClans (
  id INT NOT NULL AUTO_INCREMENT, 
  name VARCHAR(255) NOT NULL,
  KEY (id)
); 
Maybe it helps...

RE: Cannot create new table in mySQL

(OP)
Thanks,
With all the complications I went through I dropped the database and created new one without foreign keys that too was part of complication and I stuck to innoDB engine which was another source of troubles.
Thanks

RE: Cannot create new table in mySQL

2
It's not a good idea to avoid foreign keys because they pose a problem to drop a table.
If you absolutely want to drop a table that has foreign keys to others, of course, you first drop the relationships due to foreign keys first.

But if you drop a table just to create it with a different structure and will have the same foreign key relationships with the other tables, that points out you should use ALTER TABLE to add or drop columns or change data types, instead of using a pair of drop/create statements.

In the long-term life of a database, your structural updates will be based on ALTER statements more than anything else. You'll also create new tables, but it's actually DROP that has no meaning in the long run. Even if you rearrange data into a new structure then you'd first create that new structure, move data to it and then turn key columns to normal to cut old relationships and then finally drop tables you don't want anymore.

Chriss

RE: Cannot create new table in mySQL

(OP)
Thanks a lot . Your aid was very enlightening !

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close