Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Cannot create new table in mySQL 3

Status
Not open for further replies.

lupidol

Programmer
Apr 23, 2008
125
IL
Hi everyone,
This is the code to create a new table:
[pre]CREATE TABLE melClans (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
KEY (id)
);[/pre]]
This is an error message I get in response to creating table with the above code:
[pre]#1813 - Tablespace for table '`skullcrashingwords`.`melclans`' exists. Please DISCARD the tablespace before IMPORT[/pre]
Since table already exists I type:
[pre]use skullcrashingwords;
select * from melClans;[/pre]
But I get the following error:
[pre]#1146 - Table 'skullcrashingwords.melclans' doesn't exist[/pre]
Any idea why I'm recieving these contradictory errors ?
Thanks
 
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
 
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...
 
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
 
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
 
Thanks a lot . Your aid was very enlightening !
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top