Is there a limit / criteria in creating Foreign key in MySQL

[newcodder]

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.

[smendoza]

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"

[newcodder]

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.

[techgnome]

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

[newcodder]

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.

[smendoza]

nm I read the question wrong and I can't delete my pointless post.

[techgnome]

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.

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

[newcodder]

OK. I got it. Thank you.

[newcodder]

By the way, you know what, I don't have Pkey and Fkey separately for the other two tables. I mean I have both but not separate. Let me use your example to explain it,

I have...

Customer table
----------------
ID
Name
TypeID I don't have this and I don't know what it is...



and I have...


Address table
---------------
ID - I set this as Pkey and Fkey. May be what I did is wrong
CustomerID I don't have this
AddressLine1
AddressLine2
City
State
ZipCode
TypeID I don't have this and I don't know what it is...


Phone table
---------------
ID - I set this as Pkey and Fkey. May be what I did is wrong
CustomerID I don't have this
TypeID I don't have this and I don't know what it is...
PhoneNumber


Did I do something stupid?

Anyway, I will change my tables as yours. I will create ID as Pkey for all my tables and another column that represent Fkey.

[techgnome]

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