We have the two tables lower than, just how to impose constaint that Nodes that have NodeTypeId 2 (CannotBeParent) can not be a grandfather instead cause?
- Edited by gao.seng Wednesday,
Answers
- PM
I would personally begin by including a flag to dining table [NodeTypes], to point if the node kind of can also be take part given that mother inside a romance.
Notice that And i am including a separate restriction by (NodeTypeId http://datingranking.net/nl/grindr-overzicht/, CanBeParent). That is redundant, it will allow us to later fool around with a different trick leading to help you they.
The next phase is to try to incorporate it line and dining table [Nodes], to make certain that pair (NodeTypeId, CanBeParent) can be found inside table [NodeTypes]. We will also add a determined carried on column (ParentNodeId_CanBeParent), which have worthy of equal step one, to force [ParentNodeId] to participate because father or mother automatically, and you can site dining table [Nodes] by using the couple (ParentNodeId, ParentNodeId_CanBePArent).
It could be easier when the been able to fool around with a choose declaration in to the a check limitation, or becoming able to would assertions (examine constraint within databases level), however, T-SQL does not have ones enjoys to date.
- 6:58 PM
Most of the replies
So where was Alex Kuznetsov when you need him? Alex attended up with constraintss in order to demand company legislation very anybody manage just envision could well be you can easily with causes.
But, he commonly hits one adding an enthusiastic auxillary line. Perchance you will provide more background on which you are trying to reach? That’ll to provide inspiration for some solution.
If the Can be obtained (Look for Greatest 1 step 1 Out of NodeTypes Interior Subscribe Nodes To the Nodes.NodeId = And Nodes.FK_NodeTypeId = NodeTypes.NodeTypeId And NodeTypes.NodeType = ‘CanBeParent’) Start Lay = step 1 End Else Start Put = 0 Stop Come back Prevent;
, NodeType varchar(255) not null ) manage dining table Nodes (NodeId int restriction PK__Nodes_NodeId Top Key , FK_NodeTypeId int constraint FK__NodeTypes_NodeTypeId records NodeTypes (NodeTypeId) , FK_ParentNodeid int restriction FK__Nodes_ParentNodeId__Nodes_NodeId recommendations Nodes (NodeId) Glance 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
- Suggested since address of the sdsuser Wednesday,
We discover Alex a lot into the SQLBlog I’m able to search better here, however is actually proper the greater In my opinion regarding it consult the nearer I get to a solution. It’s variety of for example with a consumer that have an allocated conversion process agent and just have all of the people in the latest representatives chain of order both up and down, nevertheless customers is more including a virtual staff member but can not do anybody. In cases like this a workstation can strings along with her a collection of gadgets off a residential district pond including webcams and microphones, but the workstation alone cannot be a father unit. I believe the customer-sales rep dining table framework will get implement here, I can comment the software program framework file to see if one to makes sense.
- Proposed because respond to because of the sdsuser Wednesday,
- Unproposed once the respond to from the sdsuser Wednesday,
- PM
Notably less invisible due to the fact a cause, it might performs. I am able to find out how nHibernate loves they tomorrow, and you may my director.
- Modified of the Kalman Toth Tuesday,
I might begin by incorporating a flag in order to desk [NodeTypes], to suggest if the node kind of can engage because the father or mother from inside the a relationship.
Notice that And i am including an alternate limitation from the (NodeTypeId, CanBeParent). This is certainly redundant, nevertheless will allow me to afterwards explore a foreign secret pointing so you’re able to it.
The next thing is to try to put this line also to table [Nodes], to make certain that few (NodeTypeId, CanBeParent) is available when you look at the dining table [NodeTypes]. We’re going to include a computed carried on line (ParentNodeId_CanBeParent), with well worth equivalent 1, to make [ParentNodeId] to join because father or mother automagically, and you can site dining table [Nodes] by using the few (ParentNodeId, ParentNodeId_CanBePArent).
It could be much easier if was able to have fun with a choose report into the a check limitation, or being able to manage assertions (check limitation during the databases level), but T-SQL does not have ones features to date.
- 6:58 PM
There is no such thing as a “node_type_id” there can be a “
We avoid using meta data particularly “pk_” or “fk_” for the data ability identity. The purpose of a document feature name is to tell us the goals by the characteristics and not how it try used in this desk.
If you are using over-sized sequence like this, the dining table have a tendency to fill that have garbage. An effective coders fool around with recommendations when the set of philosophy is high otherwise volition. For example, your order outline UPC you’ll resource the new Catalog table so you can impose a rule we just take orders for gift ideas within the inventory.
When the place try smaller than average steady, then we have fun with a check (x Inside the (..)) constants. Such as for instance, “sex_password TINYINT Standard 0 Maybe not NULL See (sex_code In the (0, 1, 2, 9)) — iso-5218
Carry out Dining table Nodes (node_id INTEGER Perhaps not NULL Top Secret, node_method of INTEGER Not NULL Sources Node_Items (node_type), parent_node_id INTEGER Recommendations Nodes (node_id));
Everything you have created was a keen adjacency record design. It mimics non-relational tip chains inside SQL. It includes both agencies (nodes) as well as the hierarchy (relationship). However, a correct can not be each other (Chen? E-Roentgen modeling? Freshman databases class in school?). Thus although this is nevertheless an invalid data design, you will have authored it
Do Dining table Forest_and_Nodes (node_id INTEGER Maybe not NULL First Key, node_style of CHAR(1) Standard ‘N’ Not NULL See (node_type in (‘P’, ‘N’), — P= You are able to Mother, N= Non-Moms and dad mother or father_node_id INTEGER Records Forest_and_Nodes (node_id), Take a look at (Instance When node_types of = ‘N’ And parent_node_id Is not NULL Following ‘F’ More ‘T’ Avoid = ‘T’) );
–CELKO– Books inside the Celko Show for Morgan-Kaufmann Publishing: Statistics and you can OLAP from inside the SQL / Study and you can Database: Axioms in practice Analysis / Measurements and you can Requirements in the SQL SQL to own Sming Style / SQL Puzzles and Responses / Thinking when you look at the Kits / Woods and you may Hierarchies within the SQL
