CODEWITHSUNDEEP
sql-serverentity-framework
Julien
Julien

Reputation: 1192

Adding a required foreign key

I have a "Students" table. I'd like to add the fact that students have bags that contain items:

Students:
| Id | Name   | BagId |
|----|--------|-------|
|  0 |  Josh  |    0  |
|  1 |  Emma  |    2  |

Bags:
| Id |
|----|
|  0 |
|  1 |
|  2 |

Items: 
| BagId | Item    |
|-------|---------|
|     0 | Banana  |
|     0 | Apple   |
|     1 | Pen     |
|     1 | Tissue  |
|     2 | Gun     |

I need the "Bags" table and BagId's because I want other entities than student to be able to have a bag (but if you have a better idea, I'm all ears).

My problem:

=> How can I populate the Bags table and give a BagId to each student between the creation of the Student.BagId column, and the application of the foreign key constraint ? (I don't need to populate the Items table, all bags are empty initially)

I have tried things like 

UPDATE Students
SET BagId = t.Id
FROM (
  INSERT INTO Bags DEFAULT VALUES
  OUTPUT INSERTED) as t

without success.

I am using Entity Framework 6 and I'd like to do all that within a migration.

Upvotes: 0

Views: 33

Answers (1)

Captain0
Captain0

Reputation: 2613

Assuming there is not data in the bags table yet and you can write a custom sql script, you can do something along the lines of 

DECLARE @Temp TABLE(
StudentId INT,
BagId INT IDENTITY(1,1))

INSERT INTO @Temp(
    StudentId
)(
    Select Id
    from Students
)
SET IDENTITY_INSERT Bags ON
INSERT INTO Bags(
   Id
 )(
    Select BagId 
    From @Temp
 )
 SET IDENTITY_INSERT Bags OFF

 UPDATE Students
    Set BagId = T.BagId
 FROM Students S
 INNER JOIN @Temp T on T.StudentId = S.Id

 --just to show results
 Select * from Students
 Select * from Bags

Upvotes: 1

Related Questions