Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL index name and foreign key name must be different for different tables?

MySQL index name and foreign key name must be different for different tables?

For example,

Two tables both have the same field(profile_id) which are belonging to a third table(profiles). So I want to make the profile_id indexed and constrain it as a foreign key.

Could the index name be named "profile_id_idx" in both tables?

And "profile_id_fk" as name of foreign key for both too?

like image 525
Hao Avatar asked Nov 12 '12 03:11

Hao


People also ask

Can two tables have the same index name?

The specified name must be unique within the table or view. For example, two tables can have an index named XPK_1, but the same table cannot have two indexes named XPK_1. You cannot create an index with the same name as an existing disabled index.

Can you have the same foreign key in multiple tables?

A declared foreign key (i.e., one enforced by the database engine) cannot tie to multiple other tables.

Can foreign key name be different?

A foreign key can also have different column names than the primary key. The foreign key and primary key can also have different default values. However, since values in the referenced table must be unique, default values are not much used and are rarely used for columns that are part of a primary key.

Should foreign keys be indexed MySQL?

MySQL requires that foreign key columns be indexed; if you create a table with a foreign key constraint but no index on a given column, an index is created.


1 Answers

Foreign Key names must be unique across all tables in all databases. Index names may be re-used in different tables.

like image 99
Gavin Towey Avatar answered Sep 29 '22 20:09

Gavin Towey