08-726 78 68 info@mtco.se

This past trick is one checking one an effective NodeId can participate in a love given that a parent

I’ve both dining tables lower than, how to impose constaint that Nodes having NodeTypeId dos (CannotBeParent) cannot be a pops without cause?

  • Edited by the gao.seng Wednesday,


  • PM

I would begin by including a flag so you’re able to table [NodeTypes], to suggest when your node type can also be take part given that mother when you look at the a relationship.

Notice that And i am including a new constraint because of the (NodeTypeId, CanBeParent). That is redundant, but it enable us to afterwards play with a foreign trick directing in order to it.

The next thing is to put that it column also to dining table [Nodes], to ensure that couple (NodeTypeId, CanBeParent) is present into the desk [NodeTypes]. We will also add a determined continuous line (ParentNodeId_CanBeParent), with worthy of equivalent step 1, to force [ParentNodeId] to participate once the mother automatically, and you can source dining table [Nodes] making use of the pair (ParentNodeId, ParentNodeId_CanBePArent).

It will be much easier if the were able to have fun with a choose report to the a constraint, or being able to perform assertions (take a look at restriction during the databases top), however, T-SQL does not have of these has actually as of today.

  • 6:58 PM

The responses

So how is actually Alex Kuznetsov when you really need him? Alex attended up with constraintss so you’re able to enforce team laws and regulations extremely some body carry out just think would be you are able to that have trigger.

Then again, he have a tendency to achieves you to definitely by adding an enthusiastic auxillary column. Perhaps you offers a few more record on which you are trying to get to? Which could to give inspiration to a few provider.

When the Exists (Get a hold of Better step one step one Out-of NodeTypes Inner Join Nodes Toward Nodes.NodeId = And you can Nodes.FK_NodeTypeId = NodeTypes.NodeTypeId And NodeTypes.NodeType = ’CanBeParent’) Initiate Place = 1 Stop iamnaughty-login Otherwise Initiate Set = 0 Stop Return End;

, NodeType varchar(255) perhaps not null ) carry out desk Nodes (NodeId int limitation PK__Nodes_NodeId Number one Key , FK_NodeTypeId int constraint FK__NodeTypes_NodeTypeId recommendations NodeTypes (NodeTypeId) , FK_ParentNodeid int constraint FK__Nodes_ParentNodeId__Nodes_NodeId records 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

  • Recommended while the answer of the sdsuser Wednesday,

I comprehend Alex a lot with the SQLBlog I am able to dig deeper truth be told there, nevertheless is actually right the greater amount of I believe about any of it consult the fresh new better I get to a solution. It’s particular like which have a customers with an allocated sales agent while having every members of this new reps strings off demand one another down and up, but the customers is more such as for instance an online staff member but cannot manage someone. In this instance a workstation normally strings with her a set of products out of a community pool such cameras and you can microphones, nevertheless the workstation in itself can’t be a grandfather product. I think the client-sales representative dining table structure can get apply right here, I could feedback the application framework document to see if you to definitely is practical.

  • Proposed as the address because of the sdsuser Wednesday,
  • Unproposed due to the fact answer by sdsuser Wednesday,
  • PM

Significantly less hidden as a trigger, it could functions. I could observe nHibernate wants it the next day, and you can my personal movie director.

  • Edited from the Kalman Toth Monday,

I would personally start by including a banner in order to desk [NodeTypes], to suggest in case the node type of can also be participate because the mother or father during the a romance.

See that I am also including a unique restriction of the (NodeTypeId, CanBeParent). This really is redundant, but it will allow me to after have fun with a foreign key directing so you’re able to they.

The next thing would be to create that it line and to dining table [Nodes], to make certain that pair (NodeTypeId, CanBeParent) can be acquired in the table [NodeTypes]. We’ll also add a determined persisted column (ParentNodeId_CanBeParent), that have value equivalent step 1, to make [ParentNodeId] to become listed on since mother or father by default, and reference dining table [Nodes] utilizing the couples (ParentNodeId, ParentNodeId_CanBePArent).

It will be convenient if managed to play with a select statement inside a constraint, or becoming in a position to carry out assertions (glance at constraint at the database level), however, T-SQL lacks of them enjoys currently.

  • 6:58 PM

There is no such thing as a “node_type_id” there can be a “_type” or a “_id” in a correct data model but not a weird hybrid. An identifier is for entities (emp_id) a type encoding a scalar value (blood_type).

I never use meta data particularly “pk_” otherwise “fk_” from inside the investigation function identity. The intention of a data element name is to share with us the goals from the the character and never the way it try getting used in this desk.

If you are using over-size of sequence similar to this, the dining table tend to fill which have trash. An excellent programmers explore records when the number of viewpoints was high or volition. Such as for instance, an order detail UPC you will source the new Index dining table so you’re able to demand a tip we just take sales to own gift suggestions when you look at the stock.

If the set try smaller than average secure, after that i explore a check (x During the (..)) constants. Such, “sex_password TINYINT Standard 0 Perhaps not NULL Evaluate (sex_password During the (0, 1, dos, 9)) — iso-5218

Perform Desk Nodes (node_id INTEGER Perhaps not NULL No. 1 Key, node_type INTEGER Perhaps not NULL Recommendations Node_Products (node_type), parent_node_id INTEGER Records Nodes (node_id));

That which you have created are an enthusiastic adjacency checklist design. It imitates low-relational tip chains in the SQL. It contains both entities (nodes) additionally the steps (relationship). However, a correct can’t be both (Chen? E-Roentgen acting? Freshman databases classification in school?). Very while this is however an incorrect data design, you’ll have authored it as

Do Desk Forest_and_Nodes (node_id INTEGER Not NULL No. 1 Trick, node_kind of CHAR(1) Standard ’N’ Maybe not NULL Examine (node_enter in (’P’, ’N’), — P= You are able to Moms and dad, N= Non-Mother father or mother_node_id INTEGER Records Tree_and_Nodes (node_id), Take a look at (Instance Whenever node_style of = ’N’ And you may moms and dad_node_id Is not NULL After that ’F’ Else ’T’ Stop = ’T’) );

–CELKO– Instructions within the Celko Show having Morgan-Kaufmann Publishing: Analytics and you may OLAP inside the SQL / Investigation and you may Database: Rules in practice Data / Measurements and you may Criteria during the SQL SQL to possess Sming Concept / SQL Puzzles and you will Solutions / Convinced from inside the Sets / Trees and you can Hierarchies for the SQL