I have both tables less than, just how to enforce constaint you to definitely Nodes having NodeTypeId 2 (CannotBeParent) can not be a daddy versus lead to?
- Modified by the gao.seng Wednesday,
Solutions
- PM
I might start with incorporating a flag to desk [NodeTypes], to point if for example the node sort of can be participate as the parent when you look at the a romance.
See that I am also including another type of restriction by (NodeTypeId, CanBeParent). That is redundant, but it enable me to afterwards play with a different secret directing to help you it.
The next step is to include which column and desk [Nodes], to be certain your few (NodeTypeId, CanBeParent) is present within the dining table [NodeTypes]. We’ll include a calculated continuing column (ParentNodeId_CanBeParent), having really worth equal step one, to force [ParentNodeId] to become listed on given that parent by default, and you may resource dining table [Nodes] by using the few (ParentNodeId, ParentNodeId_CanBePArent).
It would be easier in the event the been able to have fun with a select report into the a restriction, or becoming able to perform assertions (evaluate constraint on databases height), but T-SQL lacks of them keeps as of today.
- 6:58 PM
The responses
So where was Alex Kuznetsov if you want your? Alex attended with constraintss so you can impose organization statutes most somebody would simply thought was you can easily with produces.
But, he tend to hits you to by adding an auxillary column. Maybe you can give more background about what you are trying reach? Which could to offer inspiration to some service.
If Is available (Look for Most readily useful step 1 1 Out-of NodeTypes Inner Sign-up Nodes Towards the Nodes.NodeId = And you will Nodes.FK_NodeTypeId = NodeTypes.NodeTypeId And you will NodeTypes.NodeType = ‚CanBeParent‘) Start Place = step 1 Stop Else Initiate Place = 0 Stop Go back Prevent;
, NodeType varchar(255) maybe not null ) do desk Nodes (NodeId int constraint PK__Nodes_NodeId First Key , FK_NodeTypeId int restriction FK__NodeTypes_NodeTypeId recommendations NodeTypes (NodeTypeId) , FK_ParentNodeid int constraint FK__Nodes_ParentNodeId__Nodes_NodeId recommendations Nodes (NodeId) See (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
- Advised just like the address because of the sdsuser Wednesday,
We understand Alex much towards the SQLBlog I am able to search higher around, but you is actually right the greater number of I think about this request the closer I have so you’re able to a solution. It is sort of such as with a buyers having a designated sales rep and have now every people in the new staff strings out of command each other top to bottom, nevertheless the customers is more like an online employee however, are unable to would individuals. In this instance a workstation is also strings along with her a couple of products out of a residential district pond such as cams and you will microphones, nevertheless workstation by itself can not be a dad unit. I think the customer-sales person desk build may use right here, I’m able to feedback the program construction document to see if one to is sensible.
- Advised while the address of the sdsuser Wednesday,
- Unproposed since the answer from the sdsuser Wednesday,
- PM
Much less hidden because the a cause, it may work. I’m able to see how nHibernate likes they tomorrow, and you will my personal manager.
- Modified from the Kalman Toth Friday,
I might begin by adding a banner to help you desk [NodeTypes], to indicate in case the node types of normally engage because the mother during the a love.
Notice that I am also adding another constraint because of the (NodeTypeId, CanBeParent). This might be redundant, however it enable us to later on fool around with a foreign trick leading in order to they.
The next thing will be to add that it column and desk [Nodes], to make sure your couples (NodeTypeId, CanBeParent) exists inside desk [NodeTypes]. We will also add a determined continuous line (ParentNodeId_CanBeParent), having really worth equivalent 1, to make [ParentNodeId] to participate once the mother or father by default, and you can source dining table [Nodes] utilising the partners (ParentNodeId, ParentNodeId_CanBePArent).
It might be easier when the been able to have fun with a select report into the a limitation, or becoming in a position to perform assertions (evaluate restriction at databases level), however, T-SQL lacks of them has as of today.
- 6:58 PM
There is no 
such thing as a “node_type_id” there can be a “
We never use meta tags for example “pk_” otherwise “fk_” for the investigation feature title. The reason for a document feature name is to inform all of us what it is from the the characteristics rather than the way it was being used in this table.
When you use more than-size of sequence similar to this, the desk often complete with garbage. An effective coders use recommendations in the event the selection of beliefs try highest otherwise volition. Including, an order detail UPC you are going to source this new List table in order to impose a tip we only take sales to have gifts within the stock.
When the place are small and stable, after that we fool around with a (x During the (..)) constants. Instance, “sex_code TINYINT Standard 0 Perhaps not NULL See (sex_password Inside the (0, step one, 2, 9)) — iso-5218
Perform Dining table Nodes (node_id INTEGER Maybe not NULL Number 1 Key, node_type of INTEGER Perhaps not NULL Records Node_Sizes (node_type), parent_node_id INTEGER Records Nodes (node_id));
Everything you have created is actually an adjacency checklist model. It mimics low-relational pointer stores within the SQL. It contains both agencies (nodes) while the ladder (relationship). However, a correct can not be each other (Chen? E-R acting? Freshman database group in school?). Very while this is nonetheless an invalid study model, you should have composed it as
Do Desk Forest_and_Nodes (node_id INTEGER Maybe not NULL Primary Key, node_kind of CHAR(1) Default ‚N‘ Perhaps not NULL Have a look at (node_enter in (‚P‘, ‚N‘), — P= You’ll be able to Parent, N= Non-Mother mother_node_id INTEGER References Tree_and_Nodes (node_id), Have a look at (Instance Whenever node_style of = ‚N‘ And you may parent_node_id Is not NULL Next ‚F‘ More ‚T‘ Stop = ‚T‘) );
–CELKO– Instructions in Celko Collection for Morgan-Kaufmann Publishing: Statistics and you will OLAP in SQL / Research and you can Database: Maxims in practice Data / Specifications and you can Standards from inside the SQL SQL for Sming Layout / SQL Puzzles and you may Solutions / Thought within the Kits / Trees and Hierarchies from inside the SQL
Leave A Comment