Tushar Digambar Deshpande Tushar Digambar Deshpande - 4 months ago 15
SQL Question

Primary And Foreign key mapping between view and table

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?

Answer

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.