CODEWITHSUNDEEP
sql-server
user3004443
user3004443

Reputation: 133

Foreign key contraints in a table on two columns referncing a same table

I have a table Item master, in which i have a primary key as item code

tbl_item_master

Itm_code int PK

Bill of material(tbl_Bom)

child_id int parent_id int

I have another table of bill of material(tbl_bom) in which there are 2 columns of parent and child which are having their values which is nothin but primary key of item master. I.e parent and child id in BOM are from Item Master's primary key. Follwoing is my script that I am using to apply foreign key on child and parent .

CREATE TABLE dbo.BOM_MASTER
(
    BOM_SrNo                     INT CONSTRAINT DF_BOM_MASTER_BOM_SrNo DEFAULT ((0)) NOT NULL,
    BOM_Key                      INT NOT NULL,
    PD_Key                       INT,
    BOM_Level                    INT,
    BOM_Parent_Code              VARCHAR (50),
    BOM_Child_Code               VARCHAR (50),
    BOM_UOM                      INT,
    BOM_Qty                      DECIMAL (14, 2),
    BOM_Final_Version            INT,
    BOM_ItemDimension_Applicable BIT,
    BOM_MatType                  INT CONSTRAINT DF__BOM_MASTE__BOM_M__192BAC54 DEFAULT ((0)) NOT NULL,
    CONSTRAINT FK_BOM_MASTER_Project_Details_Master FOREIGN KEY (PD_Key) REFERENCES dbo.Project_Details_Master (PD_Key),
    CONSTRAINT FK_BOM_MASTER_tbl_itm_master FOREIGN KEY (BOM_Parent_Code) REFERENCES tbl_itm_master (BSL_COST_ITEM_Item_SAP_Code),
    CONSTRAINT FK_BOM_MASTER_tbl_itm_master FOREIGN KEY (BOM_Child_Code) REFERENCES tbl_itm_master (BSL_COST_ITEM_Item_SAP_Code)
)
GO

and I am getting the following error

Cannot create two constraints named 'FK_BOM_MASTER_tbl_itm_master'. Duplicate constraint names are not allowed. Severity 16

Upvotes: 1

Views: 275

Answers (1)

smdrager
smdrager

Reputation: 7417

If you add newlines, the issue becomes apparent.

CREATE TABLE dbo.BOM_MASTER
(
    BOM_SrNo INT CONSTRAINT DF_BOM_MASTER_BOM_SrNo DEFAULT ((0)) NOT NULL, 
    BOM_Key INT NOT NULL, 
    PD_Key INT, 
    BOM_Level INT, 
    BOM_Parent_Code VARCHAR (50), 
    BOM_Child_Code VARCHAR (50), 
    BOM_UOM INT, 
    BOM_Qty DECIMAL (14, 2), 
    BOM_Final_Version INT, 
    BOM_ItemDimension_Applicable BIT,
    BOM_MatType INT CONSTRAINT DF__BOM_MASTE__BOM_M__192BAC54 DEFAULT ((0)) NOT NULL, 
    CONSTRAINT FK_BOM_MASTER_Project_Details_Master FOREIGN KEY (PD_Key) REFERENCES dbo.Project_Details_Master (PD_Key), 
    CONSTRAINT FK_BOM_MASTER_tbl_itm_master FOREIGN KEY (BOM_Parent_Code) REFERENCES tbl_itm_master (BSL_COST_ITEM_Item_SAP_Code), 
    CONSTRAINT FK_BOM_MASTER_tbl_itm_master FOREIGN KEY (BOM_Child_Code) REFERENCES tbl_itm_master (BSL_COST_ITEM_Item_SAP_Code)
)
GO

The last 2 are named identically. It is also clear there are other naming issues. What is DF__BOM_MASTE__BOM_M__192BAC54? Will future users of the table be able to gleem anything from this naming?

Upvotes: 1

Related Questions