sql database query

Question

Two tables are there

CREATE TABLE [dbo].[Status_Master_Table]

(
[StatusId] INT IDENTITY (1, 1) NOT NULL,

[Status_Description] NVARCHAR (50) NOT NULL,

[Remarks] VARCHAR (50) NOT NULL,

PRIMARY KEY CLUSTERED ([StatusId] ASC)
);

second table is

CREATE TABLE [dbo].[Lab_Master_Table]

(
[Lab_Id] INT IDENTITY (1, 1) NOT NULL,

[Lab_Name] NVARCHAR (50) NOT NULL,

[Status] NVARCHAR (50) NOT NULL,

PRIMARY KEY CLUSTERED ([Lab_Id] ASC),

UNIQUE NONCLUSTERED ([Lab_Name] ASC)

);

what is the syntax to set[StatusId] of first table as a foreign key to second table

Midhun T P · Answer

Hi You can use below query - CREATE TABLE [dbo].[Lab_Master_Table] ( [Lab_Id] INT IDENTITY(11) NOT NULL  [Lab_Name]NVARCHAR(50) NOT NULL  [Status]NVARCHAR(50) NOT NULL  [Status_id] INT references Status_Master_Table(StatusId) PRIMARY KEY CLUSTERED([Lab_Id] ASC ) UNIQUE NONCLUSTERED([Lab_Name] ASC ) );

Bikesh Srivastava · Answer

Hi Kunal Firstly you need to understand these things:- 1=>what is foreginkey 2=>why we use this. 3=>where we can use 4=>how to use refer this link:- https://msdn.microsoft.com/en-in/library/ms189049.aspx

Nitin Sontakke · Answer

Your second table should have StatusId column and NOT the [Status] nvarchar(50) column. You can define StatusId column as following in second table: [StatusId] [Integer] not null constraint [FK_LabMaster_StatusId_StatusMaster_StatusId] foreign key references [dbo].[Status_Master_Table] ([StatusId]) To get the description of the status you will need to join two tables as follows: Select * from [dbo].[Lab_Master_Table] lm left outer join [dbo].[Status_Master_Table] sm on lm.StatusId = sm.StatusId Please replace * with appropriate columns list.