Possible to have a fixed value as foreign key? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Possible to have a fixed value as foreign key?

Author

Topic

Player_One
Starting Member

5 Posts

Posted - 2007-08-15 : 18:27:08

Hi everyone,

I'm trying to make a hierarchy in which some nodes are locked, preventing children nodes from being added.

The hierarchy table points to itself (the parent entry). I want to secure this from the DB side. Initially, no nodes have parents but can attach themselves to one.

I'd like my FK contraint to be like:
CONSTRAINT FK_hierarchy FOREIGN KEY (parent_id, 1) REFERENCES nde_recursion(hierarchy_id, allow_children)

This is so the parent_id can only be set if that entry allows children.

Here's my approach. Any better ideas? I feel like I'm adding an extra field (parent_allow) when I shouldn't.


CREATE TABLE hierarchy(
	hierarchy_id	INT	IDENTITY(1,1) UNIQUE NOT NULL,
	allow_children	BIT	DEFAULT 1 NOT NULL,

	parent_id	INT,	-- NULL is for root nodes
	parent_allow	BIT	DEFAULT 1 NOT NULL,

	CONSTRAINT PK_hierarchy PRIMARY KEY (hierarchy_id, allow_children),
	CONSTRAINT FK_hierarchy FOREIGN KEY (parent_id, parent_allow) REFERENCES nde_recursion(hierarchy_id, allow_children),
    CONSTRAINT CK_hierarchy CHECK ((nde_status = 'Normal' AND parent_id IS NULL) OR (nde_status != 'Normal' AND parent_id IS NOT NULL)),
    CONSTRAINT CK_hierarchy2 CHECK (parent_allow = 1)
)

Player_One
Starting Member

5 Posts

Posted - 2007-08-17 : 09:34:42

Nobody has a better solution to this? :(

X002548
Not Just a Number

15586 Posts

Posted - 2007-08-17 : 09:36:55

This is a business rule

How do you decide when to allow/not allow child records?

I have never heard of a req lik this before

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Player_One
Starting Member

5 Posts

Posted - 2007-08-17 : 09:47:20

Well, think of it like this. Assume each record is a test result (pass or fail) from random sampling. Not all items are tested. Items that pass will not require subsequent testing and therefore should not have any children. There are two types children nodes. One is a repair record of the failing item which will have to be retested. The other is a "tracer" (a random sampling) to test a different item manufactured around the same time but it is linked to the original failing record. This is for QA/QC purposes. And if this "tracer" fails, it can have children too (more tracers and repairs). Repaired items will not have children but are children. Failed items (renamed to allow_children in my example) can have children.

X002548
Not Just a Number

15586 Posts

Posted - 2007-08-17 : 09:55:47

I think you need more tables to accuratley reflect your business model

It sounds like the square peg, round hole thing

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Player_One
Starting Member

5 Posts

Posted - 2007-08-17 : 11:41:48

Possibly, but I'm just looking for a simple answer. I feel there just has to be one. :) Anyway, I'll just break this down into 2 simpler tables.


CREATE TABLE table1
(
	table1_id	INT	IDENTITY(1,1) UNIQUE NOT NULL,
	pass		BIT	DEFAULT 0 NOT NULL,	--

	CONSTRAINT PK_hierarchy PRIMARY KEY (table1_id, pass)
)

CREATE TABLE table2
(
	table2_id	INT	IDENTITY(1,1) PRIMARY KEY NOT NULL,
	table1_id	INT,
	static_pass	BIT	DEFAULT 0 NOT NULL,

	CONSTRAINT FK_table1_table2 FOREIGN KEY (table1_id, static_pass) REFERENCES nde_recursion(table1_id, pass),
	CONSTRAINT CK_table2 CHECK (pass = 0)
)

I'm just wondering if I can get table2 to reference table1 only if table1 has "pass = 0". What I wanted to do was the following but it doesn't work...


CREATE TABLE table2
(
	table2_id	INT	IDENTITY(1,1) PRIMARY KEY NOT NULL,
	table1_id	INT,

	CONSTRAINT FK_table1_table2 FOREIGN KEY (table1_id, 0) REFERENCES nde_recursion(table1_id, pass)
)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources