I’ve the 2 dining tables less than, how-to impose constaint you to Nodes that have NodeTypeId dos (CannotBeParent) can not be a parent without produce?
- Edited from the gao.seng Wednesday,
Solutions
- PM
I would personally begin by including a banner so you can table [NodeTypes], to suggest whether your node particular is take part just like the mother or father for the a romance.
See that And i am including a separate restriction because of the (NodeTypeId, CanBeParent). That is redundant, nonetheless it enables us to later on fool around with a foreign secret leading to help you they.
The next phase is to add it column and also to table [Nodes], to ensure that the couples (NodeTypeId, CanBeParent) is obtainable into the desk [NodeTypes]. We will include a determined carried on line (ParentNodeId_CanBeParent), with worth equal 1, to make [ParentNodeId] to participate as moms and dad automagically, and site desk [Nodes] utilizing the couple (ParentNodeId, ParentNodeId_CanBePArent).
It will be simpler if managed to fool around with a choose statement inside a restriction, or being in a position to manage assertions (check limitation at databases level), however, T-SQL does not have of them have as of today.
- 6:58 PM
All of the answers
So where try Alex Kuznetsov if you want him? Alex have come with constraintss in order to impose organization laws very anybody manage just consider is you’ll with produces.
But, he tend to hits one to with the addition of a keen auxillary line. Maybe you offers a few more history on what you’re trying to reach? caribbeancupid-recensies That will supply determination to some solution.
If Is available (Find Top step 1 step 1 Out of NodeTypes Interior Register Nodes To your Nodes.NodeId = And you may Nodes.FK_NodeTypeId = NodeTypes.NodeTypeId And NodeTypes.NodeType = ‘CanBeParent’) Start Place = step one Stop More Begin Lay = 0 Stop Get back End;
, NodeType varchar(255) perhaps not null ) carry out dining table Nodes (NodeId int constraint PK__Nodes_NodeId Number one Key , FK_NodeTypeId int constraint FK__NodeTypes_NodeTypeId references NodeTypes (NodeTypeId) , FK_ParentNodeid int constraint FK__Nodes_ParentNodeId__Nodes_NodeId references Nodes (NodeId) View (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 given that answer by the sdsuser Wednesday,
I realize Alex much for the SQLBlog I am able to look higher indeed there, however you try proper the more I think regarding it consult the latest closer I have to an answer. It is version of including that have a consumer having an assigned transformation agent and then have most of the members of the fresh reps strings of order both up and down, nevertheless consumer is more like a virtual staff but can not manage some body. In cases like this a beneficial workstation normally strings along with her a couple of gizmos out-of a community pond such as cameras and microphones, although workstation alone cannot be a pops unit. In my opinion the client-sales representative dining table construction may implement here, I can remark the software program framework file to see if one is practical.
- Proposed as address by sdsuser Wednesday,
- Unproposed given that respond to by sdsuser Wednesday,
- PM
Not as undetectable given that a trigger, it may work. I will observe nHibernate wants they tomorrow, and my manager.
- Modified by Kalman Toth Friday,
I might begin by including a flag so you’re able to desk [NodeTypes], to indicate whether your node sorts of can also be engage as the moms and dad in the a relationship.
Note that I am also including a different restriction by (NodeTypeId, CanBeParent). This can be redundant, but it enables us to later explore a different key leading so you’re able to they.
The next phase will be to incorporate so it line also to dining table [Nodes], to make sure your couple (NodeTypeId, CanBeParent) is obtainable from inside the table [NodeTypes]. We’re going to include a computed continuing column (ParentNodeId_CanBeParent), that have worth equivalent step 1, to force [ParentNodeId] to join while the moms and dad automatically, and source table [Nodes] making use of the couples (ParentNodeId, ParentNodeId_CanBePArent).
It will be simpler in the event that managed to play with a choose report to the a constraint, or becoming in a position to carry out assertions (consider restriction at the database height), but T-SQL does not have ones keeps as of today.
- 6:58 PM
There is no such thing as a “node_type_id” there can be a “
We never use meta data such “pk_” otherwise “fk_” in the data function identity. The objective of a data function name’s to inform all of us the goals by their characteristics and not the way it is actually used in this table.
If you are using more than-sized sequence similar to this, your desk have a tendency to fill that have scrap. Good coders use references in the event that band of beliefs try high otherwise volition. Such as for instance, an order outline UPC you will resource the newest Collection dining table so you can enforce a guideline that we only take requests to own gift ideas in stock.
In the event the put are smaller than average secure, after that we have fun with a (x Into the (..)) constants. For example, “sex_password TINYINT Default 0 Not NULL Have a look at (sex_password For the (0, step 1, 2, 9)) — iso-5218
Perform Table Nodes (node_id INTEGER Maybe not NULL No. 1 Trick, node_form of INTEGER Perhaps not NULL References Node_Designs (node_type), parent_node_id INTEGER References Nodes (node_id));
That which you are creating try an adjacency record model. They mimics non-relational tip organizations during the SQL. It includes one another agencies (nodes) and steps (relationship). However, a correct can not be both (Chen? E-R acting? Freshman databases class in school?). Therefore while this is nevertheless an invalid investigation design, you have written it as
Perform Dining table Forest_and_Nodes (node_id INTEGER Perhaps not NULL Number one Secret, node_type of CHAR(1) Default ‘N’ Not NULL Look at (node_input (‘P’, ‘N’), — P= It is possible to Mother or father, N= Non-Parent father or mother_node_id INTEGER Sources Tree_and_Nodes (node_id), Evaluate (Case When node_form of = ‘N’ And you can moms and dad_node_id Is not NULL After that ‘F’ Otherwise ‘T’ End = ‘T’) );
–CELKO– Books from inside the Celko Collection to have Morgan-Kaufmann Publishing: Statistics and OLAP for the SQL / Analysis and you can Database: Principles used Studies / Proportions and you will Requirements for the SQL SQL to possess Sming Design / SQL Puzzles and you will Answers / Considering within the Set / Trees and you may Hierarchies inside the SQL