You will find the 2 tables less than, how to enforce constaint one Nodes which have NodeTypeId dos (CannotBeParent) can not be a pops as opposed to lead to?
- Edited of the gao.seng Wednesday,
Responses
- PM
I might start by incorporating a flag to help you desk [NodeTypes], to point when your node type is also take part given that moms and dad inside a romance.
Note that And i am incorporating a unique constraint of the (NodeTypeId, CanBeParent). This is certainly redundant, nonetheless it will allow me to later on fool around with a foreign key directing to it.
The next step is to try to put so it line in order to table [Nodes], to ensure the partners (NodeTypeId, CanBeParent) can be found when you look at the desk [NodeTypes]. We shall also add a computed continuing line (ParentNodeId_CanBeParent), having value equal step 1, to make [ParentNodeId] to join while the mother by default, and you can source table [Nodes] by using the couple (ParentNodeId, ParentNodeId_CanBePArent).
It could be smoother in the event that managed to explore a choose statement to the a limitation, or becoming able to perform assertions (have a look at constraint from the databases top), however, T-SQL lacks of these has actually currently.
- 6:58 PM
Every reactions
Where is actually Alex Kuznetsov when you require your? Alex have come up with constraintss to enforce business laws most someone create just consider might be you’ll with causes.
But, the guy commonly hits you to by the addition of an enthusiastic auxillary column. Maybe you gives even more records on which you are seeking reach? Which could to provide determination for some solution.
In the event the Is obtainable (Find Finest 1 step 1 Away from NodeTypes Inner Register Nodes On the Nodes.NodeId = And you may Nodes.FK_NodeTypeId = NodeTypes.NodeTypeId And you can NodeTypes.NodeType = ‚CanBeParent‘) Begin Put = 1 Prevent More Begin Set = 0 Avoid Return Stop;
, NodeType varchar(255) maybe not null ) create table Nodes (NodeId int restriction PK__Nodes_NodeId First Trick , FK_NodeTypeId int constraint FK__NodeTypes_NodeTypeId records NodeTypes (NodeTypeId) , FK_ParentNodeid int restriction FK__Nodes_ParentNodeId__Nodes_NodeId records Nodes (NodeId) Consider (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 while the address from the sdsuser Wednesday,
I realize Alex a lot to the SQLBlog I’m able to search greater there, you are correct more I think about it demand new better I have so you can a remedy. It is sort of including that have a customers with a designated conversion process representative and just have most of the members of the new reps strings of command one another along, nevertheless buyers is much more including an online staff member however, can not would somebody. In this instance an excellent workstation latinomeetup inloggen is strings with her a collection of gadgets out-of a residential area pond eg adult cams and you may microphones, however the workstation itself cannot be a dad unit. In my opinion the consumer-sales professional desk construction will get use right here, I will feedback the application structure document to find out if one is sensible.
- Recommended as respond to of the sdsuser Wednesday,
- Unproposed as the respond to by the sdsuser Wednesday,
- PM
Far less undetectable while the a cause, this may work. I can observe how nHibernate enjoys they tomorrow, and my director.
- Modified by the Kalman Toth Monday,
I would personally begin by incorporating a flag to desk [NodeTypes], to suggest if your node form of is take part because the mother or father inside the a relationship.
Observe that I am also including a special constraint by (NodeTypeId, CanBeParent). This can be redundant, nevertheless will allow us to after fool around with a foreign trick pointing so you can they.
The next thing will be to add this line and to table [Nodes], to ensure the few (NodeTypeId, CanBeParent) is obtainable in the table [NodeTypes]. We’ll also add a determined persisted column (ParentNodeId_CanBeParent), with well worth equal step 1, to force [ParentNodeId] to become listed on due to the fact father or mother automatically, and you will source dining table [Nodes] using the partners (ParentNodeId, ParentNodeId_CanBePArent).
It would be simpler if was able to play with a choose report to the a check restriction, or becoming able to carry out assertions (examine limitation in the database height), but T-SQL does not have of those have to date.
- 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).
I avoid using meta tags for example “pk_” or “fk_” in studies function label. The goal of a data feature name’s to inform you what it is by the nature and never the way it is actually used in this dining table.
If you utilize over-measurements of sequence such as this, your own dining table have a tendency to complete that have scrap. Good programmers fool around with sources when the selection of values are highest otherwise volition. Such, your order outline UPC might source the Index table so you’re able to impose a tip that individuals only take instructions to possess merchandise from inside the stock.
If the set try small and secure, after that we play with a check (x During the (..)) constants. Such as for example, “sex_code TINYINT Default 0 Not NULL Look at (sex_password Inside (0, step one, dos, 9)) — iso-5218
Carry out Desk Nodes (node_id INTEGER Perhaps not NULL First Secret, node_variety of INTEGER Not NULL References Node_Models (node_type), parent_node_id INTEGER Recommendations Nodes (node_id));
Everything have created are a keen adjacency record design. They mimics low-relational pointer organizations inside SQL. It has both entities (nodes) as well as the ladder (relationship). But a proper can not be each other (Chen? E-R acting? Freshman database category in school?). Therefore although this is still an invalid investigation design, you have authored it as
Perform Table Forest_and_Nodes (node_id INTEGER Not NULL Top Key, node_form of CHAR(1) Default ‚N‘ Perhaps not NULL See (node_type in (‚P‘, ‚N‘), — P= You can easily Moms and dad, N= Non-Moms and dad mother or father_node_id INTEGER Sources Tree_and_Nodes (node_id), Check (Situation When node_sort of = ‚N‘ And you will mother or father_node_id Isn’t NULL Next ‚F‘ Else ‚T‘ Stop = ‚T‘) );
–CELKO– Guides in Celko Show to have Morgan-Kaufmann Posting: Statistics and you will OLAP in the SQL / Research and you may Databases: Maxims in practice Studies / Measurements and you will Conditions into the SQL SQL to have Sming Layout / SQL Puzzles and you may Solutions / Considering when you look at the Set / Woods and you may Hierarchies from inside the SQL
