We have the 2 dining tables lower than, tips demand constaint that Nodes with NodeTypeId dos (CannotBeParent) can not be a daddy instead of lead to?
- Edited by the gao.seng Wednesday,
Solutions
- PM
I would personally start with incorporating a flag in order to dining table [NodeTypes], to point if for example the node types of is engage since father or mother when you look at the a relationship.
Observe that I am also including yet another constraint of the (NodeTypeId, CanBeParent). This can be redundant, nevertheless allows me to after have fun with a foreign secret pointing so you can it.
The next phase is always to include which line and to desk [Nodes], to make certain that couple (NodeTypeId, CanBeParent) is obtainable inside desk [NodeTypes]. We are going to also add a calculated persisted column (ParentNodeId_CanBeParent), which have well worth equal step 1, to make [ParentNodeId] to become listed on while the father or mother by default, and site dining table [Nodes] by using the couples (ParentNodeId, ParentNodeId_CanBePArent).
It would be easier if the managed to fool around with a select report in to the a restriction, or being capable carry out assertions (take a look at constraint at databases peak), but T-SQL lacks of them provides currently.
- 6:58 PM
All the reactions
So where try Alex Kuznetsov when you require him? Alex attended up with constraintss in order to demand team laws really somebody do simply imagine is possible having produces.
Then again, he commonly hits one with the addition of a keen auxillary column. Maybe you can give a few more records on what you’re seeking achieve? Which will supply determination to some services.
If Is available (Find Better step one step one Off NodeTypes Inner Subscribe Nodes Toward Nodes.NodeId = And you will Nodes.FK_NodeTypeId = NodeTypes.NodeTypeId And you will NodeTypes.NodeType = ‘CanBeParent’) Initiate Put = step 1 End More Initiate Place = 0 Avoid Come back Avoid;
, NodeType varchar(255) perhaps not null ) would table Nodes (NodeId int limitation PK__Nodes_NodeId First Secret , FK_NodeTypeId int restriction FK__NodeTypes_NodeTypeId sources NodeTypes (NodeTypeId) , FK_ParentNodeid int restriction FK__Nodes_ParentNodeId__Nodes_NodeId references Nodes (NodeId) Evaluate (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 by sdsuser Wednesday,
We realize Alex a great deal to your SQLBlog I could look higher here, nevertheless was proper the greater amount of In my opinion about this demand brand new nearer I have so you can a remedy. It’s type of for example which have a customer that have an allocated conversion rep and get most of the members of brand new staff chain out-of order one another up-and-down, although consumer is much more like a virtual staff but are unable to perform someone. In this instance a workstation can strings along with her some products out-of a residential area pond particularly adult cams and you will microphones, nevertheless the workstation by itself can’t be a parent tool. I think the client-sales agent table structure get apply right here, I could review the software build file to find out if one to makes sense.
- Advised once the answer of the sdsuser Wednesday,
- Unproposed since the answer by the sdsuser Wednesday,
- PM
Much less invisible since the a trigger, this may performs. I am able to see how nHibernate likes they tomorrow, and you will my personal manager.
- Edited from the Kalman Toth Monday,
I would begin by incorporating a banner so you’re able to desk [NodeTypes], to point if for example the node sort of can also be take part since the parent from inside the a romance.
Notice that And i am including a separate constraint from the (NodeTypeId, CanBeParent). This really is redundant, it enables me to after play with a foreign key pointing to help you they.
The next thing would be to incorporate that it column and to table [Nodes], to make certain that few (NodeTypeId, CanBeParent) can be acquired inside the table [NodeTypes]. We shall include a computed continued line (ParentNodeId_CanBeParent), with worthy of equal 1, to make [ParentNodeId] to participate since the parent automatically, and reference desk [Nodes] utilizing the couples (ParentNodeId, ParentNodeId_CanBePArent).
It could be simpler if been able to play with a select statement into the a limitation, or being capable carry out assertions (examine restriction at database height), however, T-SQL does not have of them possess as of today.
- 6:58 PM
There is no such thing as a “node_type_id” there can be a “
We avoid using meta tags such as for example “pk_” or “fk_” in investigation feature name. The reason for a data function name is to tell you the goals of the their character and not how it are getting used in this dining table.
When you use more-measurements of string similar to this, their dining table tend to fill that have rubbish. A beneficial coders use recommendations in the event the selection of viewpoints is high or volition. Like, your order detail UPC you will reference the newest Collection desk so you’re able to impose a tip that we only take instructions having presents when you look at the inventory.
In the event that set is actually smaller than average stable, up coming we fool around with a check (x In the (..)) constants. Eg, “sex_password TINYINT Standard 0 Perhaps not NULL Check (sex_code Within the (0, step 1, 2, 9)) — iso-5218
Would Table Nodes (node_id INTEGER Maybe not NULL First Secret, node_form of INTEGER Maybe not NULL Sources Node_Sizes (node_type), parent_node_id INTEGER References Nodes (node_id));
What you are creating is an adjacency listing model. It imitates low-relational tip stores during the SQL. It includes both organizations (nodes) and steps (relationship). However, a proper can not be one another (Chen? E-Roentgen modeling? Freshman database category at school?). So while this is nevertheless an incorrect research design, you will have written it as
Create Dining table Forest_and_Nodes (node_id INTEGER Perhaps not NULL First Key, node_variety of CHAR(1) Standard ‘N’ Maybe not NULL Take a look at (node_type in (‘P’, ‘N’), — P= You’ll be able to Moms and dad, N= Non-Moms and hot or not reddit dad parent_node_id INTEGER Records Forest_and_Nodes (node_id), View (Instance Whenever node_sort of = ‘N’ And you can father or mother_node_id Is not NULL After that ‘F’ Else ‘T’ Prevent = ‘T’) );
–CELKO– Guides from inside the Celko Collection to own Morgan-Kaufmann Publishing: Statistics and you will OLAP when you look at the SQL / Study and Databases: Principles in practice Data / Measurements and you may Criteria into the SQL SQL to have Sming Design / SQL Puzzles and you may Responses / Convinced inside Kits / Woods and you may Hierarchies when you look at the SQL