I’ve both tables less than, 
how to impose constaint that Nodes that have NodeTypeId 2 (CannotBeParent) cannot be a parent in place of produce?
- Edited by the gao.seng Wednesday,
Answers
- PM
I would personally start with including a banner to dining table [NodeTypes], to suggest in case your node sorts of is take part once the mother or father for the a relationship.
Notice that And i am incorporating a new restriction by (NodeTypeId, CanBeParent). It is redundant, but it enables me to afterwards explore a foreign secret leading to it.
The next thing is to incorporate this column and table [Nodes], to ensure your couples (NodeTypeId, CanBeParent) is obtainable into the desk [NodeTypes]. We will include a computed continued line (ParentNodeId_CanBeParent), which have worth equal 1, to force [ParentNodeId] to become listed on due to the fact moms and dad automagically, and you may resource dining table [Nodes] utilising the partners (ParentNodeId, ParentNodeId_CanBePArent).
It would be simpler in the event the been able to explore a choose report to the a constraint, or becoming capable carry out assertions (take a look at limitation at the databases level), but T-SQL does not have ones has as of today.
- 6:58 PM
Every responses
Where is actually Alex Kuznetsov if you want your? Alex came up with constraintss so you can impose providers guidelines very some one would merely believe would-be possible with leads to.
Then again, the guy have a tendency to reaches that adding an auxillary column. Perchance you can give some more records on what you’re trying reach? Which could giving inspiration to a few provider.
If Is obtainable (Look for Ideal 1 step 1 Out-of NodeTypes Inner Register Nodes Into Nodes.NodeId = And you may Nodes.FK_NodeTypeId = NodeTypes.NodeTypeId And you may NodeTypes.NodeType = ‚CanBeParent‘) Begin Set = step one Prevent Else Start Put = 0 Prevent Get back Stop;
, NodeType varchar(255) maybe not null ) carry out dining table Nodes (NodeId int limitation PK__Nodes_NodeId Number 1 Key , FK_NodeTypeId int limitation FK__NodeTypes_NodeTypeId sources NodeTypes (NodeTypeId) , FK_ParentNodeid int limitation FK__Nodes_ParentNodeId__Nodes_NodeId recommendations Nodes (NodeId) Examine (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 since the address by sdsuser Wednesday,
We discover Alex a lot on SQLBlog I am able to search better truth be told there, however you is correct the greater amount of I think regarding it request the newest closer I have to help you a remedy. It’s version of eg having a buyers that have an allocated transformation rep and just have all of the people in the latest reps strings off order both up-and-down, nevertheless customer is more like an online worker but can’t perform somebody. In this instance an excellent workstation normally chain with her a couple of equipment out-of a residential area pond instance webcams and you can microphones, nevertheless the workstation by itself cannot be a pops product. In my opinion the customer-sales agent desk construction may implement here, I can remark the software design file to see if one makes sense.
- Proposed since respond to of the sdsuser Wednesday,
- Unproposed since the respond to from the sdsuser Wednesday,
- PM
Not as invisible given that a trigger, it might work. I’m able to find out how nHibernate enjoys they the next day, and you can my personal manager.
- Modified by the Kalman Toth Monday,
I would personally start with including a banner so you’re able to dining table [NodeTypes], to suggest in the event the node method of normally participate once the father or mother into the a love.
Observe that I am also incorporating another restriction from the (NodeTypeId, CanBeParent). This is certainly redundant, but it enables me to later play with a foreign trick pointing to help you it.
The next thing is always to incorporate it column in order to dining table [Nodes], to ensure your partners (NodeTypeId, CanBeParent) can be found inside the dining table [NodeTypes]. We shall also add a computed carried on line (ParentNodeId_CanBeParent), which have well worth equivalent step one, to force [ParentNodeId] to become listed on once the moms and dad by default, and you may source dining table [Nodes] making use of the pair (ParentNodeId, ParentNodeId_CanBePArent).
It might be smoother when the managed to use a select statement inside a check constraint, or becoming capable create assertions (check constraint from the databases height), but T-SQL lacks of these possess to date.
- 6:58 PM
There is no such thing as a “node_type_id” there can be a “
We avoid using meta data like “pk_” or “fk_” in investigation function name. The reason for a data ability name is to inform united states what it is because of the their character rather than the way it are getting used in this table.
If you use more-measurements of sequence along these lines, the table commonly fill with trash. A great programmers play with references in the event that number of opinions was large otherwise volition. Like, your order outline UPC you’ll site the fresh new Inventory dining table to help you impose a guideline that individuals only take orders having gift ideas for the stock.
In the event the set is small and steady, following we explore a (x Inside (..)) constants. Eg, “sex_password TINYINT Default 0 Maybe not NULL Have a look at (sex_password Into the (0, 1, dos, 9)) — iso-5218
Carry out Table Nodes (node_id INTEGER Not NULL Number one Trick, node_variety of INTEGER Perhaps not NULL Sources Node_Sizes (node_type), parent_node_id INTEGER Recommendations Nodes (node_id));
Everything have created is actually an adjacency number design. They imitates non-relational pointer stores inside SQL. It includes both organizations (nodes) and the hierarchy (relationship). However, the correct can’t be each other (Chen? E-R acting? Freshman database group at school?). So although this is however an invalid investigation design, you will have created it
Do Desk Forest_and_Nodes (node_id INTEGER Perhaps not NULL Number one Trick, node_type CHAR(1) Default ‚N‘ Maybe not NULL Evaluate (node_enter in (‚P‘, ‚N‘), — P= You’ll be able to Mother or father, N= Non-Mother mother_node_id INTEGER Records Tree_and_Nodes (node_id), Glance at (Situation When node_types of = ‚N‘ And you will parent_node_id Isn’t NULL Then ‚F‘ More ‚T‘ Prevent = ‚T‘) );
–CELKO– Instructions in the Celko Show getting Morgan-Kaufmann Publishing: Statistics and you can OLAP inside SQL / Analysis and you will Databases: Maxims in practice Studies / Specifications and Requirements for the SQL SQL having Sming Build / SQL Puzzles and you can Solutions / Considering from inside the Kits / Trees and you can Hierarchies in the SQL