CODEWITHSUNDEEP
mysqlsql
AmanVirdi
AmanVirdi

Reputation: 1685

Is there any way to create foreign key relation without primary key in the table

I want to know, can a referential constraint be created between two tables, if the one is not having primary key.

For example:

   Table1
   {
        Column1(Unique Key)

        Column2(Not Null)

        Column3(Candidate key)
    }

    Table2
    {
         Column1(Primary Key)

         Column2(Foreign Key)   //Can it refer to any of the column in Table1

         Column3
    }

Upvotes: 0

Views: 1289

Answers (2)

Álvaro González
Álvaro González

Reputation: 146490

Can it refer to any of the column in Table1

I haven't tested it myself and I can't figure how it can work but, apparently, it can as long as you have an index (any kind of index :-!) where columns come first:

Foreign key definitions for InnoDB tables are subject to the following conditions:

  • InnoDB permits a foreign key to reference any index column or group of columns. However, in the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order.

  • InnoDB does not currently support foreign keys for tables with user-defined partitioning. This means that no user-partitioned InnoDB table may contain foreign key references or columns referenced by foreign keys.

  • InnoDB allows a foreign key constraint to reference a non-unique key. This is an InnoDB extension to standard SQL.

But even if the engine allows it, I wouldn't recommend to use a non-unique index (like Table1.Column2 in your example).

Upvotes: 1

Alexis_user
Alexis_user

Reputation: 425

Can it refer to any of the column in Table1

If you want to create a foreign key to another column which is not Primary key, it must be unique. (Unique Key on your column1)

But, what doesn't prevent you to do Column1 of Table1 as Primary key ?! It's already an Unique Key and Table1 doesn't have a primary key...

Upvotes: 1

Related Questions