I am implementing a database design that has a vehicle table, vehicle engine and vehicle gear table with SQL 2005.
我正在实施一个数据库设计,其中包含一个带有SQL 2005的车辆工作台,车辆引擎和车辆齿轮表。
Each table has an ID that is a SQL identity number, and each engine and gear has a relation with the vehicle ID. So before I create a vehicle I must create an engine and gear.
每个表都有一个ID为SQL标识号的ID,每个引擎和齿轮与车辆ID有关系。因此,在创造车辆之前,我必须创造一个发动机和齿轮。
How could I know the vehicle identity number when creating the engine and gear? The vehicle row hasn't yet been created because of the foreign key constraint with the engine and gear tables?
在创建发动机和齿轮时,我怎么知道车辆识别号?由于发动机和齿轮表的外键约束,车辆行尚未创建?
Should I implement an automatic trigger that on a creation of a vehicle creates an empty row for the engine and gear linked to the vehicle? But again how could I know the vehicle ID?
我是否应该实施一个自动触发器,在创建车辆时为发动机和与车辆相连的齿轮创建一个空行?但我又怎么知道车辆ID?
10 个解决方案
#1
4
Whichever, there's no need to create rows in any of the tables for which you don't have data. It's fine to, for instance, have an engine row that is unmatched to a vehicle. You can add what's missing when you find it.
无论哪个,都不需要在没有数据的任何表中创建行。例如,可以使用与车辆无法匹配的发动机排。您可以在找到它时添加缺少的内容。
I think I understand the design. Each vehicle can have one engine and one transmission. But you may record the transmission or engine before you find the vehicle.
我想我理解这个设计。每辆车可以有一个发动机和一个变速器。但您可以在找到车辆之前记录变速箱或发动机。
They really are separate entities, so treat them as such. You may easily end up with engines and transmissions which you never match to a vehicle.
它们实际上是独立的实体,所以请对它们进行处理。您可能很容易得到您从未与车辆匹配的发动机和变速箱。
Another more interesting question is whether you might end up with a transmission matched to an engine, but no vehicle. One often sees an engine and tramsission bolted together with no vehicle in sight. And they'll often be sold together as well.
另一个更有趣的问题是,你是否可能最终得到一个与发动机匹配的变速器,但没有车辆。人们常常看到发动机和电动机连接在一起,看不到车辆。它们通常也会一起出售。
In fact, you can imagine any of the three existing alone, or matched to one or two of the other entities.
实际上,您可以想象三个中的任何一个单独存在,或者与其他一个或两个实体匹配。
Triggers have no role here. If used at all, triggers should be restricted to fine-grained referential integrity rules about the schema structure - not for business rules like this. There are no mandatory constraints - every foreign key is optional (nullable). And there are several different ways to set the FK fields up.
触发器在这里没有任何作用。如果完全使用,则触发器应限于有关模式结构的细粒度参照完整性规则 - 不适用于此类业务规则。没有强制约束 - 每个外键都是可选的(可为空)。并且有几种不同的方法来设置FK字段。
#2
1
Are you sure you got the tables design right? I don't really understand what kind of relationships you have between you entities. For example: are you trying to create one to many or many to one relationship between Vehicle and Engine?
你确定你的桌子设计合适吗?我真的不明白你们实体之间有什么样的关系。例如:您是否尝试在车辆和引擎之间创建一对多或多对一关系?
One option could be (if it meets your needs):
一个选项可能是(如果它满足您的需求):
Vehicle: (ID, EngineID, GearID, ...)
车辆:(ID,EngineID,GearID,......)
Engine (ID, other engine data)
引擎(ID,其他引擎数据)
Gear (ID, other gear data)
齿轮(ID,其他齿轮数据)
#3
1
The fact that you have foreign key relationships between your tables does not mean that you have to create your data in a particular order. Normally, one would expect the Vehicle record to be created first, and then have engines and gears assigned to it later, but this does not have to be the case.
您的表之间存在外键关系这一事实并不意味着您必须按特定顺序创建数据。通常,人们会期望首先创建车辆记录,然后稍后为其分配引擎和齿轮,但事实并非如此。
If, in your scenario, it is possible for engines or gears to be recorded in the database, before being assigned to a vehicle, then you will need to make the FK columns which reference Vehicle ID allow nulls. These can then be linked to a vehicle id once the vehicle row has been created.
如果在您的方案中,在分配给车辆之前,可以将引擎或齿轮记录在数据库中,那么您将需要使引用车辆ID的FK列允许空值。然后,一旦创建了车辆行,就可以将这些链接到车辆ID。
Alternatively, you can create your Vehicle record, then assign engine and gear records to it as they are created.
或者,您可以创建车辆记录,然后在创建时为其分配引擎和齿轮记录。
#4
0
Will you only ever have one engine and one gear for each vehicle? If that's the case, then why are they in different tables?
每辆车只有一个发动机和一个齿轮吗?如果是这样的话,为什么他们在不同的表中呢?
If you must have them in two tables, you can create an INSTEAD OF INSERT trigger as described here: http://msdn.microsoft.com/en-us/library/ms175089.aspx
如果必须将它们放在两个表中,则可以按照此处所述创建INSTEAD OF INSERT触发器:http://msdn.microsoft.com/en-us/library/ms175089.aspx
#5
0
If you must have two separate tables, you could join them in a view then update/insert into the view ...
如果您必须有两个单独的表,则可以在视图中将它们连接起来然后更新/插入到视图中...
CREATE VIEW VehicleComplete AS SELECT * FROM Vehicle INNER JOIN VehicleEngine USING(VehicleID)
UPDATE VehicleComplete SET Rego = 'ABC 123', EngineModel = '380' WHERE VehicleID = 1
#6
0
I have to say that personally I believe this scenario doesn't make sense, really.
我不得不说,我个人认为这种情况真的没有意义。
In order for a gear or engine to exist, you need a vehicle. Your business rules/Domain Model should really enforce that, and the foreign key relationships are then just a way of validating this is the case.
为了存在齿轮或发动机,您需要一辆车。您的业务规则/域模型应该真正强制执行,然后外键关系只是验证这种情况的一种方式。
Alternatively it might be better to think of the engine-vehicle relationship as a many-many. For this you need an additional table to link the vehicle and engine. This would mean you can have foreign key constraints, but it also means that an engine can be linked to many cars, and vice versa. This more realistically models the real-world, where the same engine is used in many models of car, and a car may have a variety of engines to chose from.
或者,将发动机 - 车辆关系视为许多人可能会更好。为此,您需要一个额外的表来链接车辆和发动机。这意味着您可以拥有外键约束,但这也意味着引擎可以链接到许多汽车,反之亦然。这更真实地模拟了现实世界,其中相同的发动机用于许多车型,并且汽车可以具有多种发动机可供选择。
#7
0
These kind of things are solved with any persistence framework out there. In a typical O/R mapper scenario, you simply create the entities required (indirectly for example) and the O/R mapper saves them in the right order and syncs the FK/PK fields automatically.
这些事情都可以通过任何持久性框架来解决。在典型的O / R映射器场景中,您只需创建所需的实体(例如间接),O / R映射器以正确的顺序保存它们并自动同步FK / PK字段。
If you're fighting with these kind of problems, you really lose time (and thus money) over things which are already solved for you and you can't spend that time on the problem for the customer. So do yourself and your customer a favor and at least look at some of the persistence frameworks out there.
如果你正在与这些问题作斗争,你真的会浪费时间(因而也就是金钱)而不是已经为你解决的事情,你不能把时间花在客户的问题上。所以你自己和你的客户一定要帮忙,至少要看看那里的一些持久性框架。
(disclaimer: I'm the lead developer of an o/r mapper framework)
(免责声明:我是o / r映射器框架的首席开发人员)
#8
0
But again how could I know the vehicle ID?
但我又怎么知道车辆ID?
There is a table called INSERTED which is available in insert triggers. You will find the vehicle ID there.
有一个名为INSERTED的表,可在插入触发器中使用。你会在那里找到车辆ID。
#9
0
Thanks for all the answers.
感谢所有的答案。
Actually I meant: Vehicle: (ID, other Vehicle data, ...) Engine (ID, VehicleID, other engine data) Gear (ID, VehicleID, other gear data)
实际上我的意思是:车辆:( ID,其他车辆数据,......)发动机(ID,VehicleID,其他发动机数据)齿轮(ID,VehicleID,其他齿轮数据)
So each vehicle can have multiple engines and gears… (Yes I know that in the real world an engine can fit multiple cars but that’s not my target)
所以每辆车都可以有多个发动机和齿轮......(是的,我知道在现实世界中,发动机可以安装多辆车,但这不是我的目标)
I wasn't aware of the enforce foreign key constraint that by default is set to YES in SQL 2005.
我不知道在SQL 2005中默认设置为YES的强制外键约束。
So set it to NO and know its working just fine.
所以把它设置为NO并且知道它的工作正常。
Thanks again,
Omri.
#10
-1
If you really can't create each entity at diferent moments, you should create them within a transaction, explicitly showing at are the necessary steps to create each entity.
如果您真的无法在不同时刻创建每个实体,则应在事务中创建它们,明确显示创建每个实体的必要步骤。
The trigger solution, which is many times the easier to implement, would be the hardest to mantain, because it "hides" behind the curtins important business rules.
触发器解决方案很容易实现,将是最难以实现的,因为它“隐藏”了特定重要业务规则的背后。
Well .... in either case, you really should take a look at your problem definition, as this kind of questions usually arrise from bad business domain definition.
嗯....在任何一种情况下,你真的应该看看你的问题定义,因为这类问题通常来自糟糕的业务领域定义。
Bottom line: each trigger you have would be another headache for the next project iteraction.
结论:你所拥有的每一个触发器都是下一个项目迭代的另一个问题。
#1
4
Whichever, there's no need to create rows in any of the tables for which you don't have data. It's fine to, for instance, have an engine row that is unmatched to a vehicle. You can add what's missing when you find it.
无论哪个,都不需要在没有数据的任何表中创建行。例如,可以使用与车辆无法匹配的发动机排。您可以在找到它时添加缺少的内容。
I think I understand the design. Each vehicle can have one engine and one transmission. But you may record the transmission or engine before you find the vehicle.
我想我理解这个设计。每辆车可以有一个发动机和一个变速器。但您可以在找到车辆之前记录变速箱或发动机。
They really are separate entities, so treat them as such. You may easily end up with engines and transmissions which you never match to a vehicle.
它们实际上是独立的实体,所以请对它们进行处理。您可能很容易得到您从未与车辆匹配的发动机和变速箱。
Another more interesting question is whether you might end up with a transmission matched to an engine, but no vehicle. One often sees an engine and tramsission bolted together with no vehicle in sight. And they'll often be sold together as well.
另一个更有趣的问题是,你是否可能最终得到一个与发动机匹配的变速器,但没有车辆。人们常常看到发动机和电动机连接在一起,看不到车辆。它们通常也会一起出售。
In fact, you can imagine any of the three existing alone, or matched to one or two of the other entities.
实际上,您可以想象三个中的任何一个单独存在,或者与其他一个或两个实体匹配。
Triggers have no role here. If used at all, triggers should be restricted to fine-grained referential integrity rules about the schema structure - not for business rules like this. There are no mandatory constraints - every foreign key is optional (nullable). And there are several different ways to set the FK fields up.
触发器在这里没有任何作用。如果完全使用,则触发器应限于有关模式结构的细粒度参照完整性规则 - 不适用于此类业务规则。没有强制约束 - 每个外键都是可选的(可为空)。并且有几种不同的方法来设置FK字段。
#2
1
Are you sure you got the tables design right? I don't really understand what kind of relationships you have between you entities. For example: are you trying to create one to many or many to one relationship between Vehicle and Engine?
你确定你的桌子设计合适吗?我真的不明白你们实体之间有什么样的关系。例如:您是否尝试在车辆和引擎之间创建一对多或多对一关系?
One option could be (if it meets your needs):
一个选项可能是(如果它满足您的需求):
Vehicle: (ID, EngineID, GearID, ...)
车辆:(ID,EngineID,GearID,......)
Engine (ID, other engine data)
引擎(ID,其他引擎数据)
Gear (ID, other gear data)
齿轮(ID,其他齿轮数据)
#3
1
The fact that you have foreign key relationships between your tables does not mean that you have to create your data in a particular order. Normally, one would expect the Vehicle record to be created first, and then have engines and gears assigned to it later, but this does not have to be the case.
您的表之间存在外键关系这一事实并不意味着您必须按特定顺序创建数据。通常,人们会期望首先创建车辆记录,然后稍后为其分配引擎和齿轮,但事实并非如此。
If, in your scenario, it is possible for engines or gears to be recorded in the database, before being assigned to a vehicle, then you will need to make the FK columns which reference Vehicle ID allow nulls. These can then be linked to a vehicle id once the vehicle row has been created.
如果在您的方案中,在分配给车辆之前,可以将引擎或齿轮记录在数据库中,那么您将需要使引用车辆ID的FK列允许空值。然后,一旦创建了车辆行,就可以将这些链接到车辆ID。
Alternatively, you can create your Vehicle record, then assign engine and gear records to it as they are created.
或者,您可以创建车辆记录,然后在创建时为其分配引擎和齿轮记录。
#4
0
Will you only ever have one engine and one gear for each vehicle? If that's the case, then why are they in different tables?
每辆车只有一个发动机和一个齿轮吗?如果是这样的话,为什么他们在不同的表中呢?
If you must have them in two tables, you can create an INSTEAD OF INSERT trigger as described here: http://msdn.microsoft.com/en-us/library/ms175089.aspx
如果必须将它们放在两个表中,则可以按照此处所述创建INSTEAD OF INSERT触发器:http://msdn.microsoft.com/en-us/library/ms175089.aspx
#5
0
If you must have two separate tables, you could join them in a view then update/insert into the view ...
如果您必须有两个单独的表,则可以在视图中将它们连接起来然后更新/插入到视图中...
CREATE VIEW VehicleComplete AS SELECT * FROM Vehicle INNER JOIN VehicleEngine USING(VehicleID)
UPDATE VehicleComplete SET Rego = 'ABC 123', EngineModel = '380' WHERE VehicleID = 1
#6
0
I have to say that personally I believe this scenario doesn't make sense, really.
我不得不说,我个人认为这种情况真的没有意义。
In order for a gear or engine to exist, you need a vehicle. Your business rules/Domain Model should really enforce that, and the foreign key relationships are then just a way of validating this is the case.
为了存在齿轮或发动机,您需要一辆车。您的业务规则/域模型应该真正强制执行,然后外键关系只是验证这种情况的一种方式。
Alternatively it might be better to think of the engine-vehicle relationship as a many-many. For this you need an additional table to link the vehicle and engine. This would mean you can have foreign key constraints, but it also means that an engine can be linked to many cars, and vice versa. This more realistically models the real-world, where the same engine is used in many models of car, and a car may have a variety of engines to chose from.
或者,将发动机 - 车辆关系视为许多人可能会更好。为此,您需要一个额外的表来链接车辆和发动机。这意味着您可以拥有外键约束,但这也意味着引擎可以链接到许多汽车,反之亦然。这更真实地模拟了现实世界,其中相同的发动机用于许多车型,并且汽车可以具有多种发动机可供选择。
#7
0
These kind of things are solved with any persistence framework out there. In a typical O/R mapper scenario, you simply create the entities required (indirectly for example) and the O/R mapper saves them in the right order and syncs the FK/PK fields automatically.
这些事情都可以通过任何持久性框架来解决。在典型的O / R映射器场景中,您只需创建所需的实体(例如间接),O / R映射器以正确的顺序保存它们并自动同步FK / PK字段。
If you're fighting with these kind of problems, you really lose time (and thus money) over things which are already solved for you and you can't spend that time on the problem for the customer. So do yourself and your customer a favor and at least look at some of the persistence frameworks out there.
如果你正在与这些问题作斗争,你真的会浪费时间(因而也就是金钱)而不是已经为你解决的事情,你不能把时间花在客户的问题上。所以你自己和你的客户一定要帮忙,至少要看看那里的一些持久性框架。
(disclaimer: I'm the lead developer of an o/r mapper framework)
(免责声明:我是o / r映射器框架的首席开发人员)
#8
0
But again how could I know the vehicle ID?
但我又怎么知道车辆ID?
There is a table called INSERTED which is available in insert triggers. You will find the vehicle ID there.
有一个名为INSERTED的表,可在插入触发器中使用。你会在那里找到车辆ID。
#9
0
Thanks for all the answers.
感谢所有的答案。
Actually I meant: Vehicle: (ID, other Vehicle data, ...) Engine (ID, VehicleID, other engine data) Gear (ID, VehicleID, other gear data)
实际上我的意思是:车辆:( ID,其他车辆数据,......)发动机(ID,VehicleID,其他发动机数据)齿轮(ID,VehicleID,其他齿轮数据)
So each vehicle can have multiple engines and gears… (Yes I know that in the real world an engine can fit multiple cars but that’s not my target)
所以每辆车都可以有多个发动机和齿轮......(是的,我知道在现实世界中,发动机可以安装多辆车,但这不是我的目标)
I wasn't aware of the enforce foreign key constraint that by default is set to YES in SQL 2005.
我不知道在SQL 2005中默认设置为YES的强制外键约束。
So set it to NO and know its working just fine.
所以把它设置为NO并且知道它的工作正常。
Thanks again,
Omri.
#10
-1
If you really can't create each entity at diferent moments, you should create them within a transaction, explicitly showing at are the necessary steps to create each entity.
如果您真的无法在不同时刻创建每个实体,则应在事务中创建它们,明确显示创建每个实体的必要步骤。
The trigger solution, which is many times the easier to implement, would be the hardest to mantain, because it "hides" behind the curtins important business rules.
触发器解决方案很容易实现,将是最难以实现的,因为它“隐藏”了特定重要业务规则的背后。
Well .... in either case, you really should take a look at your problem definition, as this kind of questions usually arrise from bad business domain definition.
嗯....在任何一种情况下,你真的应该看看你的问题定义,因为这类问题通常来自糟糕的业务领域定义。
Bottom line: each trigger you have would be another headache for the next project iteraction.
结论:你所拥有的每一个触发器都是下一个项目迭代的另一个问题。