I have both tables less than, how exactly to demand constaint one to Nodes which have NodeTypeId dos (CannotBeParent) can not be a pops instead of lead to?
- Edited by the gao.seng Wednesday,
Solutions
- PM
I would personally begin by including a banner so you can dining table [NodeTypes], to point should your node particular can also be participate since mother inside the a romance.
See that I am also incorporating a different restriction from the (NodeTypeId, CanBeParent). This might be redundant, but it enable us to afterwards explore a different key leading in order to they.
The next phase should be to put this column in order to dining table [Nodes], to make sure your couples (NodeTypeId, CanBeParent) exists within the table [NodeTypes]. We shall include a calculated carried on column (ParentNodeId_CanBeParent), with really worth equal step one, to force [ParentNodeId] to participate as the father or mother automatically, and you will reference dining table [Nodes] using the couples (ParentNodeId, ParentNodeId_CanBePArent).
It could be smoother in the event the managed to fool around with a select report in to the a constraint, or becoming in a position to perform assertions (glance at restriction at databases top), however, T-SQL does not have of these provides currently.
- 6:58 PM
All reactions
So how was Alex Kuznetsov when you require your? Alex came with constraintss so you can demand providers legislation most anybody manage simply imagine could be possible with causes.
But, the guy commonly reaches one by the addition of an auxillary line. Perhaps you can give even more record on which you are trying get to? That will to give motivation for some service.
If Exists (See Ideal 1 1 From NodeTypes Interior Sign up Nodes On the Nodes.NodeId = And you will Nodes.FK_NodeTypeId = NodeTypes.NodeTypeId And you may NodeTypes.NodeType = ‘CanBeParent’) Start Set = step one Stop Otherwise Initiate Set = 0 Prevent Return Avoid;
, NodeType varchar(255) perhaps not null ) perform dining table Nodes (NodeId int restriction PK__Nodes_NodeId No. 1 Trick , FK_NodeTypeId int constraint FK__NodeTypes_NodeTypeId references NodeTypes (NodeTypeId) , FK_ParentNodeid int restriction FK__Nodes_ParentNodeId__Nodes_NodeId sources Nodes (NodeId) View (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 as the address because of the sdsuser Wednesday,
I understand Alex a great deal towards SQLBlog I am able to search deeper there, you was best the greater I do believe about this consult the fresh new nearer I have so you can a solution. It’s sorts of including that have a customer that have an allocated conversion process associate and possess the people in this new staff strings out of order each other down and up, however the customers is more such as for example a virtual employee but can’t carry out anybody. In this instance good workstation is chain along with her a set of products off a residential district pond such as for example cameras and you can microphones, however the workstation alone can not be a grandfather unit. In my opinion the customer-sales professional desk construction could possibly get implement right here, I can comment the software program build document to find out if you to makes sense.
- Advised as answer of the sdsuser Wednesday,
- Unproposed once the respond to by sdsuser Wednesday,
- PM
Far less invisible as the a trigger, this may work. I could see how nHibernate likes it the next day, and you will my personal movie director.
- Modified because of the Kalman Toth Tuesday,
I would begin by incorporating a banner in order to dining table [NodeTypes], to point if your node type is also take part once the mother or father when you look at the a love.
Observe that I am also including a new restriction by the (NodeTypeId, CanBeParent). This will be redundant, however it enables us to after explore a different secret pointing to help you they.
The next thing should be to put this line and to dining table [Nodes], to be sure that few (NodeTypeId, CanBeParent) can be acquired inside the dining table [NodeTypes]. We’ll include a calculated continuing line (ParentNodeId_CanBeParent), that have value equal 1, to make [ParentNodeId] to become listed on as father or mother automagically, and you may resource desk [Nodes] utilizing the partners (ParentNodeId, ParentNodeId_CanBePArent).
It will be http://datingranking.net/nl/fetlife-overzicht convenient when the was able to have fun with a choose declaration to the a constraint, or becoming in a position to create assertions (look at constraint during the database peak), but T-SQL lacks of these provides to date.
- 6:58 PM
There is no such thing as a “node_type_id” there can be a “
We avoid using meta tags for example “pk_” or “fk_” inside the research function term. The reason for a data element name’s to share with us the goals by their character and never the way it is actually used in this desk.
When you use over-sized sequence in this way, your table usually complete having scrap. A beneficial coders fool around with references when the number of thinking are large or volition. Such as for instance, your order detail UPC you will source the brand new Inventory desk in order to impose a rule that people just take purchases getting gift ideas for the stock.
In the event that put is small and steady, following i have fun with a (x Inside (..)) constants. Such as, “sex_password TINYINT Standard 0 Not NULL Take a look at (sex_password In the (0, step one, 2, 9)) — iso-5218
Perform Dining table Nodes (node_id INTEGER Maybe not NULL Number 1 Trick, node_particular INTEGER Perhaps not NULL Sources Node_Products (node_type), parent_node_id INTEGER Sources Nodes (node_id));
What you have created is an adjacency record design. It mimics non-relational tip organizations during the SQL. It includes each other organizations (nodes) and steps (relationship). However, the correct can’t be one another (Chen? E-R modeling? Freshman databases class in school?). So while this is still an incorrect investigation design, you have composed it
Do Dining table Tree_and_Nodes (node_id INTEGER Not NULL First Key, node_particular CHAR(1) Standard ‘N’ Maybe not NULL Evaluate (node_input (‘P’, ‘N’), — P= You can Father or mother, N= Non-Mother father or mother_node_id INTEGER Recommendations Tree_and_Nodes (node_id), Evaluate (Situation When node_types of = ‘N’ And mother or father_node_id Is not NULL Following ‘F’ Otherwise ‘T’ Stop = ‘T’) );
–CELKO– Guides from inside the Celko Collection to have Morgan-Kaufmann Posting: Analytics and you will OLAP for the SQL / Analysis and you will Database: Basics used Studies / Specifications and you may Criteria during the SQL SQL to own Sming Layout / SQL Puzzles and you will Solutions / Thought within the Kits / Woods and you will Hierarchies inside the SQL