Primary Key column also a Foreign Key column in same table...
-
Trying to get my head wrapped around this one. I've heard of this being done before, just not sure why. Any thoughts?
-
Are you saying that ColumnA is your primary key and that ColumnA is also a foreign key referencing ColumnA in the same table??? That doesn't make sense to me. That would be adding a constraint that does not allow you to add or edit a row so that the primary key does not equal the same as another row's primary key. That is part of what the primary key attribute is about in the first place. There is nothing to prevent that but it still doesn't make any sense.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
I have never seen such a thing in the wild...I was wondering if inserts might even be blocked so ran a quick test...they work. I also ran an execution plan and the optimizer is smart enough not to do a key lookup to see if the parent is there which is likely why they work 😛
Is this something you found in a production database?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
opc.three (5/7/2012)
I have never seen such a thing in the wild...I was wondering if inserts might even be blocked so ran a quick test...they work. I also ran an execution plan and the optimizer is smart enough not to do a key lookup to see if the parent is there which is likely why they work 😛Is this something you found in a production database?
I also wondered the same thing so I tested it myself. Here is my quick example table.
create table TableA
(
ID int primary key,
SomeValue varchar(10)
)
go
insert TableA
select 1, 'A' union all
select 2, 'B' union all
select 3, 'C'
alter table TableA ADD CONSTRAINT TableA_KeyTest FOREIGN KEY (ID) references TableA (ID)
--this should be pretty obvious it won't work because it is the primary key
insert TableA select 1, 'D'
Yes it errors on the last insert as you would expect.
Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK__TableA__3214EC274E0988E7'. Cannot insert duplicate key in object 'dbo.TableA'.
The statement has been terminated.
That error is thrown before it even checks the foreign key constraint. That would make sense since it would be the lowest level of ability for this insert to succeed. The second check would logically be any foreign keys IF the primary key check is valid.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
wow, it's possible to do that if you create the foreign key with the NOCHECk command;
still that's just making a mess : looks like someone was just slapping bad code all over; i'd remove the constraint.
alter table TableA
WITH NOCHECK --the trick!
ADD CONSTRAINT TableA_KeyTest FOREIGN KEY (ID)
references TableA (ID)
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
You guys know the other thread I have going at the moment where I asked 'Referential Integrity: Who needs it - right?...'? This is from that database, and yes - it's in production. I only now discovered it as I was beginning to document the tables - what a mess.
:blink:
-
My test started with an empty table.
IF EXISTS ( SELECT *FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.t1')
AND type IN (N'U') )
DROP TABLE dbo.t1
GO
CREATE TABLE dbo.t1
(
id INT CONSTRAINT [pk_t1] PRIMARY KEY,
CONSTRAINT [fk_t1_t1] FOREIGN KEY (id) REFERENCES dbo.t1 (id)
);
GO
INSERT dbo.t1
(id)
VALUES (1);
Yes, I saw your other post...how have you survived the project? I may have tried torching the entire place by now 😛
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
opc.three (5/7/2012)
Yes, I saw your other post...how have you survived the project? I may have tried torching the entire place by now 😛Trust me - it has been an excruciating exercise in patience, and picking and choosing my battles as wisely as possible...
Put it this way - if you could have been a fly on the wall to some of the conversations I have had to have over the past 30 days on this project, you would've laughed your butt off (if it wasn't so eff'd up).
-
SQL_ME_RICH (5/7/2012)
opc.three (5/7/2012)
Yes, I saw your other post...how have you survived the project? I may have tried torching the entire place by now 😛Trust me - it has been an excruciating exercise in patience, and picking and choosing my battles as wisely as possible...
Put it this way - if you could have been a fly on the wall to some of the conversations I have had to have over the past 30 days on this project, you would've laughed your butt off (if it wasn't so eff'd up).
That sounds like a nasty situation to be in. It sounds like you have weathered the storm though and calmer seas are ahead?? I hate projects like that where you just know everything is a bad decision and nobody else will listen.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
SQL_ME_RICH (5/7/2012)
opc.three (5/7/2012)
Yes, I saw your other post...how have you survived the project? I may have tried torching the entire place by now 😛Trust me - it has been an excruciating exercise in patience, and picking and choosing my battles as wisely as possible...
Put it this way - if you could have been a fly on the wall to some of the conversations I have had to have over the past 30 days on this project, you would've laughed your butt off (if it wasn't so eff'd up).
Sean Lange (5/7/2012)[That sounds like a nasty situation to be in. It sounds like you have weathered the storm though and calmer seas are ahead?? I hate projects like that where you just know everything is a bad decision and nobody else will listen.
Well - I'm working on documentation now, and the other person that was involved in this project now has full exclusivity to the database. That has served to lessen my stress (in the way of having to listen to less whining), and gives me a chance to focus on other tasks that are more germaine to my growing concerns of actually rolling this thing out into PROD (which BTW - they kind of already have - half pregnant so-to-speak). I tried to warn them against that, but along with a lot of other topics for discussion - it was disregarded, and then last week (literally like the day after they moved some PROD pieces into the instance) guess what happened? BOOM! Drive failure on the external array. System went down, and they didn't even know when (nor why at the time).
And they STILL are looking to put those pieces back on while the other system is still being completed.
So - I'm doing documentation (another thing that is seriously lacking).
:blink:
-
I have found in the past that documentation for systems like this don't exist or are horribly wrong more often than not. I suspect it is either the original "author" who doesn't want to document because it might show how incredibly incompetent they really are, or somebody else (like yourself) has to try to put humpty together again. I feel your pain as I have been in those shoes before.
I am actually currently working on trying to decipher some issues with a decade old Access "application" that suddenly is producing some strange results. I am finding the code the be along the lines of wonderful that you are dealing with. Makes me want to take out my eyeballs and peel them.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Wow, Rich, your two threads are certainly not of the kind one would expect on a Newbies thread. Seems you have all the right questions and the accompanying answers already and are just looking for confirmation? I'd suggest you post Q's in a thread on the Database Design Forum here, since otherwise they'll just get drowned in the sea of what are real Newbies questions.
Just my two cents of advise.
-
I guess the upside to it all is that with systems like this out there, we will never be short on opportunities to fill in, but when you name gets attached to a project like this - well, it just leaves a lot to be desired.
I am only a contractor at this moment, and have had to park my concerns (as if I was an employee) at the door. All I can do is the best I can do - nothing more, but certainly nothing less.
I have a feeling that with the way this thing has gone - my involvement will be needed again before it's over, but for now - Documentation and working with I.T. to put together the best possible backup rotation for the thing as possible.
Sorry to hear that you too are in cleaning up after someone else. I think this poor system never really had much of a chance for improvement (beyond what little I was able to coax into it before relinquishing control of it), but at least it have some decent file structure now, and some table partitioning to alleviate some of the load that it was maintaining in it's old environment.
-
Jan Van der Eecken (5/7/2012)
Wow, Rich, your two threads are certainly not of the kind one would expect on a Newbies thread. Seems you have all the right questions and the accompanying answers already and are just looking for confirmation? I'd suggest you post Q's in a thread on the Database Design Forum here, since otherwise they'll just get drowned in the sea of what are real Newbies questions.Just my two cents of advise.
Hi Jan -
I appreciate that input. I honestly did not know if what I was looking at though was correct or not, hence why I put it here (because if it was, and I was just being a 'newb', then that last thing I want to do is put up a posting where serious discussions are taking place by seasoned data architects).
That being said - I will make a more concerted effort to ensure that I am in the correct forum for these discussions in the future.
Thank you!
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply