Foreign key in MySQL

Posted on

Question :

I am learning about database, I need to know something about the foreign key in MySQL.

Consider the following two tables.

  1. UserGroupType.
  2. UserGroup.

UserGroupType:

enter image description here

UserGroup:

enter image description here

In this table the foreign key is defined as follows,

foreign key (GroupType_id) references UserGroupType(GroupType_id)

I just want to know whether the name of the field in UserGroup can be changed? consider the following altered statement.

GroupType:
enter image description here

foreign key (Type_id) references UserGroupType(GroupType_id)

Is it necessary that both the field names must be same?

How to set the default value for datetime?

Thanks in advance, sorry if it is very basic things.

Answer :

foreign key would have a different name from the primary key it is referring to, but necessarily , their data type and other attributes should be the same.
for example , if one is INT(10) unsigned the other should be defined the same.

but sometimes it is a good practice to name them similarly in complex queries. for example when you want to JOIN the relating tables, you can use the syntax :

userGroupType INNER JOIN userGroup USING (GroupType_id)

instead of :

userGroupType INNER JOIN userGroup ON userGroupType.GroupType_id = userGroup.GroupType_id

Leave a Reply

Your email address will not be published. Required fields are marked *