You will find both tables below, tips impose constaint you to Nodes with NodeTypeId dos (CannotBeParent) can not be a pops as opposed to bring about?
- Edited because of the gao.seng Wednesday,
Solutions
- PM
I might begin by adding a flag to dining table [NodeTypes], to suggest whether your node type of can take part since father or mother for the a relationship.
Observe that I am also adding yet another restriction from the (NodeTypeId, CanBeParent). This can be redundant, nevertheless enables me to after explore a foreign secret leading so you’re able to they.
The next thing is to try to include it line and desk [Nodes], to be certain that pair (NodeTypeId, CanBeParent) exists in desk [NodeTypes]. We’re going to also add a determined persisted column (ParentNodeId_CanBeParent), which have really worth equal 1, to force [ParentNodeId] to join since father or mother by default, and you will resource desk [Nodes] with the pair (ParentNodeId, ParentNodeId_CanBePArent).
It could be easier if the was able to play with a select declaration into the a constraint, or becoming able to manage assertions (look at limitation at databases height), but T-SQL lacks of these have to date.
- 6:58 PM
The replies
So where was Alex Kuznetsov if you want your? Alex attended with constraintss to demand organization guidelines very anybody manage simply believe could be it is possible to with causes.
But then, he have a tendency to achieves you to definitely with the addition of an enthusiastic auxillary column. Perchance you offers a few more background about what you are trying go? Which could to provide inspiration to a few services.
When the Can be obtained (Find Greatest step 1 step 1 From NodeTypes Inner Register Nodes On Nodes.NodeId = And Nodes.FK_NodeTypeId = NodeTypes.NodeTypeId And NodeTypes.NodeType = ‘CanBeParent’) Start Put = step one Avoid More Begin Put = 0 End Come back Avoid;
, NodeType varchar(255) not null ) carry out table Nodes (NodeId int restriction PK__Nodes_NodeId No. 1 Secret , FK_NodeTypeId int constraint FK__NodeTypes_NodeTypeId sources NodeTypes (NodeTypeId) , FK_ParentNodeid int limitation FK__Nodes_ParentNodeId__Nodes_NodeId references Nodes (NodeId) Check (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
- Proposed as respond to from the sdsuser Wednesday,
I realize Alex a lot into the SQLBlog I’m able to dig greater around, nevertheless is actually correct the greater I believe about it demand brand new nearer I have so you’re able to an answer. It is type of particularly that have a customers having an allocated conversion process associate and have all people in brand new reps strings regarding order both up and down, however the consumer equestriansingles aansluiting is far more instance a virtual staff however, cannot carry out some body. In this case an excellent workstation is chain together with her a set of gadgets off a community pool instance cameras and you will microphones, but the workstation alone cannot be a parent equipment. I believe the client-sales agent table framework could possibly get pertain here, I’m able to opinion the program framework file to see if you to is practical.
- Advised as answer of the sdsuser Wednesday,
- Unproposed as address because of the sdsuser Wednesday,
- PM
Much less hidden because the a cause, this may functions. I could find out how nHibernate loves they the next day, and you can my manager.
- Edited of the Kalman Toth Monday,
I would begin by including a banner to dining table [NodeTypes], to suggest if the node type normally participate once the father or mother into the a relationship.
Observe that And i am including another type of limitation of the (NodeTypeId, CanBeParent). It is redundant, nevertheless enable me to later fool around with a foreign secret leading to it.
The next step should be to put so it column and dining table [Nodes], to be certain the pair (NodeTypeId, CanBeParent) is obtainable in table [NodeTypes]. We shall include a calculated continued line (ParentNodeId_CanBeParent), having well worth equivalent step one, to make [ParentNodeId] to become listed on because mother by default, and you can resource desk [Nodes] utilising the few (ParentNodeId, ParentNodeId_CanBePArent).
It will be convenient in the event the managed to play with a select statement in to the a restriction, or becoming in a position to would assertions (look at restriction during the database peak), however, T-SQL does not have of them have currently.
- 6:58 PM
There is no such thing as a “node_type_id” there can be a “
I avoid using meta tags such “pk_” otherwise “fk_” during the analysis element name. The goal of a document ability name is to inform you what it is of the their nature and not how it are being used in this table.
If you use more-measurements of sequence like this, your table often fill which have rubbish. A beneficial programmers explore references if the gang of opinions is large or volition. For example, your order outline UPC you’ll resource the brand new Catalog desk to help you impose a guideline that people just take purchases to own merchandise when you look at the inventory.
In the event the set are small and secure, then i use a (x From inside the (..)) constants. Such as for example, “sex_password TINYINT Standard 0 Not NULL Look at (sex_password When you look at the (0, step 1, dos, 9)) — iso-5218
Manage Table Nodes (node_id INTEGER Not NULL First Trick, node_variety of INTEGER Not NULL Records Node_Products (node_type), parent_node_id INTEGER References Nodes (node_id));
That which you are creating try an adjacency record design. They mimics non-relational pointer chains when you look at the SQL. It contains one another entities (nodes) therefore the ladder (relationship). However, a proper can not be one another (Chen? E-R acting? Freshman database class in school?). Therefore although this is nevertheless an invalid investigation design, you should have authored it as
Perform Desk Forest_and_Nodes (node_id INTEGER Not NULL First Key, node_variety of CHAR(1) Standard ‘N’ Perhaps not NULL Take a look at (node_type in (‘P’, ‘N’), — P= You can easily Mother, N= Non-Parent parent_node_id INTEGER Sources Forest_and_Nodes (node_id), Take a look at (Case When node_method of = ‘N’ And you can parent_node_id Isn’t NULL Next ‘F’ More ‘T’ Stop = ‘T’) );
–CELKO– Instructions from inside the Celko Series to possess Morgan-Kaufmann Publishing: Analytics and OLAP from inside the SQL / Investigation and you will Database: Rules in practice Data / Proportions and Conditions from inside the SQL SQL to have Sming Build / SQL Puzzles and you may Solutions / Considering for the Sets / Woods and you will Hierarchies for the SQL
