我们的SQL或实体框架中的错误?
我从我们的DBA中获得了一些SQL,我只是检查它是正确的,因为EF似乎没有将实体完全链接在一起。 它知道有一个关系,但不执行PK链接的FK。
任何想法或想法(而不是使用NHibernate!)都很赞赏。
1 CREATE TABLE [dbo].[Employee]( 2 [ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, 3 [PersonID] [int] NOT NULL, 4 [GenderID] [int] NULL, 5 [EthnicOriginID] [int] NULL, 6 [MaritalStatusID] [int] NULL, 7 [DateOfBirth] [datetime] NULL, 8 [LeaveDate] [datetime] NULL, 9 [OptOut] [bit] NULL CONSTRAINT [DF_employee_OptOut] DEFAULT (0), 10 [OptOutDate] [datetime] NULL, 11 [PassportNumber] [nvarchar](50) NULL, 12 [WorkPermitNumber] [nvarchar](50) NULL, 13 [WorkPermitExpiryDate] [datetime] NULL, 14 [PayrollNumber] [nvarchar](50) NULL, 15 [NINumber] [nvarchar](50) NULL, 16 [Visa] [bit] NULL CONSTRAINT [DF_employee_Visa] DEFAULT (0), 17 [VisaNumber] [nvarchar](50) NULL, 18 [VisaExpiryDate] [smalldatetime] NULL, 19 [GuaranteeAmount] [money] NULL, 20 [GuaranteeDuration] [int] NULL, 21 [GuaranteeEndDate] [datetime] NULL, 22 [GuaranteePeriod] [int] NULL, 23 [IsDisabled] [bit] NULL CONSTRAINT [DF_employee_IsDisabled] DEFAULT (0), 24 [DisabilityReferenceNumber] [nvarchar](20) NULL, 25 [IsActive] [bit] NULL CONSTRAINT [DF_employee_Active] DEFAULT (1), 26 [IsUKResident] [bit] NULL, 27 [BankAccount] [nvarchar](100) NULL, 28 CONSTRAINT [PK_employee] PRIMARY KEY CLUSTERED 29 ( 30 [ID] ASC 31 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], 32 CONSTRAINT [IX_employee] UNIQUE NONCLUSTERED 33 ( 34 [ID] ASC 35 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 36 ) ON [PRIMARY] 37 38 GO 39 ALTER TABLE [dbo].[Employee] WITH NOCHECK ADD CONSTRAINT [FK_Employee_People] FOREIGN KEY([PersonID]) 40 REFERENCES [dbo].[Person] ([Id]) 41 NOT FOR REPLICATION 42 GO 43 ALTER TABLE [dbo].[Employee] NOCHECK CONSTRAINT [FK_Employee_People] 44 45 46 ---- 47 48 CREATE TABLE [dbo].[EmployeeWorkHistory]( 49 [id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, 50 [EmployeeId] [int] NOT NULL, 51 [JobTitle] [nvarchar](50) NULL, 52 [OfficeId] [int] NULL, 53 [DepartmentId] [int] NULL, 54 [StartDate] [smalldatetime] NULL, 55 [EndDate] [smalldatetime] NULL, 56 [Salary] [decimal](18, 0) NULL, 57 [ContractTypeId] [int] NULL, 58 [CommentID] [int] NULL, 59 [WorkHours] [float] NULL, 60 [ManagerEmployeeID] [int] NULL, 61 [EmployeeTypeID] [int] NULL, 62 [LastWorkingDay] [smalldatetime] NULL, 63 [GardenLeave] [bit] NULL CONSTRAINT [DF_employeeWorkHistory_GardenLeave] DEFAULT (0), 64 [WorkingHours] [nvarchar](20) NULL CONSTRAINT [DF_EmployeeWorkHistory_WorkingHours] DEFAULT ('8.30am - 5.30pm'), 65 [WorkingDays] [nvarchar](100) NULL CONSTRAINT [DF_EmployeeWorkHistory_WorkingDays] DEFAULT ('Monday to Friday'), 66 [TerminationId] [int] NULL, 67 [TerminiationDate] [smalldatetime] NULL, 68 CONSTRAINT [PK_employeeWorkHistory] PRIMARY KEY CLUSTERED 69 ( 70 [id] ASC 71 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 72 ) ON [PRIMARY] 73 74 GO 75 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EmployeeWorkHistory', @level2type=N'COLUMN',@level2name=N'WorkingHours' 76 GO 77 ALTER TABLE [dbo].[EmployeeWorkHistory] WITH NOCHECK ADD CONSTRAINT [FK_EmployeeWorkHistory_ContractType] FOREIGN KEY([ContractTypeId]) 78 REFERENCES [dbo].[ContractType] ([Id]) 79 NOT FOR REPLICATION 80 GO 81 ALTER TABLE [dbo].[EmployeeWorkHistory] CHECK CONSTRAINT [FK_EmployeeWorkHistory_ContractType] 82 GO 83 ALTER TABLE [dbo].[EmployeeWorkHistory] WITH NOCHECK ADD CONSTRAINT [FK_EmployeeWorkHistory_Employee] FOREIGN KEY([EmployeeId]) 84 REFERENCES [dbo].[Employee] ([ID]) 85 NOT FOR REPLICATION 86 GO 87 ALTER TABLE [dbo].[EmployeeWorkHistory] CHECK CONSTRAINT [FK_EmployeeWorkHistory_Employee] 88 GO 89 90 91 ------ 92 93 94 CREATE TABLE [dbo].[ContractType]( 95 [Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, 96 [Text] [nvarchar](50) NOT NULL, 97 [IsActive] [bit] NOT NULL CONSTRAINT [DF_ContractType_IsActive] DEFAULT (1), 98 CONSTRAINT [PK_ContractType] PRIMARY KEY CLUSTERED 99 ( 100 [Id] ASC 101 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY] 102 ) ON [PRIMARY] 103 104 -- 105 106 CREATE TABLE [dbo].[EmployeeReference]( 107 [ID] [int] IDENTITY(1,1) NOT NULL, 108 [EmployeeID] [int] NOT NULL, 109 [RefereePersonID] [int] NOT NULL, 110 [Company] [nvarchar](200) NULL, 111 [CommentID] [int] NULL, 112 [DateRequested] [smalldatetime] NULL, 113 [DateReceived] [smalldatetime] NULL, 114 [TimeKnownFor] [nvarchar](100) NULL, 115 [ReferenceDocument] [nvarchar](500) NULL, 116 [ReferenceTypeID] [int] NOT NULL, 117 [FileAttachmentID] [int] NULL, 118 [ContactDetailId] [int] NULL, 119 CONSTRAINT [PK_EmployeeReference] PRIMARY KEY CLUSTERED 120 ( 121 [ID] ASC 122 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 123 ) ON [PRIMARY] 124 125 GO 126 ALTER TABLE [dbo].[EmployeeReference] WITH NOCHECK ADD CONSTRAINT [FK_EmployeeReference_Person] FOREIGN KEY([RefereePersonID]) 127 REFERENCES [dbo].[Person] ([Id]) 128 NOT FOR REPLICATION 129 GO 130 ALTER TABLE [dbo].[EmployeeReference] NOCHECK CONSTRAINT [FK_EmployeeReference_Person] 131 GO 132 ALTER TABLE [dbo].[EmployeeReference] WITH NOCHECK ADD CONSTRAINT [FK_EmployeeReferenceMapping_Employee] FOREIGN KEY([EmployeeID]) 133 REFERENCES [dbo].[Employee] ([ID]) 134 NOT FOR REPLICATION 135 GO 136 ALTER TABLE [dbo].[EmployeeReference] NOCHECK CONSTRAINT [FK_EmployeeReferenceMapping_Employee] 137 138 --- 139 140 141 CREATE TABLE [dbo].[Person]( 142 [Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, 143 [SalutationId] [int] NULL, 144 [Firstname] [nvarchar](50) NULL, 145 [Middlename] [nvarchar](50) NULL, 146 [Surname] [nvarchar](50) NULL, 147 [PreferredName] [nvarchar](50) NULL, 148 [ContactDetailId] [int] NULL, 149 [PersonTypeId] [int] NULL, 150 [IsActive] [bit] NOT NULL CONSTRAINT [DF_people_Active] DEFAULT (1), 151 [EnteredBy] [int] NULL, 152 [EnteredDate] [datetime] NULL, 153 [UpdatedBy] [int] NULL, 154 [UpdatedDate] [datetime] NULL, 155 CONSTRAINT [PK_person] PRIMARY KEY CLUSTERED 156 ( 157 [Id] ASC 158 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 159 ) ON [PRIMARY] 160 161 GO 162 GO 163
您已经在外键约束上定义了NOCHECK
ALTER TABLE [dbo].[Employee] NOCHECK CONSTRAINT [FK_Employee_People]
另外,你有一个重复的员工索引。
链接地址: http://www.djcxy.com/p/84109.html