什么/应该NULL意味着与FK关系 - 数据库 [英] What does/should NULL mean along with FK relationships - Database

问题描述

我在我的关系SQL数据库中经历了很难创建FK关系，在工作上进行了简短的讨论之后，我们意识到我们有可能是最有可能导致问题的列。我一直将NULL视为未分配，未指定，空白等，并且真的没有看到问题。

我正在发言的其他开发人员认为，处理如果两个实体之间存在关系的情况的唯一方法，则必须创建一个表加入两个实体的数据...

至少对于一个包含另一个表的ID的列，如果该列不是null，那么它必须有一个来自另一个表的ID，但是如果它为NULL，那么这样就可以继续。似乎这本身就与某些说话和建议相矛盾。

最好的做法或正确的方式来处理两个表之间的关系的情况并且如果指定了一个值，那么它必须在另一个表中...

解决方案

这是完全可以接受的，这意味着，如果该列具有任何值，则其值必须存在于另一个表中。 （我看到其他答案是否定的，但我请求不同。）

想一下车辆和发动机的表，发动机没有安装在车辆中所以VehicleID为null）。或者是带有主管专栏的员工表和公司首席执行官。

更新：根据Solberg的要求，这里是一个有两个外键关系的表格示例外键字段值可以为空。

  CREATE TABLE [dbo]。[EngineTable] （
 [EngineID] [int] IDENTITY（1,1）NOT NULL，
 [EngineCylinders] smallint NOT NULL，
 CONSTRAINT [EngineTbl_PK] PRIMARY KEY NONCLUSTERED 
（
 [EngineID] ASC 
）WITH（IGNORE_DUP_KEY = OFF）ON [PRIMARY] 
）ON [PRIMARY] 
 
 CREATE TABLE [dbo]。[CarTable]（
 [CarID] [int] IDENTITY（1,1）NOT NULL，
 [Model] [varchar]（32）COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL，
 [EngineID] [int] NULL 
 CONSTRAINT [PK_UnitList] PRIMARY KEY CLUSTERED 
（
 [CarID] ASC 
）WITH（IGNORE_DUP_KEY = OFF）ON [PRIMARY] 
）ON [PRIMARY] 
 
] ALTER TABLE [dbo]。[CarTable] WITH CHECK ADD CONSTRAINT [FK_Engine_Car] FOREIGN KEY（[EngineID]）
参考[dbo]。[EngineTable]（[EngineID]）
 
 
插入到EngineTable（EngineCylinders）值（4）; 
插入到EngineTable（EngineCylinders）值（6）; 
插入到EngineTable（EngineCylinders）值（6）; 
插入到EngineTable（EngineCylinders）值（8）; 
  

- 现在有些测试：

 插入CarTable（Model，EngineID）值（'G35x'，3）; - 引用第三个引擎
 
插入CarTable（Model，EngineID）值（'Sienna'，13）; -  FK参考无效 - 抛出错误
 
插入CarTable（Model）值（'M'）; - 在引擎ID字段&不抛出错误
  

I was experiencing a hard time creating FK relationships in my relational SQL database and after a brief discussion at work, we realized that we have nullable columns which were most likely contributing to the problem. I have always viewed NULL as meaning unassigned, not specified, blank, etc. and have really never seen a problem with that.

The other developers I was speaking with felt that the only way to handle a situation where if a relationship did exist between 2 entities, then you would have to create a table that joins the data from both entities...

It seems intuitive to me at least to say that for a column that contains an ID from another table, if that column is not null, then it must have an ID from the other table, but if it is NULL then that is OK and move on. It seems like this in itself is contradictory to what some say and suggest.

What is the best practice or correct way to handle situations where there could be a relationship between two tables and if a value is specified then it must be in the other table...

解决方案

It's perfectly acceptable, and it means that, if that column has any value, its value must exist in another table. (I see other answers asserting otherwise, but I beg to differ.)

Think a table of Vehicles and Engines, and the Engines aren't installed in a Vehicle yet (so VehicleID is null). Or an Employee table with a Supervisor column and the CEO of the company.

Update: Per Solberg's request, here is an example of two tables that have a foreign key relationship showing that the foreign key field value can be null.

CREATE TABLE [dbo].[EngineTable](
    [EngineID] [int] IDENTITY(1,1) NOT NULL,
    [EngineCylinders] smallint NOT NULL,
 CONSTRAINT [EngineTbl_PK] PRIMARY KEY NONCLUSTERED 
(
    [EngineID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[CarTable](
    [CarID] [int] IDENTITY(1,1) NOT NULL,
    [Model] [varchar](32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [EngineID] [int] NULL
 CONSTRAINT [PK_UnitList] PRIMARY KEY CLUSTERED 
(
    [CarID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[CarTable]  WITH CHECK ADD CONSTRAINT [FK_Engine_Car] FOREIGN KEY([EngineID])
REFERENCES [dbo].[EngineTable] ([EngineID])


Insert Into EngineTable (EngineCylinders) Values (4);
Insert Into EngineTable (EngineCylinders) Values (6);
Insert Into EngineTable (EngineCylinders) Values (6);
Insert Into EngineTable (EngineCylinders) Values (8);

-- Now some tests:

Insert Into CarTable (Model, EngineID) Values ('G35x', 3);  -- References the third engine

Insert Into CarTable (Model, EngineID) Values ('Sienna', 13);  -- Invalid FK reference - throws an error

Insert Into CarTable (Model) Values ('M');  -- Leaves null in the engine id field & does NOT throw an error 

这篇关于什么/应该NULL意味着与FK关系 - 数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！