Primary And Foreign key mapping between view and table

Question

I have created 2 table in 2 different databases. First database name is user which contains userDetails table, which have id as a primary key and user_name, and my second database is customer which have 1 table called as customerDetails, which have 1 id as a primary key and customer name and one view of above user table which contains id of that user table and name.

So what i want to do is, creating a foreign key of that view in customerDetails table, so that i can access user table from customer database through view. I don't know how to achieve this, as i am new to database concepts please anyone can get me out of this.

Whole scenario is as follow,

> Database Name : user 
> Table Name    : userDetails 
> Fields        : id   userName
> 
> Database Name : customer 
> View Name     : user_view
> Fields        : id  userName
> 
> Database Name : customer 
> View Name     : customerDetails 
> Fields        : id     custName

i want in last table that is in customerDetails last column as a foreign key from view. How can i achieve this?

`FOREIGN KEYS` are used for restrictions on what you can insert in a table column. What I think you're looking for is a `JOIN`, that is used to get data from two tables together linked by a column. — Fredster, Jul 15 '16 at 09:22
`Views` are pre-prepared `SELECT` statments that don't interfere with `FOREIGN KEYS`. — Fredster, Jul 15 '16 at 09:24
Yes i want data from userDetails table, but which is present in another database called as user, so to access it in the customer database i am creating view of that userDetails Table here, how can i use id of userDetails table as a foreign key in my customerDetails table? — Tushar Deshpande, Jul 15 '16 at 09:35
I don't think you can create a `foreign key` with a `view`. But if you could, just create a `view` with `CREATE VIEW userDetails_in_customer_databse AS SELECT * FROM user.userDetails`. This may help you: http://stackoverflow.com/questions/4452132/add-foreign-key-relationship-between-two-databases — Fredster, Jul 15 '16 at 09:46

score 1 · Accepted Answer · edited May 23 '17 at 11:44

Views are not related to foreign keys as much as getting to your data as mentioned in comments by your peers. The below uses a Junction Table to intersect users and companies, enforcing a Foreign Key constraint between databases (not a bad idea for shared info between databases).

The Junction Table is many-to-many, and hooks users and companies together.

Schema:

create schema userDB;
create table userDB.userDetails
(   id int auto_increment primary key,
    userName varchar(100) not null
);

create schema customerDB;
create table customerDB.customerDetails
(   id int auto_increment primary key,
    custName varchar(100) not null
);

create table customerDB.userCustomerJunction
(   -- a many-to-many mapping
    id int auto_increment primary key,
    userId int not null,
    custId int not null,
    unique key (userId,custId), -- no dupes allowed
    foreign key `ucj_2_user` (userId) references userDB.userDetails(id),
    foreign key `ucj_2_cust` (custId) references customerDb.customerDetails(id)
);

Test it:

insert customerDB.customerDetails(custName) values ('Exxon Mobil'); -- id 1
insert customerDB.userCustomerJunction(userId,custId) values (1,7); -- FK Failure
-- above line generates an error 1452 as expected
insert userDB.userDetails(userName) values ('Kelly'); -- id 1
insert customerDB.userCustomerJunction(userId,custId) values (1,1); -- success, FK's satisfied

Remember that the user and company are separate entities and to interface the two would require something that ties them together. A Junction table is a fantastic place to put a column such as effectiveRights or something. It would denote what the user can do, such as insert, update, delete, view, blacklist, etc.

Creating a view between user and company is simply like any join, but in this case it would be between databases with the whichDB. in front of the table name. The view is materialized and manifested in the physical tables. So as the physical rules, the physical has the FK's in force (data integrity). And the addition of an effectiveRights column will assist you in determining what each user and company can do together: such as, yes, this user has certain rights to this company info, etc. With a rights bitmark, or separate columns for rights, all in the Junction table. For an example of Junction tables, see this Answer of mine.

Primary And Foreign key mapping between view and table

1 Answers1