thenna thenna - 4 months ago 13
SQL Question

how to make FOREIGN KEY Relationship in Two table

I have creating database name called montage. same database hosted server . i cant able to back up server database. so i am created locally . i have two table name called

1.TP_Users
2.TP_Roles


Following Table below Fields

TP_Users
--------
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 -------------->Foreign key this make Relationship in TP_Roles Id column
IsActive bit, not null
ClientId int, null


Another table TP_Roles following Fields like this

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


In server database i seen relationship table
i seen Like following

FK Name Parent tabl name column_id Refrenced table name column_id
FK__Users_cop__RoleI__27AED5D5 TP_Users RoleId 6 TP_Roles Id 1
FK__Users_cop__RoleI__28A2FA0E TP_Users RoleId 6 TP_Roles Id 1


I have See like Relationship server database above like this but when i create Local database same like above how can i create local database

Answer

You can add a foreign key constraint using the ALTER TABLE DDL statement:

ALTER TABLE TP_Users 
ADD CONSTRAINT FK_TP_Users_TP_Roles FOREIGN KEY (RoleId)     
    REFERENCES TP_Roles  (Id )     
    ON DELETE CASCADE -- optional   
    ON UPDATE CASCADE -- optional    
;    
Comments