Solved

Error message for binding primary keys

Posted on 2010-11-20
18
1,209 Views
Last Modified: 2012-05-10
Hi;
I am trying to create relation between 3 tables with the same primary key and this is the error I get.  Can you please help me as I am brand new in mysql and this will be a huge favor ro me.

ERROR 1061: Duplicate key name 'Media_ID'

SQL Statement:

ALTER TABLE `iran79`.`media_owner_info`

ADD INDEX `Media_ID` (`Media_ID` ASC)

, ADD INDEX `Media_ID` (`Media_ID` ASC)



ERROR: Error when running failback script. Details follow.



ERROR 1046: No database selected

SQL Statement:

CREATE TABLE `media_owner_info` (

  `Media_ID` int(11) NOT NULL AUTO_INCREMENT,

  `First_Name` varchar(45) CHARACTER SET utf8 COLLATE utf8_persian_ci DEFAULT NULL,

  `Last_Name` varchar(45) CHARACTER SET utf8 DEFAULT NULL,

  `Email_Address` varchar(45) CHARACTER SET utf8 DEFAULT NULL,

  `Business Phone` varchar(45) CHARACTER SET utf8 DEFAULT NULL,

  `Job_Title` varchar(45) CHARACTER SET utf8 DEFAULT NULL,

  `Home_Phone` varchar(45) CHARACTER SET utf8 DEFAULT NULL,

  `Mobile_Phone` varchar(45) CHARACTER SET utf8 DEFAULT NULL,

  `Address` varchar(45) CHARACTER SET utf8 DEFAULT NULL,

  `Zip` varchar(45) CHARACTER SET utf8 DEFAULT NULL,

  `State` varchar(45) CHARACTER SET utf8 DEFAULT NULL,

  `City` varchar(45) CHARACTER SET utf8 DEFAULT NULL,

  `Country` varchar(45) CHARACTER SET utf8 DEFAULT NULL,

  PRIMARY KEY (`Media_ID`)

) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

0
Comment
Question by:babak62
  • 9
  • 8
18 Comments
 
LVL 14

Expert Comment

by:leoahmad
ID: 34181579
one name can be used only for once.
to join 3 tables you need two pk, fk constraints with two different names
0
 

Author Comment

by:babak62
ID: 34181586
Can you please tell me how to correct the syntax as I have no idea where to do it.  Thanks a lot for the help.
0
 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 34181819
Does the table already exist?  Because this query:

CREATE TABLE `media_owner_info` (
  `Media_ID` int(11) NOT NULL AUTO_INCREMENT,
  `First_Name` varchar(45) CHARACTER SET utf8 COLLATE utf8_persian_ci DEFAULT NULL,
  `Last_Name` varchar(45) CHARACTER SET utf8 DEFAULT NULL,
  `Email_Address` varchar(45) CHARACTER SET utf8 DEFAULT NULL,
  `Business Phone` varchar(45) CHARACTER SET utf8 DEFAULT NULL,
  `Job_Title` varchar(45) CHARACTER SET utf8 DEFAULT NULL,
  `Home_Phone` varchar(45) CHARACTER SET utf8 DEFAULT NULL,
  `Mobile_Phone` varchar(45) CHARACTER SET utf8 DEFAULT NULL,
  `Address` varchar(45) CHARACTER SET utf8 DEFAULT NULL,
  `Zip` varchar(45) CHARACTER SET utf8 DEFAULT NULL,
  `State` varchar(45) CHARACTER SET utf8 DEFAULT NULL,
  `City` varchar(45) CHARACTER SET utf8 DEFAULT NULL,
  `Country` varchar(45) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (`Media_ID`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Open in new window


Works fine when I run it in a blank database...
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 34181826
If you are running:

ALTER TABLE `iran79`.`media_owner_info`
ADD INDEX `Media_ID` (`Media_ID` ASC),
ADD INDEX `Media_ID` (`Media_ID` ASC)

On the existing table, there is no need.  There is already an index on the Media_ID column because it is the Primary Key
0
 

Author Comment

by:babak62
ID: 34181849
Thanks for the response this is what I got back


ERROR 1046: No database selected

SQL Statement:

CREATE TABLE `media_owner_info` (

  `Media_ID` int(11) NOT NULL AUTO_INCREMENT,

  `First_Name` varchar(45) CHARACTER SET utf8 COLLATE utf8_persian_ci DEFAULT NULL,

  `Last_Name` varchar(45) CHARACTER SET utf8 DEFAULT NULL,

  `Email_Address` varchar(45) CHARACTER SET utf8 DEFAULT NULL,

  `Business Phone` varchar(45) CHARACTER SET utf8 DEFAULT NULL,

  `Job_Title` varchar(45) CHARACTER SET utf8 DEFAULT NULL,

  `Home_Phone` varchar(45) CHARACTER SET utf8 DEFAULT NULL,

  `Mobile_Phone` varchar(45) CHARACTER SET utf8 DEFAULT NULL,

  `Address` varchar(45) CHARACTER SET utf8 DEFAULT NULL,

  `Zip` varchar(45) CHARACTER SET utf8 DEFAULT NULL,

  `State` varchar(45) CHARACTER SET utf8 DEFAULT NULL,

  `City` varchar(45) CHARACTER SET utf8 DEFAULT NULL,

  `Country` varchar(45) CHARACTER SET utf8 DEFAULT NULL,

  PRIMARY KEY (`Media_ID`)

) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COLLATE=utf8_



ERROR: Error when running failback script. Details follow.



ERROR 1046: No database selected

SQL Statement:

CREATE TABLE `media_owner_info` (

  `Media_ID` int(11) NOT NULL AUTO_INCREMENT,

  `First_Name` varchar(45) CHARACTER SET utf8 COLLATE utf8_persian_ci DEFAULT NULL,

  `Last_Name` varchar(45) CHARACTER SET utf8 DEFAULT NULL,

  `Email_Address` varchar(45) CHARACTER SET utf8 DEFAULT NULL,

  `Business Phone` varchar(45) CHARACTER SET utf8 DEFAULT NULL,

  `Home_Phone` varchar(45) CHARACTER SET utf8 DEFAULT NULL,

  `Mobile_Phone` varchar(45) CHARACTER SET utf8 DEFAULT NULL,

  `Address` varchar(150) CHARACTER SET utf8 DEFAULT NULL,

  `Zip` varchar(45) CHARACTER SET utf8 DEFAULT NULL,

  `State` varchar(45) CHARACTER SET utf8 DEFAULT NULL,

  `City` varchar(45) CHARACTER SET utf8 DEFAULT NULL,

  `Country` varchar(45) CHARACTER SET utf8 DEFAULT NULL,

  PRIMARY KEY (`Media_ID`)

) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

0
 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 34181858
>> ERROR 1046: No database selected

Did you in fact, select a database?

How are you doing this?  From the MySQL command line?
0
 

Author Comment

by:babak62
ID: 34181871
I went to the workbench, opened the table and under the columns selected the tables, using the drop downs, and assigned the primary key "media_ID" to other tables and tried to apply, then when I got the first error in the sql screen pasted your code and ran it.  Sorry if it does not sound so logical but this is my second day working with these tools.
0
 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 34181880
I'm not real familiar with the Workbench (I don't use it) but something above sounds off.

Does media_owner_info already exist?  If so, does it have an auto-increment column defined?

For the other two tables, you add media_ID as a column but do NOT make it the primary key.  
0
 

Author Comment

by:babak62
ID: 34181888
I executed the same script in phpmyadmin and this is the error I got

#1273 - Unknown collation: 'utf8'
0
 

Author Comment

by:babak62
ID: 34181892
one more thing the database is not blank so could that be the problem?
0
 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 34181896
Answer my first questions:

Does media_owner_info already exist?  If so, does it have an auto-increment column defined?
0
 

Author Comment

by:babak62
ID: 34181909
Yes it does exist and the auto increment is the primary key also "Media_ID"
0
 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 34181914
Okay, so there is no need to run

CREATE TABLE `media_owner_info`

again, nor is there a need to ALTER it to add a new index on Media_ID

So what are you trying to do?

0
 

Author Comment

by:babak62
ID: 34181933
I am trying to update the other tables with the same key when I insert a record to the media_owner_info and insert the same record to other table at the same time.
0
 
LVL 70

Accepted Solution

by:
Jason C. Levine earned 500 total points
ID: 34181974
Can't do that solely through MySQL commands.  That happens via your PHP coding.

When you inset the record into the parent table, you need to use mysql_insert_id() after the insert operation to get the newly created primary key value.  You then use that value to update whatever other tables you need to but you don't insert the value into the primary key of the sub-tables.  Instead, you just insert it into a normal column and that becomes the Foreign Key.

If you are new to PHP and MySQL, then Dreamweaver is not going to be of any great to help to you.  It does not naturally operate like this with it's Insert and Update behaviors.
0
 

Author Comment

by:babak62
ID: 34181980
is there any other tool that I can use to do php which is in the same nature as dreamweaver that you suggest?
0
 

Author Closing Comment

by:babak62
ID: 34181982
Great Help
0
 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 34181989
>> is there any other tool that I can use to do php which is in the same nature as dreamweaver that you
>> suggest?

Kind of.  There is a paid extension for Dreamweaver that does integrate mysql_insert_id() into its behaviors and writes it to a Session Variable automatically.

DataAssist from WebAssist:  http://www.webassist.com/dreamweaver-extensions/dataassist/

However, I would urge you to spend a fair amount of time reading up on how to handle INNER JOINS in MySQL and how to design a complete database before throwing yourself into the code for this.  
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MySQL Init Waits 25 100
mysql left join sentence 7 39
MySQL left join performance 4 38
MYSQL database problem within Coldfusion 2016 environment 12 33
More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question