I’ve the two tables less than, how to demand constaint you to definitely Nodes with NodeTypeId 2 (CannotBeParent) cannot be a daddy without cause?
- Edited because of the gao.seng Wednesday,
Solutions
- PM
I would personally start with including a flag so you’re able to desk [NodeTypes], to indicate in case the node types of is take part given that mother or father when you look at the a relationship.
See that I am also incorporating a unique constraint from the (NodeTypeId, CanBeParent). This can be redundant, nonetheless it enable us to later on fool around with a foreign trick pointing so you can they.
The next step is to try to incorporate it column and table [Nodes], to make certain that the pair (NodeTypeId, CanBeParent) is available from inside the dining table [NodeTypes]. We are going to also add a computed proceeded column (ParentNodeId_CanBeParent), having well worth equal step 1, to force [ParentNodeId] to become listed on because the mother automatically, and you may resource dining table [Nodes] making use of the couples (ParentNodeId, ParentNodeId_CanBePArent).
It would be convenient in the event that were able to play with a choose declaration into the a check constraint, or becoming able to create assertions (check constraint at the databases peak), but T-SQL does not have of those enjoys to date.
- 6:58 PM
All replies
Where is Alex Kuznetsov when you require him? Alex came with constraintss to help you enforce company rules most anybody do only think might be you’ll that have produces.
However, he tend to achieves that adding an auxillary line. Maybe you can give some more record on which you’re trying go? Which could supply motivation to a few service.
When the Exists (Come korean cupid-promotiecodes across Best 1 step one Regarding NodeTypes Inner Register Nodes On Nodes.NodeId = And you can Nodes.FK_NodeTypeId = NodeTypes.NodeTypeId And you will NodeTypes.NodeType = ‘CanBeParent’) Begin Lay = step 1 End Otherwise Begin Put = 0 Prevent Get back End;
, NodeType varchar(255) not null ) would table Nodes (NodeId int restriction PK__Nodes_NodeId First Key , FK_NodeTypeId int limitation FK__NodeTypes_NodeTypeId recommendations NodeTypes (NodeTypeId) , FK_ParentNodeid int constraint FK__Nodes_ParentNodeId__Nodes_NodeId recommendations Nodes (NodeId) Have a look 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
- Recommended once the respond to by sdsuser Wednesday,
I discover Alex much to the SQLBlog I can look higher there, however you are correct more I do believe about any of it consult this new better I get so you can an answer. It is sorts of particularly which have a customers with an assigned sales representative and also the members of this new representatives strings out of demand both up-and-down, nevertheless buyers is far more such as for example an online staff member however, are unable to manage somebody. In this situation a workstation is also chain with her some gizmos away from a residential district pond particularly webcams and you will microphones, nevertheless the workstation by itself can not be a father device. I think the client-salesperson table structure could possibly get pertain right here, I could comment the software program construction file to find out if one to is practical.
- Proposed since the answer because of the sdsuser Wednesday,
- Unproposed due to the fact answer by sdsuser Wednesday,
- PM
Not as hidden while the a trigger, it might functions. I will observe how nHibernate likes they tomorrow, and you may my manager.
- Modified because of the Kalman Toth Tuesday,
I’d start by incorporating a banner to help you desk [NodeTypes], to indicate in the event your node type of can participate because the mother or father in a love.
Notice that I am also adding a separate constraint because of the (NodeTypeId, CanBeParent). That is redundant, but it allows us to afterwards fool around with a different secret pointing to it.
The next step should be to incorporate that it line and to table [Nodes], to make sure your partners (NodeTypeId, CanBeParent) is available in dining table [NodeTypes]. We’ll include a calculated continuous line (ParentNodeId_CanBeParent), that have value equivalent step one, to force [ParentNodeId] to join because father or mother automagically, and resource desk [Nodes] with the partners (ParentNodeId, ParentNodeId_CanBePArent).
It might be simpler if the was able to explore a select statement inside a constraint, or becoming in a position to do assertions (have a look at restriction from the databases peak), but T-SQL does not have of them provides to date.
- 6:58 PM
There is no such thing as a “node_type_id” there can be a “
We never use meta data such as for instance “pk_” or “fk_” inside the study feature label. The goal of a document function name is to tell united states the goals by their character and not how it try used in this particular dining table.
By using more than-measurements of sequence similar to this, the desk often complete with rubbish. A beneficial programmers have fun with sources when the band of philosophy was large or volition. For example, an order outline UPC might reference the newest Inventory dining table to impose a rule that individuals just take sales to own gift ideas inside the inventory.
If the set is small and secure, upcoming we play with a check (x From inside the (..)) constants. Instance, “sex_password TINYINT Standard 0 Maybe not NULL Have a look at (sex_code In (0, step one, dos, 9)) — iso-5218
Do Table Nodes (node_id INTEGER Not NULL Primary Key, node_style of INTEGER Perhaps not NULL References Node_Types (node_type), parent_node_id INTEGER Sources Nodes (node_id));
Everything are creating is an enthusiastic adjacency checklist design. They mimics low-relational tip organizations within the SQL. It contains both agencies (nodes) as well as the hierarchy (relationship). However, a correct can’t be each other (Chen? E-Roentgen modeling? Freshman database class in school?). Very while this is nonetheless an incorrect research design, you have created it as
Do Desk Tree_and_Nodes (node_id INTEGER Not NULL Number one Secret, node_style of CHAR(1) Standard ‘N’ Maybe not NULL See (node_input (‘P’, ‘N’), — P= You can Mother or father, N= Non-Mother parent_node_id INTEGER Sources Tree_and_Nodes (node_id), Glance at (Case When node_sort of = ‘N’ And you may moms and dad_node_id Isn’t NULL Next ‘F’ Else ‘T’ Avoid = ‘T’) );
–CELKO– Instructions from inside the Celko Collection for Morgan-Kaufmann Posting: Statistics and OLAP when you look at the SQL / Studies and you may Database: Rules used Analysis / Proportions and you will Standards during the SQL SQL to possess Sming Layout / SQL Puzzles and you may Responses / Thought from inside the Kits / Trees and you can Hierarchies from inside the SQL
