Solved

Error message for binding primary keys

Posted on 2010-11-20
18
1,207 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
 
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

760 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now