我正在编写一个存储过程以将数据插入表中.如何根据另一个ID插入具有唯一ID的数据来写入条件 [英] Iam writing a stored procedure to insert data into table.how can we write condition oninserting data with unique id depending on another id

问题描述

我有2个桌子.
一个是masterdatamodel，一个是masterdataattr

在masterdatamdoel中，我具有masterdatamodelid和mastertype.
masterdataattr我有一个字段，如masterdatamodelid，mastertype，attorder，attname，atttype

我应该为masterdatamodelid插入新数据.
假设masterdatamodelid数据为1
masterdataattr数据ismastermodelid = 1 attorder = 1 attname ="test" atttype ="test123"
attorder = 2 attname ="test2" atttype ="test1234"
mastermodelid = 2 attorder = 1 attname ="test" attype ="test123"
attorder = 2 attname ="test" attype ="test1234"

所以我必须给这里条件.

I have 2 have tables.
one is masterdatamodel and one is masterdataattr

in masterdatamdoel i have masterdatamodelid and mastertype.
masterdataattr i have field like masterdatamodelid,mastertype,attorder,attname,atttype

i should insert new data for masterdatamodelid.
suppose for masterdatamodelid data is 1
masterdataattr data ismastermodelid =1 attorder=1 attname="test" atttype="test123"
attorder=2 attname="test2" atttype="test1234"
mastermodelid=2 attorder=1 attname="test" attype="test123"
attorder=2 attname="test" attype="test1234"

so i have to give here condition.

ALTER PROCEDURE CORE.SP_InsertMasterModelATTR 
	(
	   @MasterModelId int
	  ,@AttributeOrder int
      ,@AttributeName char(50)
      ,@AttributeType char(10)
      ,@MasterType    char(50)
      ,@Required      char(1)
      ,@ValueUsedAsKey char(1)
      ,@DefaultValue   char(100)
	)
AS
BEGIN
	
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	Insert INTO CORE.MasterModelAttr 
	(
	   AttributeOrder 
      ,AttributeName 
      ,AttributeType 
      ,MasterType    
      ,Required      
      ,ValueUsedAsKey 
      ,DefaultValue   
	)
	values(
	   @AttributeOrder 
      ,@AttributeName 
      ,@AttributeType 
      ,@MasterType    
      ,@Required      
      ,@ValueUsedAsKey 
      ,@DefaultValue )

我正在尝试写条件不起作用.有人可以帮忙吗.

Iam trying to write condition its not working.can anyone please help on this.

推荐答案

AttributeOrder可能已设置为MasterModelAttr表中的主键，问题.
您的问题是您没有将MasterModelAttr表中的记录与特定的MasterModelID相关联.
我将创建一个自动递增的IDENTITY以用作MasterModelAttr表中的主键.
然后为MasterModelID创建一个新的外键列.
然后创建一个包含AttributeOrder和MasterModelID组合的唯一约束，以便尊重唯一性.

创建多列唯一约束

AttributeOrder is likely set up as your primary key in the MasterModelAttr table and that will be a problem.
Your issue is that you don''t associate your records in the MasterModelAttr table with a specific MasterModelID.
I would create an auto-incrementing IDENTITY to act as the primary key in the MasterModelAttr table.
Then create a new foreign key column for MasterModelID.
Then create a unique constraint containing a combination of AttributeOrder and MasterModelID so that uniqueness is respected.

Create Multi Column Unique Constraint

CREATE TABLE T
{
   Name varchar(10) ,
   Location varchar(10) ,
         CONSTRAINT U_NameLocation UNIQUE (Name,Location) 
}

这篇关于我正在编写一个存储过程以将数据插入表中.如何根据另一个ID插入具有唯一ID的数据来写入条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！