I have the two tables below, ideas on how to demand constaint one to Nodes having NodeTypeId 2 (CannotBeParent) cannot be a dad as opposed to end up in?
I might begin by including a flag so you’re able to dining table [NodeTypes], to indicate whether your node sort of can take part since the moms and dad for the a love.
Notice that And i am adding a unique limitation by (NodeTypeId, CanBeParent). That is redundant, it will allow me to later on fool around with a foreign secret leading so you’re able to they.
The next step is to add so it column also to desk [Nodes], to make sure that the few (NodeTypeId, CanBeParent) can be obtained in dining table [NodeTypes]. We’ll also add a computed carried on column (ParentNodeId_CanBeParent), having well worth equivalent step 1, to make [ParentNodeId] to become listed on while the parent automagically, and you will reference table [Nodes] utilizing the few (ParentNodeId, ParentNodeId_CanBePArent).
It could be easier in the event the was able to fool around with a select declaration inside a restriction, or being able to would assertions (view restriction in the database peak), but T-SQL lacks of those features currently.
Where is actually Alex Kuznetsov when you need your? Alex have come with constraintss so you can demand team guidelines extremely some one manage simply believe would-be you can having triggers.
But then, the guy commonly hits that by the addition of a keen auxillary line. Maybe you will offer even more record on what you are looking to go? Which will to provide inspiration to a few provider.
In the event that Can be found (Discover Ideal 1 step one Regarding NodeTypes Interior Subscribe Nodes To the Nodes.NodeId = And Nodes.FK_NodeTypeId = NodeTypes.NodeTypeId And you will NodeTypes.NodeType = ‘CanBeParent’) Initiate Place = step one Prevent Else Start Set = 0 Prevent Get back Prevent;
, NodeType varchar(255) perhaps not null ) do table Nodes (NodeId int restriction PK__Nodes_NodeId First Secret , FK_NodeTypeId int constraint FK__NodeTypes_NodeTypeId sources NodeTypes (NodeTypeId) , FK_ParentNodeid int constraint FK__Nodes_ParentNodeId__Nodes_NodeId sources Nodes (NodeId) Look at (dbo.CanBeParent(FK_ParentNodeid) = 1) )
insert Nodes values (1,1,1) <-- this will pass, because it has a parent 1 insert Nodes values (2,2,1) <-- this will pass, because it has a parent 1 which can be a parent insert Nodes values (3,2,2) <-- this will fail, the parent nodeId=2, is of NodeTypeId 2, which cant be a parent
I understand Alex a lot to your SQLBlog I can look higher truth be told there, but you is right more In my opinion about this consult the fresh new better I have so you can a remedy. It’s variety of for example that have a customer that have an allocated transformation rep and get the members of brand new representatives chain from demand one another top to bottom, however the consumer is more eg a virtual employee but can not do some body. In this instance an excellent workstation normally strings together some equipment out-of a community pool eg adult cams and you can microphones, nevertheless the workstation alone can not be a parent unit. I do believe the client-sales agent table structure will get implement right here, I am able to opinion the software program build document to see if one is sensible.
Less invisible as a cause, it may really works. I could see how nHibernate enjoys it tomorrow, and you can my personal manager.
I’d begin by including a banner so you can desk [NodeTypes], to point in the event the node kind of can participate as mother or father during the a relationship.
Note that I am also adding a different restriction from the (NodeTypeId, CanBeParent). This really is redundant, it will allow me to later fool around with a different key directing so you’re able to they.
The next step should be to incorporate this column and also to table [Nodes], to make certain the couple (NodeTypeId, CanBeParent) can be acquired within the table [NodeTypes]. We are going to include a calculated continuing line (ParentNodeId_CanBeParent), having value equal 1, to force [ParentNodeId] to join since the father or mother by default, and source dining table [Nodes] utilizing the couples (ParentNodeId, ParentNodeId_CanBePArent).
It will be convenient if was able to explore a choose statement to the a restriction, or being in a position to would assertions (examine constraint at databases peak), however, T-SQL lacks of them keeps currently.
There is no such thing as a “node_type_id” there can be a “
I never use meta data particularly “pk_” otherwise “fk_” in the study element identity. The goal of a data function christian cupid-app name is to share with us what it is from the their character and never the way it try used in this particular dining table.
If you utilize more than-sized sequence such as this, your desk will fill which have trash. An effective coders have fun with references when the band of philosophy was higher or volition. Instance, your order outline UPC you’ll site the Collection desk so you’re able to demand a tip that individuals just take purchases to possess gift suggestions within the stock.
In the event the set try small and secure, after that we have fun with a check (x During the (..)) constants. Such as for example, “sex_password TINYINT Default 0 Maybe not NULL Evaluate (sex_code From inside the (0, 1, 2, 9)) — iso-5218
Create Dining table Nodes (node_id INTEGER Not NULL Number one Secret, node_form of INTEGER Maybe not NULL Recommendations Node_Brands (node_type), parent_node_id INTEGER Recommendations Nodes (node_id));
Everything are creating try an enthusiastic adjacency listing design. It imitates non-relational pointer stores for the SQL. It contains one another entities (nodes) while the hierarchy (relationship). But the correct cannot be each other (Chen? E-R acting? Freshman database classification in school?). So although this is nevertheless an incorrect analysis model, you have written it as
Manage Table Forest_and_Nodes (node_id INTEGER Maybe not NULL First Secret, node_particular CHAR(1) Default ‘N’ Perhaps not NULL Take a look at (node_input (‘P’, ‘N’), — P= You’ll be able to Mother, N= Non-Moms and dad parent_node_id INTEGER Recommendations Tree_and_Nodes (node_id), Examine (Instance When node_sort of = ‘N’ And you will father or mother_node_id Is not NULL Upcoming ‘F’ More ‘T’ End = ‘T’) );
–CELKO– Courses for the Celko Show to have Morgan-Kaufmann Publishing: Analytics and you can OLAP for the SQL / Investigation and you can Databases: Basics in practice Study / Measurements and you can Standards into the SQL SQL for Sming Layout / SQL Puzzles and you can Solutions / Considering during the Set / Woods and Hierarchies in the SQL
2023/05/24Thể loại : christian cupid visitorsTab :