eKGEVhRp

1Master { ID, masterfields }
2
3Detail1 { ID, MasterID, detail1fields }
4
5Detail2 { ID, MasterID, detail2fields }
6
7SomeOtherTable {ID, Detail1ID, Detail2ID, someothertablefields }
8	
9Master { ID, masterfields }
10
11DetailMain { ID, MasterID }
12
13Detail1 { DetailMainID, detail1fields }
14
15Detail2 { DetailMainID, detail2fields }
16
17SomeOtherTable {ID, DetailMainID, someothertablefields }
18	
19public class Employee{
20public virtual ICollection<Qualification> Qualifications {get;set;}
21	
22public class Qualification{
23
24public Employee Employee {get;set;}
25
26public virtual ICollection<University> Universities {get;set;}
27	
28public class University{
29
30public Qualification Qualification {get;set;}
31	
32protected override void OnModelCreating(DbModelBuilder modelBuilder){
33
34modelBuilder.Entity<Qualification>().HasRequired(x=> x.Employee).WithMany(e => e.Qualifications);
35modelBuilder.Entity<University>.HasRequired(x => x.Qualification).WithMany(e => e.Universities);
36	
37modelBuilder.Entity<Qualification>().**HasRequired**(x=> x.Employee).WithMany(e => e.Qualifications);
38	
39modelBuilder.Entity<Qualification>().**HasOptional**(x=> x.Employee).WithMany(e => e.Qualifications);
40	
41protected override void OnModelCreating( DbModelBuilder modelBuilder )
42{
43    modelBuilder.Entity<TableName>().HasMany(i => i.Member).WithRequired().WillCascadeOnDelete(false);
44}
45	
46modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();
47	
48IF OBJECT_ID('dbo.fktest2', 'U') IS NOT NULL
49    drop table fktest2
50IF OBJECT_ID('dbo.fktest1', 'U') IS NOT NULL
51    drop table fktest1
52IF EXISTS (SELECT name FROM sysobjects WHERE name = 'fkTest1Trigger' AND type = 'TR')
53    DROP TRIGGER dbo.fkTest1Trigger
54go
55create table fktest1 (id int primary key, anQId int identity)
56go  
57    create table fktest2 (id1 int, id2 int, anQId int identity,
58        FOREIGN KEY (id1) REFERENCES fktest1 (id)
59            ON DELETE CASCADE
60            ON UPDATE CASCADE/*,    
61        FOREIGN KEY (id2) REFERENCES fktest1 (id) this causes compile error so we have to use triggers
62            ON DELETE CASCADE
63            ON UPDATE CASCADE*/ 
64            )
65go
66
67CREATE TRIGGER fkTest1Trigger
68ON fkTest1
69AFTER INSERT, UPDATE, DELETE
70AS
71    if @@ROWCOUNT = 0
72        return
73    set nocount on
74
75    -- This code is replacement for foreign key cascade (auto update of field in destination table when its referenced primary key in source table changes.
76    -- Compiler complains only when you use multiple cascased. It throws this compile error:
77    -- Rrigger Introducing FOREIGN KEY constraint on table may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, 
78    -- or modify other FOREIGN KEY constraints.
79    IF ((UPDATE (id) and exists(select 1 from fktest1 A join deleted B on B.anqid = A.anqid where B.id <> A.id)))
80    begin       
81        update fktest2 set id2 = i.id
82            from deleted d
83            join fktest2 on d.id = fktest2.id2
84            join inserted i on i.anqid = d.anqid        
85    end         
86    if exists (select 1 from deleted)       
87        DELETE one FROM fktest2 one LEFT JOIN fktest1 two ON two.id = one.id2 where two.id is null -- drop all from dest table which are not in source table
88GO
89
90insert into fktest1 (id) values (1)
91insert into fktest1 (id) values (2)
92insert into fktest1 (id) values (3)
93
94insert into fktest2 (id1, id2) values (1,1)
95insert into fktest2 (id1, id2) values (2,2)
96insert into fktest2 (id1, id2) values (1,3)
97
98select * from fktest1
99select * from fktest2
100
101update fktest1 set id=11 where id=1
102update fktest1 set id=22 where id=2
103update fktest1 set id=33 where id=3
104delete from fktest1 where id > 22
105
106select * from fktest1
107select * from fktest2