I have both tables lower than, how-to impose constaint that Nodes with NodeTypeId 2 (CannotBeParent) can’t be a grandfather instead of result in?
- Edited from the gao.seng Wednesday,
Solutions
- PM
I would personally start by adding a banner so you’re able to desk [NodeTypes], to suggest should your node type of can be engage since the parent into the a romance.
Observe that I am also including a different sort of limitation because of the (NodeTypeId, CanBeParent). That is redundant, but it enables us to later fool around with a different key leading so you can it.
The next phase is to put that it column in order to desk [Nodes], to be sure that couples (NodeTypeId, CanBeParent) can be found inside the dining table [NodeTypes]. We are going to include a calculated continued column (ParentNodeId_CanBeParent), which have value equal step 1, to force [ParentNodeId] to become listed on since the mother automatically, and you may resource table [Nodes] with the pair (ParentNodeId, ParentNodeId_CanBePArent).
It will be simpler if the been able to use a select declaration in to the a check limitation, or being able to do assertions (evaluate constraint at the databases peak), but T-SQL does not have of these possess currently.
- 6:58 PM
Every answers
So where are Alex Kuznetsov if you want your? Alex attended up with constraintss in order to enforce providers guidelines really people carry out just thought will be you can easily that have causes.
Then again, he usually achieves that with the addition of a keen auxillary line. Maybe you offers a few more records on what you’re looking to get to? That could provide motivation for some provider.
In the event that Is obtainable (Discover Best step one step one Out-of NodeTypes Inner Signup Nodes Into the Nodes.NodeId = And you may Nodes.FK_NodeTypeId = NodeTypes.NodeTypeId And you may NodeTypes.NodeType = ‘CanBeParent’) Begin Lay = step one Prevent Else Start Lay = 0 Avoid Come back End;
, NodeType varchar(255) maybe not null ) do dining table Nodes (NodeId int limitation PK__Nodes_NodeId First Secret , FK_NodeTypeId int limitation FK__NodeTypes_NodeTypeId recommendations NodeTypes (NodeTypeId) , FK_ParentNodeid int limitation 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
- Recommended as the respond to because of the sdsuser Wednesday,
I read Alex a great deal on the SQLBlog I will search higher there, however you is actually right the greater amount of In my opinion about it request brand new better I get to help you a simple solution. It’s kind of including which have a consumer that have a designated conversion rep and also all members of the new representatives chain from demand each other down and up, but the customer is more instance an online employee however, cannot create some body. In cases like this an effective workstation is chain along with her some gadgets out of a community pond such adult cams and you will microphones, although workstation by itself cannot be a father tool. I do believe the customer-sales professional desk framework may use here, I am able to opinion the application construction file to find out if you to makes sense.
- Advised because the respond to from the sdsuser Wednesday,
- Unproposed since the answer of the sdsuser Wednesday,
- PM
A lot less invisible since the a trigger, it might works. I can observe how nHibernate likes they the next day, and you may my director.
- Edited by the Kalman Toth Saturday,
I’d start by including a banner to help you dining table [NodeTypes], to suggest should your node particular is also engage because the mother or father in a romance.
Observe that And i am incorporating another limitation angelreturn mobile site by (NodeTypeId, CanBeParent). This really is redundant, but it enables me to afterwards have fun with a foreign key leading so you can they.
The next phase would be to add this line and to dining table [Nodes], to be sure the partners (NodeTypeId, CanBeParent) can be found in the dining table [NodeTypes]. We’ll include a determined persisted column (ParentNodeId_CanBeParent), having value equivalent 1, to make [ParentNodeId] to join while the mother automatically, and reference desk [Nodes] with the couple (ParentNodeId, ParentNodeId_CanBePArent).
It could be much easier if the were able to fool around with a select report into the a constraint, or becoming able to would assertions (look at constraint on databases height), but T-SQL does not have of those keeps to date.
- 6:58 PM
There is no such thing as a “node_type_id” there can be a “
I never use meta tags such as for instance “pk_” otherwise “fk_” for the data ability identity. The goal of a document feature name is to tell united states the goals from the their characteristics and not how it was getting used in this particular table.
By using more-size of string along these lines, your own table tend to fill that have scrap. An excellent coders use sources if the gang of beliefs are highest or volition. Instance, your order detail UPC you’ll resource the newest List desk to impose a tip that we just take orders getting gift suggestions for the inventory.
In the event that lay was smaller than average stable, up coming we play with a (x Inside the (..)) constants. Including, “sex_password TINYINT Standard 0 Maybe not NULL View (sex_password Within the (0, 1, 2, 9)) — iso-5218
Perform Dining table Nodes (node_id INTEGER Not NULL No. 1 Secret, node_variety of INTEGER Maybe not NULL Records Node_Brands (node_type), parent_node_id INTEGER Records Nodes (node_id));
Everything you are creating is a keen adjacency list design. They mimics low-relational tip organizations for the SQL. It includes one another agencies (nodes) as well as the hierarchy (relationship). However, a correct cannot be one another (Chen? E-Roentgen modeling? Freshman database class at school?). Very while this is however an invalid investigation design, you have created it as
Manage Table Tree_and_Nodes (node_id INTEGER Perhaps not NULL Number one Trick, node_type of CHAR(1) Default ‘N’ Not NULL Check (node_input (‘P’, ‘N’), — P= You can easily Father or mother, N= Non-Moms and dad mother or father_node_id INTEGER Records Tree_and_Nodes (node_id), Evaluate (Instance When node_sorts of = ‘N’ And you may moms and dad_node_id Is not NULL After that ‘F’ Else ‘T’ End = ‘T’) );
–CELKO– Instructions for the Celko Collection for Morgan-Kaufmann Publishing: Statistics and you may OLAP inside SQL / Research and Databases: Principles in practice Investigation / Proportions and you can Standards in SQL SQL to own Sming Design / SQL Puzzles and you will Answers / Thought into the Set / Trees and you can Hierarchies inside SQL
