question

Mehboob Ali avatar image
Mehboob Ali asked

Problem in Realtionship among tables in Sql Server2005

Good morning to all of you...well i ve some problem in implementing relationship among two tables name mainHeads and SubHead.

                    
MainHeads([No],CatID,HID,HeadName) // No is P.K identity                    
SubHeads([No],CatID,HID,SHID,SubHeadName)  // here No is also P.K identity                    

Now i want to Map HID from MainHeads to SubHeads table(1:M) but it yield me the following error.

'The Columns in table SubHeads do not match an existing Primary Key or UNIQUE constraint'

can anyone help me in this regard plz

sql-server-2005foreign-key
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Madhivanan avatar image
Madhivanan answered

It should be something like this

    create table MainHeads([No] int identity(1,1) primary key,CatID int,HID int ,            
        HeadName varchar(100))             
            
    create table SubHeads([No] int references MainHeads([No]),CatID int,HID int ,            
        SHID int ,SubHeadName varchar(100))            
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kristen avatar image
Kristen answered

To set up a Foreign Key for SubHeads.HID to reference MainHeads.HID then either HID must be the Primary key in MainHeads (it isn't, you are using [No] instead) OR you must have a Unique Index on it - like this:

            
CREATE TABLE MainHeads            
(            
    [No] int identity(1,1) ,            
    CatID int,            
    HID int ,            
    HeadName varchar(100),            
    PRIMARY KEY            
    (            
    	[No]            
    )            
)             
            
CREATE UNIQUE INDEX IX_MyIndex            
ON  MainHeads            
(            
    HID            
)            
            
CREATE TABLE SubHeads            
(            
    [No] int REFERENCES MainHeads([HID]),            
    CatID int,            
    HID int ,            
    SHID int ,            
    SubHeadName varchar(100),            
    PRIMARY KEY            
    (            
    	[No]            
    )            
)            
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.