-8

I want to create a Master Table and there are other 2-3 tables which will have its relation on Master table. Please see the Details below:-

  • Master table Name: tbl_org

There are more 2-3 tables which will have relation to this Master table. Please suggest how to proceed as I have never been created a Master table and given its relation.

Also, Please suggest what Datatype should I give to IsActive field.

2

Step 1:

---- Create master table 
CREATE TABLE [dbo].[tbl_Master]
   (
    [MasterID] [int] NOT NULL,
    [Value] [varchar](200) NULL
    )

Step 2:

---- Create PRIMARY KEY on column for having unique values 
ALTER TABLE [dbo].[tbl_Master] ADD  CONSTRAINT [PK_tbl_Master] PRIMARY KEY CLUSTERED 
(
    [MasterID] ASC
)

step 3:

-- create chlid table 
CREATE TABLE [dbo].[tbl_Child](
    [ChlidId] [int] NULL,
    [MasterID] [int] NULL,
    [Details] [varchar](200) NULL
) ON [PRIMARY]

step 4:

--- create relationship between chlid and master using FOREIGN KEY
ALTER TABLE [dbo].[tbl_Child]  WITH CHECK ADD  CONSTRAINT [FK_tbl_Child_tbl_Master] FOREIGN KEY([MasterID])
REFERENCES [dbo].[tbl_Master] ([MasterID])
GO

ALTER TABLE [dbo].[tbl_Child] CHECK CONSTRAINT [FK_tbl_Child_tbl_Master]
GO

Relationship diagram: enter image description here

Note: The same way you can add many child tables .

More info on the relationship types:http://msdn.microsoft.com/en-us/library/s780ea06(v=vs.71).aspx

Not the answer you're looking for? Browse other questions tagged or ask your own question.