-
Sep 8th, 2014, 04:33 PM
#1
Thread Starter
Member
Is there a limit / criteria in creating Foreign key in MySQL
Hi,
I was trying to create foreign key. I have three tables T1, T2 and T3. ID of T1 is set as Primary Key, Non Null and Auto Increment. I want to make a relation between T1 and T2, T1 and T3. Whenever a new ID is created in T1 this has to be added in T2 and T3 (I think this is what the purpose of foreign key, I am just a beginner and don't know much). So I go to Alter Table and added the foreign key for T2 (I chose T1 as referenced table and T1's ID as referenced column) it was successfully created. I try to create the foreign key for T3, I chose T1 as referenced table and T1's ID as referenced column but I got an error as follows.
ERROR 1022: Can't write; duplicate key in table '#sql-894_1c'
SQL Statement:
ALTER TABLE `mydb`.`T3`
ADD CONSTRAINT `IDs`
FOREIGN KEY (`ID`)
REFERENCES `mydb`.`T1` (`ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION
ERROR: Error when running failback script. Details follow.
ERROR 1050: Table 'T3' already exists
SQL Statement:
CREATE TABLE `T3` (
`Designation` varchar(25) DEFAULT NULL,
`Department` varchar(25) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
Then when I deleted the foreign key that was created for T2 and try again for T3 there was no errors and created successfully. Then I try creating for T2 I got the same error again. Can I create foreign key only for one table? Can I not refer to the T1's ID for T2 and T3?
Can you tell me what could possibly the reason for this error?
Thanks in advance.
-
Sep 8th, 2014, 07:17 PM
#2
Addicted Member
Re: Is there a limit / criteria in creating Foreign key in MySQL
If I recall it's because you have a constraint in your database already called "IDS" try changing the name of the constraints name to something on than "IDS"
Last edited by smendoza; Sep 8th, 2014 at 07:17 PM.
Reason: type o
-
Sep 10th, 2014, 09:54 AM
#3
Thread Starter
Member
Re: Is there a limit / criteria in creating Foreign key in MySQL
Actually, I don't have a foreign key in table T3. But it worked when I tried with another name as you said. But how come it, because I could create a foreign key name as ID in T3 if I delete the foreign key I have created in T2.
Note : The name of the foreign key created in T2 is ID.
-
Sep 10th, 2014, 12:14 PM
#4
Re: Is there a limit / criteria in creating Foreign key in MySQL
It's your naming ... the names of the constraints/fkeys need to be unique across the database. So when you created the fkey/constraint on T2, you probably gave it the name ID/IDs ... so when you tried to create the fkey using the SAME name in on T3 ... it fails. It worked when you then removed the foreign key from T2, right? that's because the name no longer exists... but then you created it on T3, so when you then try to create it again on T2, it fails. To help avoid this issue, we use a somewhat common naming convention: "FK_childTable_parentTable_childField" so for T2 it would be FK_T2_T3_ID ... for T3 it would be FK_T3_T1_ID ... actually it is a bit more than that because the Fkey field usually gets meaningful name. It might be ID in T1... but odds are T2 & T3 are also going to have their own ID field... so instead in T2 and T3, it would be T1ID as the field name.... making the Fkey names FK_T2_T1_T1ID and FK_T3_T1_T1ID
To put it into a bit more real-world terms and something less cryptic and confusing that T1/T2 ...
Let's say I have a Customer table, Address table and a Phone table...
Each table has an ID field. Both the Address and Phone tables have a Fkey to the Custom table, the field is CustomerID
Customer table
----------------
ID
Name
TypeID
Address table
---------------
ID
CustomerID
AddressLine1
AddressLine2
City
State
ZipCode
TypeID
Phone table
---------------
ID
CustomerID
TypeID
PhoneNumber
results in the following FKeys:
FK_Address_Customer_CustomerID
FK_Phone_Customer_CustomerID
FK_Customer_Type_TypeID
FK_Address_Type_TypeID
FK_Phone_Type_TypeID
Even though CustomerID and TypeID are fairly obvious, we include them because sometimes we'll have multiple fields refer back to the same table.
-tg
-
Sep 12th, 2014, 10:36 AM
#5
Thread Starter
Member
Re: Is there a limit / criteria in creating Foreign key in MySQL
Originally Posted by techgnome
so when you tried to create the fkey using the SAME name in on T3 ... it fails. It worked when you then removed the foreign key from T2, right?
Yes, you are right. So what I understand now is that foreign key name can't be same across the table, is that right?
In all your three tables, there are ID, I think it is the primary key of those tables and is auto increment.
and the CustomerID in the Address table and and Phone table is the foreign key pointing to primary key (ID) in the Customer table.
Now I have one question about foreign key. When you create a new customer in the Customer Table using a VB.NET form, does this new customer ID reflect / created automatically in the Address table and Phone table? Or should we make a new query to add this new Customer ID to these two tables as foreign keys?
I am just a beginner, so please bear with me.
Thank you.
-
Sep 12th, 2014, 10:44 AM
#6
Addicted Member
Re: Is there a limit / criteria in creating Foreign key in MySQL
nm I read the question wrong and I can't delete my pointless post.
-
Sep 12th, 2014, 11:16 AM
#7
Re: Is there a limit / criteria in creating Foreign key in MySQL
Originally Posted by newcodder
Yes, you are right. So what I understand now is that foreign key name can't be same across the table, is that right?
They have to be unique in the DATABASE ...
so I think the answer is yes, that's right.
Originally Posted by newcodder
In all your three tables, there are ID, I think it is the primary key of those tables and is auto increment.
and the CustomerID in the Address table and and Phone table is the foreign key pointing to primary key (ID) in the Customer table.
Now I have one question about foreign key. When you create a new customer in the Customer Table using a VB.NET form, does this new customer ID reflect / created automatically in the Address table and Phone table? Or should we make a new query to add this new Customer ID to these two tables as foreign keys?
I am just a beginner, so please bear with me.
Thank you.
Yes, all three IDs are autonumbers. and the ID field is the PKey for THAT table. And yes, CustomerID is then an FKey that points to the ID of the Customer record in the Customer table.
When I insert into Customer table, I select back the ID (how exactly that's done depends on the database being used, but there is a post on that in the Database FAQ & Tutorial section in the Database forums here) I then use that ID as the CustomerID when I insert into Address and Phone. It doesn't do it automatically because how can it? How would it know what customer ID to put in? What if I don't put in an address, but I do a Phone number? So it's still up to to you to put the value in to the field. What the FKey is going to do is prevent you from entereing 11 as the CustomerID, if there is no Customer with ID 11... it's also going to (depending on how you set it up) delete the Address & Phone records when you delete the parent Customer record. But when you're inserting an address record, it doens't know if the CustomerID should be 1, 5, 10, or 457. YOU have to provide that.
-tg
-
Sep 12th, 2014, 11:35 AM
#8
Thread Starter
Member
-
Sep 13th, 2014, 12:43 PM
#9
Thread Starter
Member
-
Sep 13th, 2014, 09:15 PM
#10
Re: Is there a limit / criteria in creating Foreign key in MySQL
That's probably part of your problem - using ID as both the PKey and the FKey ... what if the Customer has more than one address? (that's whwre the TypeID comes in - indicates what the address type is - Home, Work, Vacation, Mailing, Billing, etc) ... If you're only to have one and only one address per customer, then why bother with a table for it? Same with the Phone.
-tg
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|