We have the 2 tables below, just how to impose constaint you to Nodes with NodeTypeId 2 (CannotBeParent) can not be a daddy in the place of cause?
- Modified of the gao.seng Wednesday,
Responses
- PM
I’d start with adding a flag so you can desk [NodeTypes], to indicate if your node method of can also be engage since mother in a relationship.
Notice that And i am adding a new constraint by (NodeTypeId, CanBeParent). That is redundant, nevertheless will allow us to later fool around with a different key pointing in order to they.
The next step is to create which line and to desk [Nodes], to make sure that couples (NodeTypeId, CanBeParent) is present inside table [NodeTypes]. We are going to also add a calculated continuous line (ParentNodeId_CanBeParent), with value equivalent 1, to make [ParentNodeId] to join as the moms and dad automatically, and you can resource desk [Nodes] making use of the few (ParentNodeId, ParentNodeId_CanBePArent).
It will be simpler if were able to have fun with a choose report to the a limitation, or being in a position to perform assertions (view limitation from the database height), but T-SQL does not have of those enjoys to date.
- 6:58 PM
All the reactions
So where are Alex Kuznetsov when you require your? Alex came with constraintss so you can impose organization rules extremely some one carry out only thought could well be you’ll be able to with leads to.
But, he will hits that by adding an auxillary line. Perhaps you can give even more history on what you are trying achieve? That will to give determination for some services.
If the Can be acquired (Select Ideal step one step one From NodeTypes Internal Sign up Nodes Towards the Nodes.NodeId = And you may Nodes.FK_NodeTypeId = NodeTypes.NodeTypeId And you can NodeTypes.NodeType = ‘CanBeParent’) Initiate Put = step 1 Stop Else Initiate Place = 0 End Return Avoid;
, NodeType varchar(255) not null ) carry out desk Nodes (NodeId int restriction PK__Nodes_NodeId First Trick , FK_NodeTypeId int restriction FK__NodeTypes_NodeTypeId sources NodeTypes (NodeTypeId) , FK_ParentNodeid int restriction FK__Nodes_ParentNodeId__Nodes_NodeId records Nodes (NodeId) Consider (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
- Suggested because address by the sdsuser Wednesday,
I read Alex a lot toward SQLBlog I’m able to look greater there, but you is right the greater number of I believe about it request the closer I get in order to an answer. It’s type of such as for instance which have a customer with a designated transformation associate and also every members of the brand new staff chain of command one another along, although buyers is much more including an online employee however, can’t create individuals. In this situation a good workstation can chain together with her a collection of gadgets off a community pond such as for example cameras and microphones, however the workstation by itself can’t be a father unit. I believe the customer-sales rep dining table framework may implement right here, I’m able to review the software construction file to find out if that is practical.
- Proposed given that answer because of the sdsuser Wednesday,
- Unproposed given that address of the sdsuser Wednesday,
- PM
Notably less invisible because the a cause, it could really works. I will see how nHibernate wants they tomorrow, and my director.
- Edited by Kalman Toth Monday,
I would start with including a flag in order to dining table [NodeTypes], to indicate should your node sort of is participate since mother from inside the a relationship.
Observe that And i am including a unique limitation because of the (NodeTypeId, CanBeParent). That is redundant, nevertheless will allow us to later on fool around with a different trick leading to help you they.
The next phase is always to create that it column and table [Nodes], to ensure your pair (NodeTypeId, CanBeParent) can be found into the dining table [NodeTypes]. We’re going to include a calculated persisted column (ParentNodeId_CanBeParent), having well worth equivalent step one, to make [ParentNodeId] to participate as the mother or father automagically, and you can reference table [Nodes] utilizing the partners (ParentNodeId, ParentNodeId_CanBePArent).
It will be convenient when the were able to explore a choose report in to the a check restriction, or being capable would assertions (glance at restriction on databases peak), however, T-SQL lacks of those keeps as of today.
- 6:58 PM
There how to see who likes you on blackpeoplemeet without paying is no such thing as a “node_type_id” there can be a “
We avoid using meta tags eg “pk_” or “fk_” when you look at the research ability identity. The intention of a data feature name’s to tell united states what it is by their nature and not how it is used in this particular table.
When you use more-sized string along these lines, your own dining table will complete having scrap. An excellent programmers explore records in the event the band of philosophy is actually higher otherwise volition. Particularly, your order detail UPC you’ll site the new Index desk so you’re able to enforce a tip that we just take requests to have gift ideas from inside the inventory.
In the event the set is actually small and secure, upcoming we use a (x Inside (..)) constants. Such, “sex_code TINYINT Standard 0 Perhaps not NULL Check (sex_password Inside the (0, step 1, 2, 9)) — iso-5218
Would Table Nodes (node_id INTEGER Not NULL Number one Key, node_kind of INTEGER Not NULL Records Node_Brands (node_type), parent_node_id INTEGER References Nodes (node_id));
What you are creating are an adjacency checklist design. It imitates low-relational pointer stores in the SQL. It includes one another organizations (nodes) and ladder (relationship). But a proper can not be one another (Chen? E-R modeling? Freshman databases group in school?). So although this is nevertheless an invalid investigation design, you’ll have composed it as
Carry out Desk Forest_and_Nodes (node_id INTEGER Maybe not NULL Number 1 Key, node_variety of CHAR(1) Standard ‘N’ Maybe not NULL Consider (node_input (‘P’, ‘N’), — P= You are able to Moms and dad, N= Non-Mother moms and dad_node_id INTEGER Records Tree_and_Nodes (node_id), Take a look at (Circumstances Whenever node_particular = ‘N’ And you may mother_node_id Is not NULL Next ‘F’ Otherwise ‘T’ Avoid = ‘T’) );
–CELKO– Instructions during the Celko Series to own Morgan-Kaufmann Publishing: Analytics and OLAP into the SQL / Research and you will Database: Principles in practice Studies / Proportions and you will Conditions from inside the SQL SQL to possess Sming Style / SQL Puzzles and you can Answers / Thinking when you look at the Kits / Woods and you can Hierarchies for the SQL
