I have the 2 tables less than, tips impose constaint you to Nodes with NodeTypeId dos (CannotBeParent) cannot be a grandfather without bring about?
- Edited by gao.seng Wednesday,
Solutions
- PM
I would personally begin by adding a flag to dining table [NodeTypes], to indicate in case your node method of can take part as the parent when you look at the a romance.
Notice that And i am incorporating an alternative restriction by the (NodeTypeId, CanBeParent). It is redundant, it will allow us to after use a foreign secret leading so you can they.
The next phase is always to include this line and also to dining table [Nodes], to make sure that couples (NodeTypeId, CanBeParent) is obtainable into the table [NodeTypes]. We will include a calculated proceeded column (ParentNodeId_CanBeParent), with well worth equivalent step 1, to make [ParentNodeId] to join because parent by default, and you will resource table [Nodes] utilizing the couple (ParentNodeId, ParentNodeId_CanBePArent).
It will be convenient in the event that was able to fool around with a select statement inside a limitation, or becoming able to carry out assertions (look at constraint at database level), however, T-SQL does not have of them features as of today.
- 6:58 PM
Most of the answers
So where try Alex Kuznetsov when you really need your? Alex came up with constraintss in order to enforce organization laws really somebody do simply consider might be you can easily having produces.
But, the guy usually reaches you to definitely adding a keen auxillary column. Perchance you deliver some more background about what you’re trying achieve? Which will supply desire for some service.
If the Exists (Come across Best step one step one From NodeTypes Internal Sign-up Nodes On Nodes.NodeId = And you will Nodes.FK_NodeTypeId = NodeTypes.NodeTypeId And you will NodeTypes.NodeType = ‘CanBeParent’) Start Lay = step 1 Avoid Else Initiate Put = 0 End Get back Prevent;
, NodeType varchar(255) perhaps not null ) create desk Nodes (NodeId int restriction PK__Nodes_NodeId No. 1 Key , FK_NodeTypeId int limitation FK__NodeTypes_NodeTypeId sources NodeTypes (NodeTypeId) , FK_ParentNodeid int limitation FK__Nodes_ParentNodeId__Nodes_NodeId records Nodes (NodeId) Have a 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
- Advised due to the fact address by the sdsuser Wednesday,
I discover Alex much towards the SQLBlog I can enjoy greater truth be told there, but you try right more I do believe regarding it demand the fresh new closer I get so you can an answer. It’s variety of such as with a buyers having an assigned sales agent and just have most of the people in the fresh representatives chain away from demand one another top to bottom, but the customer is far more particularly an online staff member but are unable to perform people. In cases like this an excellent workstation can also be strings together with her some equipment regarding a residential area pond for example adult cams and you will microphones, however the workstation in itself cannot be a daddy equipment. I think the consumer-sales representative table build get apply here, I can feedback the program framework file to see if that is sensible.
- Suggested due to the fact respond to by the sdsuser Wednesday,
- Unproposed just like the answer of the sdsuser Wednesday,
- PM
Far less undetectable since the a trigger, it might really works. I will observe how nHibernate enjoys it tomorrow, and you may my personal director.
- Edited from the Kalman Toth Tuesday,
I’d start by adding a flag to help you dining table [NodeTypes], to suggest if the node kind of normally engage since mother or father during the a love.
Note that I am also incorporating an alternative restriction because of the (NodeTypeId, CanBeParent). This might be redundant, but it enable us to later on play with a foreign secret pointing so you can they.
The next phase will be to put so it column in order to dining table [Nodes], to be certain that couple (NodeTypeId, CanBeParent) is available when you look at the table [NodeTypes]. We’re going to include a calculated proceeded line (ParentNodeId_CanBeParent), having really worth equivalent step 1, to force [ParentNodeId] to become listed on while the mother or father automatically, and you will source desk [Nodes] making use of the partners (ParentNodeId, ParentNodeId_CanBePArent).
It could be smoother when the was able to explore a select declaration in to the a restriction, or becoming able to would assertions (consider constraint within databases height), however, T-SQL lacks ones possess currently.
- 6:58 PM
There is no such thing as a “node_type_id” there can be a “
I never use meta tags such “pk_” otherwise “fk_” for the analysis ability title. The goal of a document feature name’s to inform you what it is by their nature and never how it is getting used in this particular table.
If you utilize more-sized sequence such as this, your own table tend to complete which have rubbish. An excellent programmers fool around with references in the event the band of viewpoints are large otherwise volition. Such, an order outline UPC you are going to site brand new Directory table to demand a tip we only take purchases having gifts in the inventory.
If the lay is smaller than average stable, then i explore a (x From inside the (..)) constants. Including, “sex_code TINYINT Default 0 Maybe not NULL Evaluate (sex_password Inside (0, step 1, 2, 9)) — iso-5218
Perform Dining table Nodes (node_id INTEGER Perhaps not NULL Top Trick, node_style of INTEGER Perhaps not NULL Sources Node_Versions (node_type), parent_node_id INTEGER Records Nodes (node_id));
What you have created is an enthusiastic adjacency listing design. It imitates non-relational pointer chains in SQL. It includes each other organizations (nodes) in addition to steps (relationship). But a correct can’t be both (Chen? E-R acting? Freshman database group in school?). Thus while this is still an invalid studies design, you’ll have hoe iemand een bericht te sturen op eris authored it
Create Table Tree_and_Nodes (node_id INTEGER Not NULL First Secret, node_form of CHAR(1) Standard ‘N’ Maybe not NULL See (node_type in (‘P’, ‘N’), — P= You can easily Mother or father, N= Non-Mother or father parent_node_id INTEGER References Forest_and_Nodes (node_id), See (Instance Whenever node_kind of = ‘N’ And moms and dad_node_id Is not NULL Following ‘F’ Otherwise ‘T’ Prevent = ‘T’) );
–CELKO– Courses within the Celko Show to possess Morgan-Kaufmann Posting: Statistics and you can OLAP in the SQL / Studies and you can Databases: Axioms in practice Studies / Measurements and you may Standards inside the SQL SQL to have Sming Layout / SQL Puzzles and you can Answers / Thought when you look at the Establishes / Trees and Hierarchies from inside the SQL