locked
Union foreign key RRS feed

  • Question

  • I have table that has two columns and that two columns connect to some table as foreign key.

    (Three tables as primary and one as foreign key)

    How can i connect them as foreign key(I used foreign key but that doesn't work as my way)

    Sounds like union but we don't have union in foreign key

    Friday, December 18, 2015 6:13 PM

Answers

  • I wrote query like this as my trigger (of course for delete ) then remove foreign keys

    Then i could solve my problem

    I hope this query help someone else

    CREATE TRIGGER [dbr].[FK_Delete_Cascade_Import] ON [dbr].[DbrImports]
    FOR DELETE
    AS
        --  SET NOCOUNT ON;
         BEGIN
             DECLARE @EntityTypeID INT, @EntityId BIGINT, @INSERTEDCOUNT INT, @DELETEDCOUNT INT, @Table NVARCHAR(MAX), @Query NVARCHAR(MAX), @TableName NVARCHAR(MAX), @Schema NVARCHAR(10);
             SELECT @EntityTypeID = D.Entitytype_Id,
                    @EntityId = D.Id
                    FROM Deleted D;
             SELECT @Tablename = OBJECT_SCHEMA_NAME(Parent_Id)+'.'+OBJECT_NAME(Parent_Id)
                    FROM Sys.Triggers
                    WHERE object_id = @@ProcId;
             IF EXISTS
             (
              SELECT *
                     FROM Dbr.Dbrsends Ds
                     WHERE Ds.Entitytype_Id = @EntityTypeID
                           AND Ds.Entity_Id = @EntityId
             )
                 BEGIN
                     SELECT @INSERTEDCOUNT = COUNT(*)
                            FROM Inserted I;
                     SELECT @DELETEDCOUNT = COUNT(*)
                            FROM Deleted D;
                     IF(@DELETEDCOUNT = 1
                        AND @INSERTEDCOUNT = 0)
                         BEGIN
                             RAISERROR(15600, -1, -1, 'Data is used');
    					ROLLBACK TRANSACTION
                             RETURN;
                         END;
                 END;
             ELSE
                 BEGIN
    
                     
    
                     DECLARE Product_Cursor CURSOR
                     FOR SELECT DISTINCT
                                (C.Table_Name),
                                C.Table_Schema
                                FROM Information_Schema.Columns C
                                     INNER JOIN Information_Schema.Tables T ON T.Table_Name = C.Table_Name
                                WHERE Table_Type = 'BASE TABLE'
                                      AND Column_Name LIKE N'%Entity_Id%';
                    
                     OPEN Product_Cursor;
                     FETCH NEXT FROM Product_Cursor INTO @Table, @Schema;
                     IF @@Fetch_Status <> 0
                         BEGIN
                             PRINT '         <<None>>';
                         END;
                     WHILE @@Fetch_Status = 0
                         BEGIN
                             SET @Table = @Schema+'.'+@Table;
                             SET @Query = 'IF  EXISTS (select id from '+@Table+' where EntityType_Id='+CAST(@EntityTypeID AS NVARCHAR(20))+' and Entity_Id='+CAST(@EntityId AS NVARCHAR(20))+')
         DELETE FROM '+@Table+' where EntityType_Id='+CAST(@EntityTypeID AS NVARCHAR(20))+' and Entity_Id='+CAST(@EntityId AS NVARCHAR(20));
       
                            
                             EXEC (@Query);
                             FETCH NEXT FROM Product_Cursor INTO @Table, @Schema;
                         END;
                     CLOSE Product_Cursor;
                     DEALLOCATE Product_Cursor;
                     SET @Query = ' DELETE FROM '+@TableName+' where Id='+CAST(@EntityId AS NVARCHAR(20));
                     
                     EXEC (@Query);
                 END;
         END;


    Thursday, December 24, 2015 10:08 AM

All replies

  • It is difficult to understand your description.  It would help to see a short example of your tables and a few rows to illustrate your design.
    Friday, December 18, 2015 6:59 PM
  • Think i have three tables(users,Roles,User_Roles)

    In users table, I have 2 Primary keys that name is  (Id,E_Id)

    in another tables I have two column that name is (Id,User_Id)

    Id connect to Id in users table and User_Id to E_Id

    So I have two FK but with two columns and record in table users is different with table user_Roles.

    When i want to insert in users table i have problem because my record is in roles table but that is not in user_roles table

    How can i make it possible

    Friday, December 18, 2015 7:33 PM
  • It appears that you are using terminology incorrectly.  So to avoid confusion, please post DDL in the future.  A table cannot have 2 primary keys - only 1 is allowed.  But perhaps you meant that the primary key is composed of 2 columns - which we call a compound primary key. In addition, you define 3 specific tables in your first sentence but you refer to "in another tables" - please be specific about which table you are referring to.  We don't know your schema, we can't see your screen, we don't work in your company - help yourself by being as specific as you can.

    As best I can tell, the problem is with your user_roles table.  Below is an example of one approach to such a schema.

    set nocount on;
    if object_id('user_roles') is not null drop table user_roles; 
    if object_id('users') is not null drop table users; 
    if object_id('roles') is not null drop table roles;
    go
    
    create table users (userid int identity(1,1) not null constraint pku primary key, username varchar (20) not null);
    create table roles (roleid int identity(1,1) not null constraint pkr primary key, rolename varchar (20) not null); 
    go
    create table user_roles (userid int not null, roleid int not null, expiration_date date null,
    constraint pkx primary key clustered (userid, roleid), 
    constraint fkx foreign key (userid) references users(userid), 
    constraint fky foreign key (roleid) references roles(roleid)
    );
    go
    insert users (username) values ('banana');
    insert roles (rolename) values ('fruit');
    insert roles (rolename) values ('vegetable');
    select * from users;
    select * from roles;
    insert user_roles (userid, roleid, expiration_date) values (1, 1, '20151231'); 
    insert user_roles (userid, roleid, expiration_date) values (1, 3, '20151231'); 
    select * from user_roles;
    go
     

    Friday, December 18, 2015 8:11 PM
  • It is possible to have two pk

    My Table distinct with e_id

    Think like join

    Select * from users u inner join roles r on u.id=r.user_id and u.e_id=r.e_id

    Union

    .....

    So I think Microsoft does not support something like it

    As I saw sp in system

    Friday, December 18, 2015 9:11 PM
  • I wrote query like this as my trigger (of course for delete ) then remove foreign keys

    Then i could solve my problem

    I hope this query help someone else

    CREATE TRIGGER [dbr].[FK_Delete_Cascade_Import] ON [dbr].[DbrImports]
    FOR DELETE
    AS
        --  SET NOCOUNT ON;
         BEGIN
             DECLARE @EntityTypeID INT, @EntityId BIGINT, @INSERTEDCOUNT INT, @DELETEDCOUNT INT, @Table NVARCHAR(MAX), @Query NVARCHAR(MAX), @TableName NVARCHAR(MAX), @Schema NVARCHAR(10);
             SELECT @EntityTypeID = D.Entitytype_Id,
                    @EntityId = D.Id
                    FROM Deleted D;
             SELECT @Tablename = OBJECT_SCHEMA_NAME(Parent_Id)+'.'+OBJECT_NAME(Parent_Id)
                    FROM Sys.Triggers
                    WHERE object_id = @@ProcId;
             IF EXISTS
             (
              SELECT *
                     FROM Dbr.Dbrsends Ds
                     WHERE Ds.Entitytype_Id = @EntityTypeID
                           AND Ds.Entity_Id = @EntityId
             )
                 BEGIN
                     SELECT @INSERTEDCOUNT = COUNT(*)
                            FROM Inserted I;
                     SELECT @DELETEDCOUNT = COUNT(*)
                            FROM Deleted D;
                     IF(@DELETEDCOUNT = 1
                        AND @INSERTEDCOUNT = 0)
                         BEGIN
                             RAISERROR(15600, -1, -1, 'Data is used');
    					ROLLBACK TRANSACTION
                             RETURN;
                         END;
                 END;
             ELSE
                 BEGIN
    
                     
    
                     DECLARE Product_Cursor CURSOR
                     FOR SELECT DISTINCT
                                (C.Table_Name),
                                C.Table_Schema
                                FROM Information_Schema.Columns C
                                     INNER JOIN Information_Schema.Tables T ON T.Table_Name = C.Table_Name
                                WHERE Table_Type = 'BASE TABLE'
                                      AND Column_Name LIKE N'%Entity_Id%';
                    
                     OPEN Product_Cursor;
                     FETCH NEXT FROM Product_Cursor INTO @Table, @Schema;
                     IF @@Fetch_Status <> 0
                         BEGIN
                             PRINT '         <<None>>';
                         END;
                     WHILE @@Fetch_Status = 0
                         BEGIN
                             SET @Table = @Schema+'.'+@Table;
                             SET @Query = 'IF  EXISTS (select id from '+@Table+' where EntityType_Id='+CAST(@EntityTypeID AS NVARCHAR(20))+' and Entity_Id='+CAST(@EntityId AS NVARCHAR(20))+')
         DELETE FROM '+@Table+' where EntityType_Id='+CAST(@EntityTypeID AS NVARCHAR(20))+' and Entity_Id='+CAST(@EntityId AS NVARCHAR(20));
       
                            
                             EXEC (@Query);
                             FETCH NEXT FROM Product_Cursor INTO @Table, @Schema;
                         END;
                     CLOSE Product_Cursor;
                     DEALLOCATE Product_Cursor;
                     SET @Query = ' DELETE FROM '+@TableName+' where Id='+CAST(@EntityId AS NVARCHAR(20));
                     
                     EXEC (@Query);
                 END;
         END;


    Thursday, December 24, 2015 10:08 AM
  • Hi ALireza,

    Glad to hear that the issue is resolved. Thanks for your sharing, you can mark your reply as answer, other community members could benefit from your solution.

    Thanks,
    Lydia Zhang


    Lydia Zhang
    TechNet Community Support


    Monday, December 28, 2015 7:19 AM