How to Refer Foreign Key table to refer another table?

Question

I have Two Table Two Table Name called Following on this

1. TP_Users
2. TP_Roles

TP_Users Have Following Fields

Id (PK, int, not null)
UserName (nvarchar(50), null)
UserEmail (nvarchar(50), null)
DisplayName (nvarchar(50), null)
Password (nvarchar(50), null)
RoleId (FK, int, not null)
IsActive (bit, not null) ------>Foreign Key Reference 
ClientId (int, null)

TP_Roles have Following Fields

Id  PK, int, not null
Role_Name   varchar(200), null
IsActive    bit, null

TP_Users Parent table column name RoleId its Refrenced table TP_Roles column name id.
How to make relationships and how the link two tables using query ?

Refer http://stackoverflow.com/questions/10028214/add-foreign-key-to-existing-table — rahulbhondave, Jul 04 '16 at 10:27
@cralfaro its not necessary Fk key? please explain little bit more — thenna, Jul 04 '16 at 10:52
@thenna I added a response with a possible example of what you need on MySql db engine, i did the example with not all yours fields, add them please — cralfaro, Jul 04 '16 at 11:02

score 1 · Accepted Answer · answered Jul 04 '16 at 11:06

You can add forieng key constraint on your users table as follows

create table TP_Users
(
....., --- column list here
constraint FK_TP_Users_RoleId foreign key(RoleId) references TP_Roles(Id)
)

Then by using a simple inner join on related fields, you can build your query

select * 
from TP_Users
inner join TP_Roles on TP_Roles.Id = TP_Users.RoleId

score 0 · Answer 2 · answered Jul 04 '16 at 11:01

Hi here is an example for the MySQL database language, how to create both tables and a simple query to join them.

CREATE TABLE TP_USERS (
  ID int(11) NOT NULL,
  USER_NAME varchar(50),
  ROL_ID int(11) NOT NULL,
  PRIMARY KEY (ID),
  CONSTRAINT `FK_roles` FOREIGN KEY (`ROL_ID`) REFERENCES `TP_ROLES` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



CREATE TABLE TP_ROLES (
   ID int(11) NOT NULL,
   ROL_NAME varchar(50),
   IS_ACTIVE tinyint(1) NOT NULL DEFAULT '1',
   PRIMARY KEY (ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

And here a simple query where you can join both tables:

select u.USER_NAME, r.ROL_NAME from TP_USERS u, TP_ROLES r where u.ROL_ID=r.ID

How to Refer Foreign Key table to refer another table?

2 Answers2