You will find both dining tables less than, simple tips to enforce constaint one to Nodes with NodeTypeId dos (CannotBeParent) can’t be a parent instead produce?
- Edited of the gao.seng Wednesday,
Solutions
- PM
I’d start by incorporating a flag in order to desk [NodeTypes], to point in the event the node types of is also engage because moms and dad in the a relationship.
See that And i am including an alternate limitation from the (NodeTypeId, CanBeParent). This might be redundant, nevertheless will allow me to later play with a different trick directing so you can they.
The next step would be to create which line in order to dining table [Nodes], to be sure your pair (NodeTypeId, CanBeParent) is present within the table [NodeTypes]. We will include a calculated carried on line (ParentNodeId_CanBeParent), which have value equal 1, to force [ParentNodeId] to become listed on given that mother by default, and you will reference dining table [Nodes] making use of the partners (ParentNodeId, ParentNodeId_CanBePArent).
It would be simpler if been able to explore a select declaration to the a restriction, or being able to do assertions (see constraint at the database level), however, T-SQL lacks ones keeps to date.
- 6:58 PM
Every answers
So how was Alex Kuznetsov when you need your? Alex have come up with constraintss so you can demand business laws most people would merely imagine was possible with trigger.
However, he usually reaches one by adding a keen auxillary column. Perhaps you will offer a few more background on which you are looking to go? That may to provide determination for some services.
In the event the Can be acquired (Look for Ideal 1 step one Off NodeTypes Inner Sign up Nodes On Nodes.NodeId = And you may Nodes.FK_NodeTypeId = NodeTypes.NodeTypeId And you can NodeTypes.NodeType = ‘CanBeParent’) Begin Place = 1 End Otherwise Start Place = 0 End Get back Avoid;
, NodeType varchar(255) not null ) create dining table Nodes (NodeId int limitation PK__Nodes_NodeId Top Trick , FK_NodeTypeId int restriction FK__NodeTypes_NodeTypeId references NodeTypes (NodeTypeId) , FK_ParentNodeid int limitation FK__Nodes_ParentNodeId__Nodes_NodeId recommendations 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
- Advised as the respond to by sdsuser Wednesday,
We see Alex a lot into the SQLBlog I could search better there, but you was right the greater I believe about this consult the new closer I have to help you an answer. It is version of instance having a buyers having an allocated sales agent and also all the people in the newest staff strings off command both up and down, however the customers is more particularly a virtual staff however, cannot would someone. In this instance a great workstation is also chain with her some equipment out of a residential district pool such as for example webcams and microphones, nevertheless workstation by itself cannot be a pops device. I do believe the customer-sales rep table construction could possibly get use right here, I can remark the program construction file to find out if that is reasonable.
- Suggested as answer by the sdsuser Wednesday,
- Unproposed as the respond to because of the sdsuser Wednesday,
- PM
Far less invisible because the a trigger, it could performs. I’m able to see how nHibernate loves it tomorrow, and you may my personal manager.
- Edited of the Kalman Toth Saturday,
I’d start by adding a banner in order to desk [NodeTypes], to indicate if the node variety of normally participate due to the fact moms and dad during the a romance.
See that I am also incorporating a separate limitation because of the (NodeTypeId, CanBeParent). It is redundant, but it enables me to after play with a foreign key directing to they.
The next step should be to incorporate which column and dining table [Nodes], to be certain your couple (NodeTypeId, CanBeParent) is present from inside the dining table [NodeTypes]. We’re going to include a computed continuing column (ParentNodeId_CanBeParent), having well worth equal 1, to force [ParentNodeId] to participate since the father or mother automatically, and resource table [Nodes] using the few (ParentNodeId, ParentNodeId_CanBePArent).
It will be much easier if were able to explore a select report into the a check constraint, or being able to manage assertions (consider constraint from the database top), but T-SQL does not have of these enjoys to date.
- 6:58 PM
There is no such thing as a “node_type_id” there can be a “
I never use meta tags including “pk_” otherwise “fk_” within the data element label. The purpose of a document feature name is to share with united states what it is by their character and never the way it try used in this particular table.
When you use over-measurements of sequence such as this, your own table tend to fill with scrap. Good coders play with references in the event the set of philosophy is actually high or volition. Such as feabie com-app for instance, an order outline UPC you will resource the new Index table in order to enforce a guideline we only take sales getting presents from inside the stock.
When the lay was smaller than average stable, up coming i explore a (x For the (..)) constants. For example, “sex_password TINYINT Standard 0 Perhaps not NULL Look at (sex_password When you look at the (0, 1, 2, 9)) — iso-5218
Perform Table Nodes (node_id INTEGER Perhaps not NULL Number 1 Key, node_sorts of INTEGER Not NULL Recommendations Node_Items (node_type), parent_node_id INTEGER Records Nodes (node_id));
Everything have created is an enthusiastic adjacency number design. It mimics low-relational pointer chains when you look at the SQL. It has one another entities (nodes) additionally the steps (relationship). But the correct cannot be each other (Chen? E-R modeling? Freshman database classification in school?). So although this is nevertheless an invalid data model, you’ll have authored it
Create Table Forest_and_Nodes (node_id INTEGER Perhaps not NULL No. 1 Key, node_kind of CHAR(1) Standard ‘N’ Perhaps not NULL Examine (node_enter in (‘P’, ‘N’), — P= Possible Moms and dad, N= Non-Mother or father mother or father_node_id INTEGER Recommendations Forest_and_Nodes (node_id), Have a look at (Case Whenever node_style of = ‘N’ And you can mother_node_id Is not NULL Up coming ‘F’ More ‘T’ End = ‘T’) );
–CELKO– Guides when you look at the Celko Collection to have Morgan-Kaufmann Publishing: Statistics and OLAP from inside the SQL / Analysis and you will Database: Axioms used Research / Dimensions and you may Standards inside the SQL SQL to have Sming Style / SQL Puzzles and you will Responses / Convinced inside the Set / Woods and you can Hierarchies within the SQL