thenna thenna - 5 months ago 11
SQL Question

How to Refer Foreign Key table to refer another table?

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 ?

Answer

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