We have the two tables below, simple tips to enforce constaint that Nodes which have NodeTypeId 2 (CannotBeParent) cannot be a grandfather instead of lead to?
- Edited because of the gao.seng Wednesday,
Solutions
- PM
I would personally begin by including a banner to dining table [NodeTypes], to suggest in the event your node style of normally engage once the parent during the a relationship.
Observe that I am also including a special constraint by (NodeTypeId, CanBeParent). It is redundant, nevertheless will allow us to after fool around with a foreign secret pointing in order to they.
The next phase is to try to include it line and dining table [Nodes], to be certain the partners (NodeTypeId, CanBeParent) can be found inside the desk [NodeTypes]. We shall also add a determined continuing line (ParentNodeId_CanBeParent), which have well worth equivalent step one, to force [ParentNodeId] to join while the mother or father by default, and you will resource dining table [Nodes] with the couple (ParentNodeId, ParentNodeId_CanBePArent).
It might be simpler in the event that were able to use a select report into the a constraint, or being able to create assertions (see constraint within databases height), but T-SQL does not have of them has to date.
- 6:58 PM
All answers
So where was Alex Kuznetsov when you need your? Alex came with constraintss to help you enforce company statutes very someone would merely envision would-be you can having produces.
But then, he will hits one with the addition of an enthusiastic auxillary line. Perchance you will offer some http://datingranking.net/nl/loveandseek-overzicht/ more background about what you’re seeking get to? Which could giving determination to some service.
In the event that Is obtainable (Come across Ideal step one 1 Regarding NodeTypes Interior Signup Nodes Into Nodes.NodeId = And Nodes.FK_NodeTypeId = NodeTypes.NodeTypeId And you can NodeTypes.NodeType = ‘CanBeParent’) Start Put = step one End Else Start Put = 0 Prevent Get back Avoid;
, NodeType varchar(255) maybe not null ) do desk Nodes (NodeId int restriction PK__Nodes_NodeId Top Key , FK_NodeTypeId int constraint FK__NodeTypes_NodeTypeId recommendations NodeTypes (NodeTypeId) , FK_ParentNodeid int restriction FK__Nodes_ParentNodeId__Nodes_NodeId sources Nodes (NodeId) Examine (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
- Advised because the respond to by sdsuser Wednesday,
I understand Alex a lot towards the SQLBlog I will enjoy deeper there, but you is actually best the greater I believe about this consult the brand new nearer I get to a remedy. It’s brand of such that have a customer that have an allocated transformation agent and get every members of new staff strings of order both along, nevertheless consumer is more like a virtual personnel however, cannot would anybody. In this instance a great workstation can also be chain together some gadgets out-of a residential area pool for example cams and you may microphones, nevertheless workstation itself can’t be a pops tool. I do believe the customer-sales rep table design get implement right here, I can opinion the software construction file to find out if one is practical.
- Suggested while the respond to from the sdsuser Wednesday,
- Unproposed given that answer of the sdsuser Wednesday,
- PM
Significantly less hidden since the a trigger, it may functions. I will observe nHibernate enjoys it the next day, and my personal director.
- Edited from the Kalman Toth Tuesday,
I would begin by adding a banner so you’re able to table [NodeTypes], to indicate in the event the node style of normally participate since mother in the a relationship.
Observe that And i am incorporating an alternate restriction from the (NodeTypeId, CanBeParent). That is redundant, however it enable us to later play with a different trick leading to help you it.
The next step would be to create so it column and also to desk [Nodes], to ensure that the partners (NodeTypeId, CanBeParent) is present for the dining table [NodeTypes]. We will also add a calculated continuous line (ParentNodeId_CanBeParent), which have value equal step 1, to force [ParentNodeId] to join as the father or mother automagically, and resource desk [Nodes] making use of the few (ParentNodeId, ParentNodeId_CanBePArent).
It might be smoother in the event that was able to explore a select statement into the a check restriction, or being capable perform assertions (check limitation within databases level), but T-SQL lacks ones provides currently.
- 6:58 PM
There is no such thing as a “node_type_id” there can be a “
We never use meta tags like “pk_” otherwise “fk_” during the data feature label. The reason for a document feature name is to tell all of us the goals from the its nature and not the way it was being used in this table.
If you are using over-measurements of string in this way, your own table often complete that have garbage. A programmers fool around with sources in the event the number of viewpoints was highest otherwise volition. Such as for example, an order detail UPC you will reference brand new List dining table to demand a tip that people only take sales to possess gift suggestions from inside the inventory.
In the event the put was smaller than average stable, next i explore a (x In (..)) constants. Such as for example, “sex_password TINYINT Standard 0 Not NULL Glance at (sex_code For the (0, 1, 2, 9)) — iso-5218
Do Desk Nodes (node_id INTEGER Perhaps not NULL No. 1 Secret, node_type INTEGER Perhaps not NULL Records Node_Models (node_type), parent_node_id INTEGER Sources Nodes (node_id));
What you have created is actually an enthusiastic adjacency list design. They imitates non-relational pointer chains within the SQL. It has each other organizations (nodes) and the ladder (relationship). But a proper can not be one another (Chen? E-Roentgen acting? Freshman database group at school?). Thus while this is nonetheless an invalid analysis model, you will have written it
Perform Desk Forest_and_Nodes (node_id INTEGER Maybe not NULL Top Key, node_type of CHAR(1) Default ‘N’ Perhaps not NULL Have a look at (node_input (‘P’, ‘N’), — P= Possible Mother or father, N= Non-Mother or father mother or father_node_id INTEGER References Forest_and_Nodes (node_id), Take a look at (Instance When node_style of = ‘N’ And you may parent_node_id Is not NULL Upcoming ‘F’ More ‘T’ Stop = ‘T’) );
–CELKO– Guides when you look at the Celko Show getting Morgan-Kaufmann Publishing: Analytics and you may OLAP inside the SQL / Study and you may Database: Basics in practice Investigation / Dimensions and you will Conditions when you look at the SQL SQL to possess Sming Build / SQL Puzzles and you can Answers / Thinking in the Set / Woods and you may Hierarchies in the SQL