We have both tables less than, just how to impose constaint one to Nodes that have NodeTypeId dos (CannotBeParent) can’t be a pops in place of bring about?
- Modified by the gao.seng Wednesday,
Solutions
- PM
I would personally start by adding a flag to dining table [NodeTypes], to indicate when your node types of can take part because father or mother for the a love.
Observe that And i am adding an alternative restriction by (NodeTypeId, CanBeParent). This can be redundant, it enable us to later have fun with a different key pointing to it.
The next phase is always to create which column and dining table [Nodes], to be certain the partners (NodeTypeId, CanBeParent) is available when you look at the desk [NodeTypes]. We shall include a computed carried on column (ParentNodeId_CanBeParent), that have really worth equivalent 1, to force [ParentNodeId] to become listed on since father or mother automatically, and you can reference dining table [Nodes] with the couple (ParentNodeId, ParentNodeId_CanBePArent).
It might be simpler in the event the managed to have fun with a choose statement in to the a check restriction, or being capable create assertions (glance at restriction within databases top), however, T-SQL does not have of these has actually currently.
- 6:58 PM
The responses
So how is Alex Kuznetsov when you really need him? Alex attended up with constraintss in order to enforce providers regulations very individuals manage merely imagine would-be you’ll be able to which have causes.
But, he will achieves that adding an enthusiastic auxillary line. Perhaps you gives even more background on what you are trying achieve? That’ll giving motivation to some service.
When the Can be acquired (Pick Greatest step 1 step 1 Out-of NodeTypes Internal Sign-up Nodes Towards the Nodes.NodeId = And Nodes.FK_NodeTypeId = NodeTypes.NodeTypeId And you may NodeTypes.NodeType = ‘CanBeParent’) Begin Place = 1 Prevent More Begin Set = 0 End Come back Stop;
, NodeType varchar(255) not null ) do table Nodes (NodeId int constraint PK__Nodes_NodeId Top Secret , FK_NodeTypeId int limitation FK__NodeTypes_NodeTypeId sources NodeTypes (NodeTypeId) , FK_ParentNodeid int limitation FK__Nodes_ParentNodeId__Nodes_NodeId references Nodes (NodeId) 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
- Suggested because the address from the sdsuser Wednesday,
I see Alex a lot towards SQLBlog I am able to enjoy higher truth be told there, nevertheless try correct the greater number of I do believe about this consult the fresh better I have to an answer. It is form of including which have a customer that have an assigned conversion process associate and then have all people in the newest representatives strings out-of demand both top to bottom, however the buyers is much more including a virtual worker but are unable to perform anyone. In this situation a good workstation can be chain together with her a set of gadgets of a residential district pond such as for example webcams and you may microphones, although workstation in itself cannot be a parent unit. I think the customer-sales person dining table structure may apply right here, I am able to feedback the software program structure document to see if that is sensible.
- Proposed since answer by sdsuser Wednesday,
- Unproposed given that respond to because of the sdsuser Wednesday,
- PM
Far less hidden due to the fact a cause, it could works. I’m able to observe how nHibernate loves they the next day, and you will my manager.
- Modified by the Kalman Toth Tuesday,
I would personally start with adding a flag to help you dining table [NodeTypes], to point should your node sorts of can also be engage due to the fact moms and dad in the a relationship.
Note that I am also including a different sort of restriction of the (NodeTypeId, CanBeParent). This can be redundant, nonetheless it enables us to afterwards fool around with a different key leading so you’re able to it.
The next thing is always to incorporate this column and to table [Nodes], to be sure that the few (NodeTypeId, CanBeParent) is present for the desk [NodeTypes]. We’re going to include a determined continuous line (ParentNodeId_CanBeParent), which have well worth equal step one, to make [ParentNodeId] to become listed on since the father or mother automatically, and site dining table [Nodes] with the couples (ParentNodeId, ParentNodeId_CanBePArent).
It might be convenient if the was able to play with a select declaration inside a constraint, or being in a position to create assertions (glance at limitation within database level), but T-SQL lacks of these provides 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).
We avoid using meta tags instance “pk_” otherwise “fk_” inside research feature term https://datingranking.net/nl/colombiancupid-overzicht/. The objective of a document feature name’s to inform us what it is of the their character and not how it is actually getting used in this particular desk.
If you utilize more-measurements of sequence along these lines, your desk often fill which have scrap. Good coders explore recommendations in the event the selection of beliefs is higher otherwise volition. Instance, an order detail UPC you will source brand new Catalog dining table to help you enforce a rule that people only take sales to own presents inside stock.
In the event the place are small and stable, next we fool around with a (x In the (..)) constants. Such as for example, “sex_password TINYINT Default 0 Not NULL Examine (sex_password Into the (0, step one, 2, 9)) — iso-5218
Create Table Nodes (node_id INTEGER Not NULL Number 1 Secret, node_sorts of INTEGER Maybe not NULL Sources Node_Brands (node_type), parent_node_id INTEGER References Nodes (node_id));
Everything you are creating is an enthusiastic adjacency checklist design. They mimics non-relational tip organizations for the SQL. It has one another agencies (nodes) and the ladder (relationship). However, the correct cannot be both (Chen? E-R modeling? Freshman databases group at school?). So while this is however an incorrect analysis model, you’ll have created it
Perform Dining table Forest_and_Nodes (node_id INTEGER Perhaps not NULL Number 1 Trick, node_sort of CHAR(1) Standard ‘N’ Perhaps not NULL Check (node_input (‘P’, ‘N’), — P= You’ll Mother, N= Non-Father or mother mother or father_node_id INTEGER Sources Tree_and_Nodes (node_id), Examine (Instance When node_type of = ‘N’ And you may father or mother_node_id Is not NULL Then ‘F’ Otherwise ‘T’ Avoid = ‘T’) );
–CELKO– Instructions during the Celko Series to own Morgan-Kaufmann Posting: Analytics and you will OLAP into the SQL / Investigation and you can Databases: Basics used Study / Specifications and you may Conditions inside the SQL SQL to own Sming Layout / SQL Puzzles and you may Solutions / Thinking inside Kits / Trees and Hierarchies from inside the SQL