8

I have a database A and database B. In database A i set company_id as Primary key. In database B i have to set same company_id as a reference. Is it possible to set B.company_id as foreign key? If yes means how to set this B.company_id as foreign key?

Juned Ahsan
  • 67,789
  • 12
  • 98
  • 136
Cody
  • 83
  • 1
  • 5

3 Answers3

7

Any relationship between/among tables are confined within a schema. You cannot define foreign key constrain between two unrelated tables in two different schema. If you have a real need o do it then you need to re-think about your database design.

Juned Ahsan
  • 67,789
  • 12
  • 98
  • 136
  • Then how to relate these two fields in two different schema. – Cody Aug 14 '13 at 05:41
  • @Zapp there is no way you can define mappings between two tables in two different shemas. As i mentioned if two tables are related, bring them in one schema and work accordingly. – Juned Ahsan Aug 14 '13 at 05:44
  • i am in a situation where tow applications share the few tables. like user table. my client want to have the shared users between two applications. i am done sharing the users. **But the problem is foreign key of user. table to many another tables of application** if i use the separate user table then. i have to upload all users. many another issue may rise. – Nitin Sidhu Apr 18 '20 at 14:06
  • 1
    @JunedAhsan Hi, are we sure foreign key between two different schemas is a design issue? It could totally happen that 1 is a master table in a schema that many other applications need in separate schemas but they all sit on 1 database server. In order to not replicate the master data into each application DB, we connect it to the original source, the parent. Not sure if my though process is correct but this situation seems to be quite plausible for me. – AyB Feb 10 '21 at 12:59
  • @AyB I am not exactly sure about this in your comment "In order to not replicate the master data into each application DB, we connect it to the original source, the parent". Are you doing that in your application code or at the database level? – Juned Ahsan Feb 10 '21 at 22:43
  • @JunedAhsan It's on database level, I mentioned application for you to understand the use case better. For a better example, imagine the users data exists on the master database. We have multiple applications where different activities are associated with these users. Now would it be better to replicate these users into each application's DB or create foreign keys to the master database for the user id columns in each application's DB... – AyB Feb 11 '21 at 08:02
  • @AyB When i wrote this answer, i had a notion that there is no way to create foreign keys in different schemas. As databases allow this, i believe it is not a bad idea to separate the common data in one schema and create references/constrains in the dependent schemas. – Juned Ahsan Feb 11 '21 at 11:11
  • 2
    what about microservices? ex- `A` table which belongs to `component A` needs to access `B` table which belongs to `component B`. there are two separate databases for two services – Nafaz M N M May 25 '21 at 06:29
  • @NafazBenzema Microservices should be loosely coupled so their databases should be i.e. using their own DBs. If they are using the same database then you can always create the relationships within one schema. Always remember loosely couple components designs are less complicated and easier to manage in the long run. – Juned Ahsan May 25 '21 at 22:48
  • I am not sure the last statement holds true. This design is not uncommon at all for microservices. On the contrary, using the same DB server with separate schemas (databases) is what we often want. – msTam Jun 22 '21 at 18:59
  • Should this answer be deleted? It's false. See Jim's answer. – aswine Sep 30 '22 at 14:01
2

Please refer to this nearly duplicate item. It is possible to have foreign keys between different schemas (sometimes called "databases") on the same MySQL database server. MySQL InnoDB foreign key between different databases

Jim
  • 2,672
  • 1
  • 21
  • 14
1

Assuming Table2 belongs to schema B, and Table1 belongs to schema A and both have company_id

ALTER TABLE B.Table2
ADD foreign key B_company_id(company_id)
REFERENCES A.Table1(company_id)
Kisanagaram
  • 289
  • 2
  • 10