CODEWITHSUNDEEP
sql-serverentity-frameworkrelationship
Misi
Misi

Reputation: 748

one to zero or one relationship ASP.NET MVC

I have a ASP.NET MVC 4 application with EF and I'm not using CodeFirst. I want my SQL tables to look like this :

Orders 1 ---- 0...1 OrdersWithShipment

CREATE TABLE Orders (
OrderId int NOT NULL IDENTITY(1,1),
OrderDate date NOT NULL,
OrderNo int NOT NULL,
ShipmentId Int NULL
CONSTRAINT [Orders_PK] PRIMARY KEY CLUSTERED 
(
   [OrderId] ASC
))

CREATE TABLE OrdersWithShipment (
ShipmentId int NOT NULL IDENTITY(1,1),
OrderId int NULL,
ShipmentDate date NOT NULL 
CONSTRAINT [OrdersWithShipment_PK] PRIMARY KEY CLUSTERED 
(
[ShipmentId] ASC
))

Q : What do I need to do (in SQL and EF) so that I have a 1 --- 0...1 relationship ?

Edit : Can I use unique filtered index link

Upvotes: 0

Views: 719

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115610

This is how an 1..0-1 could be defined in SQL (ShipmentId removed from both tables):

CREATE TABLE Orders (
OrderId int NOT NULL IDENTITY(1,1),
OrderDate date NOT NULL,
OrderNo int NOT NULL,
CONSTRAINT [Orders_PK] PRIMARY KEY CLUSTERED 
  ( [OrderId] ASC )
) ;

CREATE TABLE OrdersWithShipment (
OrderId int NOT NULL,
ShipmentDate date NOT NULL, 
CONSTRAINT [OrdersWithShipment_PK] 
  PRIMARY KEY CLUSTERED 
  ( [OrderId] ASC ),
CONSTRAINT [Orders_OrdersWithShipment_FK] 
  FOREIGN KEY 
    ( [OrderId] )
  REFERENCES [Orders] 
    ( [OrderId] )
) ;

Upvotes: 2

Ladislav Mrnka
Ladislav Mrnka

Reputation: 364359

EF doesn't support unique keys (except primary keys), so you will not achieve one-to-one relation with this setup. If you want one-to-one relation you have to remove OrderId column from OrderWithShipment table and ShipmentId from Orders table and put your foreign key constraint directly on ShipmentId in OrdersWithShipment table. That will build one-to-one relation for EF where Orders is principal table for relation with OrdersWithShipment.

Upvotes: 2

Related Questions