CODEWITHSUNDEEP
mysql
Mrinalini Pal
Mrinalini Pal

Reputation: 509

Database insertion in a table with foreign keys

I have the following tables-

  1. company(cid,comp_name) cid is pk
  2. role(rid,role_name) rid id pk
  3. user(uid,user_name,cid,rid) uid id pk, cid is fk,rid is fk

I want to enter values in user table in such a way that one user can be assigned multiple cid and rid and multiple users can be assigned same cid and/or rid. However if user table has a value where user_name='A', cid='1', rid='1'; same record cannot be inserted again, there has to be a change in either cid or rid.

I am writing this code which is not working:

CREATE TABLE IF NOT EXISTS `user` (
  `uid` INT AUTO_INCREMENT,
  `user_name` varchar(100) NOT NULL,
  `cid` INT NOT NULL,
  `rid` INT NOT NULL,
  PRIMARY KEY (`uid`),
  FOREIGN KEY (`cid`) REFERENCES company(`cid`),
  FOREIGN KEY (`rid`) REFERENCES role(`rid`)
)DEFAULT CHARACTER SET latin1
COLLATE latin1_general_cs;

Please help.

Upvotes: 0

Views: 27

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

The easiest solution here would be to create a separate, dedicated, bridge table which stores the relation between a user and his role at a given company:

userrolecompany (uid, cid, rid) PK is (uid, cid, rid))

This allows a given user to have multiple roles at multiple companies. Your user table then simplifies to the following:

user (uid, user_name) PK is uid

So, you would insert into the user table only once, when a user is added, but you may insert multiple company-roles into the userolecompany table. Doing this refactor to your database is a simple example of normalization, or at least a good step in that direction.

The CREATE TABLE statement for userrolecompany might look something like this:

CREATE TABLE userolecompany (
    uid INT NOT NULL,
    cid INT NOT NULL,
    pid INT NOT NULL,
    PRIMARY KEY (uid, cid, pid),
    FOREIGN KEY (uid) REFERENCES user (uid),
    FOREIGN KEY (cid) REFERENCES company (cid),
    FOREIGN KEY (rid) REFERENCES role (rid)
)

Upvotes: 2

Related Questions