This last secret is the one checking you to a NodeId can be take part in a love while the a grandfather
You will find the 2 tables below, how-to enforce constaint that Nodes having NodeTypeId dos (CannotBeParent) can’t be a dad instead end up chappy telefoonnummer in?
- Modified of the gao.seng Wednesday,
I would personally start with including a flag to help you desk [NodeTypes], to indicate in the event the node style of is engage because parent inside the a relationship.
Notice that And i am incorporating a separate limitation by (NodeTypeId, CanBeParent). This is exactly redundant, it enable us to after use a foreign key leading so you’re able to they.
The next phase will be to put this line and also to desk [Nodes], to ensure that partners (NodeTypeId, CanBeParent) can be found for the desk [NodeTypes]. We are going to include a computed persisted column (ParentNodeId_CanBeParent), that have value equivalent step one, to make [ParentNodeId] to join just like the mother by default, and you may site desk [Nodes] with the few (ParentNodeId, ParentNodeId_CanBePArent).
It might be smoother in the event the were able to fool around with a choose statement into the a check restriction, or becoming able to create assertions (view restriction in the databases level), but T-SQL lacks of those keeps to date.
- 6:58 PM
Most of the answers
Where is Alex Kuznetsov when you need your? Alex attended up with constraintss so you can impose team laws and regulations most anyone manage merely consider would be you can that have leads to.
Then again, the guy commonly achieves you to by adding an auxillary line. Perhaps you can give even more record about what you are seeking reach? That’ll to give motivation to some service.
If Exists (Get a hold of Most readily useful 1 1 Away from NodeTypes Inner Subscribe Nodes To the Nodes.NodeId = And Nodes.FK_NodeTypeId = NodeTypes.NodeTypeId And you may NodeTypes.NodeType = 'CanBeParent’) Initiate Set = 1 Prevent More Initiate Set = 0 Stop Come back End;
, NodeType varchar(255) not null ) do dining table Nodes (NodeId int limitation PK__Nodes_NodeId Top Trick , FK_NodeTypeId int restriction FK__NodeTypes_NodeTypeId records NodeTypes (NodeTypeId) , FK_ParentNodeid int constraint FK__Nodes_ParentNodeId__Nodes_NodeId references Nodes (NodeId) Check (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
- Recommended while the answer of the sdsuser Wednesday,
We realize Alex much into the SQLBlog I will search greater around, you try proper more I think about this consult the brand new closer I get to a remedy. It is sort of instance that have a customer that have a designated conversion representative and just have all the people in the staff chain out-of order both along, but the buyers is more such as for example an online employee however, cannot perform someone. In this situation good workstation normally chain together with her some devices off a residential district pool such webcams and you can microphones, but the workstation itself can not be a father product. I believe the client-sales person desk framework may apply right here, I will opinion the software construction file to find out if you to definitely is reasonable.
- Proposed because answer by sdsuser Wednesday,
- Unproposed due to the fact respond to from the sdsuser Wednesday,
Less undetectable given that a trigger, it could performs. I can see how nHibernate likes they tomorrow, and my personal director.
- Modified by the Kalman Toth Friday,
I would personally start by adding a banner to help you dining table [NodeTypes], to suggest should your node type is engage since father or mother inside a romance.
Notice that And i am incorporating a different sort of constraint by (NodeTypeId, CanBeParent). This is certainly redundant, but it will allow me to later have fun with a different trick pointing so you’re able to it.
The next phase would be to incorporate that it column also to dining table [Nodes], to make sure that the couples (NodeTypeId, CanBeParent) can be found during the dining table [NodeTypes]. We are going to include a determined continued column (ParentNodeId_CanBeParent), which have value equal step one, to force [ParentNodeId] to join because moms and dad automatically, and you will reference dining table [Nodes] by using the partners (ParentNodeId, ParentNodeId_CanBePArent).
It might be easier in the event the were able to fool around with a select declaration in to the a restriction, or becoming capable create assertions (see limitation within database level), but T-SQL does not have of them possess as of today.
- 6:58 PM
There is no such thing as a “node_type_id” there can be a “
We avoid using meta data such as for instance “pk_” or “fk_” when you look at the study element name. The goal of a document feature name’s to inform united states what it is of the the characteristics and not the way it was getting used in this dining table.
When you use more-measurements of sequence like this, their desk have a tendency to complete with garbage. A beneficial coders play with records when the band of philosophy is actually high or volition. Like, an order detail UPC might source the Directory desk in order to demand a rule we just take instructions to have gift ideas in the stock.
If set try small and steady, upcoming i fool around with a (x When you look at the (..)) constants. Instance, “sex_password TINYINT Default 0 Not NULL Evaluate (sex_password Within the (0, step one, 2, 9)) — iso-5218
Would Table Nodes (node_id INTEGER Maybe not NULL Number one Key, node_kind of INTEGER Maybe not NULL Recommendations Node_Brands (node_type), parent_node_id INTEGER Sources Nodes (node_id));
That which you are creating is an enthusiastic adjacency number design. It imitates non-relational tip stores in SQL. It contains each other organizations (nodes) in addition to steps (relationship). But a correct cannot be one another (Chen? E-Roentgen acting? Freshman databases category in school?). Very although this is however an invalid research model, you should have written it as
Perform Desk Forest_and_Nodes (node_id INTEGER Maybe not NULL Number 1 Trick, node_sort of CHAR(1) Standard 'N’ Maybe not NULL Take a look at (node_enter in (’P’, 'N’), — P= You can Moms and dad, N= Non-Father or mother mother or father_node_id INTEGER Recommendations Tree_and_Nodes (node_id), View (Case Whenever node_sorts of = 'N’ And you can father or mother_node_id Isn’t NULL Up coming 'F’ Else 'T’ Stop = 'T’) );
–CELKO– Instructions inside Celko Collection to have Morgan-Kaufmann Posting: Analytics and you may OLAP inside SQL / Study and you can Databases: Basics in practice Research / Specifications and Requirements when you look at the SQL SQL to own Sming Concept / SQL Puzzles and you will Solutions / Considering in the Sets / Woods and you can Hierarchies into the SQL
Leave a Comments