October 1, 2009 at 6:20 am
Hello,
I have a table tbl_person that has a Clustered Index Person_ID and also some FK’s. It has data on it. Now I am introducing a new table, Staff_in_EDT, and it has 2 columns as PK (composite key); they are Dept_No and ARS_No. Dept_No and ARS_No combination will always be unique in the new table.
Now both these fields exist in tbl_person, also. Dept_No is a FK in tbl_person. (It references tbl_Dept.Dept_No). ARS_No is not a key in tbl_person. Unfortunately, due to bad data, ARS_No is not unique in tbl_person (for some, it was not generated yet, and for some it was human data entry). So, Dept_No and ARS_No combination is not always unique in tbl_person, but from now on, with the right coding, this is being enforced, but old data cannot be cleaned up now, due to some factors.
Now, I would like to have referential integrity in Staff_in_EDT table. I would like to make sure that Staff_in_EDT.ARS_No exists in tbl_person.ARS_No, before the record is being added. This will make sure the new data is always accurate.
I tried this:
ALTER TABLE [dbo].[Staff_in_EDT] WITH CHECK ADD CONSTRAINT [FK_ Staff_in_EDT_tbl_person]
FOREIGN KEY([ARS_No],[Dept_No])
REFERENCES [dbo].[tbl_person] ([ARS_No],[Dept_No])
or just on one key:
ALTER TABLE [dbo].[Staff_in_EDT] WITH CHECK ADD CONSTRAINT [FK_ Staff_in_EDT_tbl_person]
FOREIGN KEY([ARS_No])
REFERENCES [dbo].[tbl_person] ([ARS_No])
It gives the error:
There are no primary or candidate keys in the referenced table 'dbo.tbl_person' that match the referencing column list in the foreign key 'FK_ Staff_in_EDT_tbl_person'.
Could not create constraint. See previous errors.
Can you please help me on how to create this referential integrity, so that no record is added in Staff_in_EDT, if the ARS_No does not exist in tbl_person. If I can add a check on both the columns, that will be great, too. (Check if the combination of ARS_No and Dept_No exist in tbl_person).
Thanks
Dan.
October 1, 2009 at 7:43 am
As your error message says, unless there is a Primary key or Unique constraint on the columns you want to reference in tbl_Person, you can't add the foreign key constraint.
You've already said you can't clean up the data (which would be by far the best thing to do), so you will have to create an INSERT and UPDATE trigger on the Staff_in_EDT table that checks for the existence of a matching row in tbl_Person.
October 1, 2009 at 8:15 am
Ian
If I make the two columns part of the primary key in tbl_person, will that help me to add the FK lookup? (3 columns in the new table looking up 3 columns in tbl_person) for referential integrity?
If not, is triggers my only option?
-Dan
October 1, 2009 at 8:21 am
My main and only emphasis right now is that ARS_No does not get entered in Staff_in_EDT if it is not in tbl_person.
If it was unique in tbl_person, I would have been ok. But alas, it is not so.
Just want to make sure what are the options (other than triggers), if any available?
Thanks again
Dan
October 1, 2009 at 8:26 am
As an unique constraint cannot be created with the NOCHECK option, I would be inclined to put some junk data into the ARS_No column of tbl_Person. Assuming that Person_Id is the PK of tbl_Person then something like the following may work:
-- get rid of duplicates
UPDATE D
SET ARS_No = NULL
FROM
(
SELECT *
,ROW_NUMBER() OVER (PARTITION BY Dept_No, ARS_No ORDER BY Person_ID) AS RowNum
FROM dbo.tbl_Person
WHERE ARS_No IS NOT NULL
) D
WHERE RowNum > 1
UPDATE D
SET ARS_No = Junk
FROM
(
SELECT *
,- ROW_NUMBER() OVER (PARTITION BY Dept_No ORDER BY Person_ID) AS Junk
FROM dbo.tbl_Person
WHERE ARS_No IS NULL
) D
ALTER TABLE dbo.tbl_Person
ADD CONSTRAINT UN_Person_Dept_No_ARS_No
UNIQUE NONCLUSTERED (Dept_No, ARS_No)
ALTER TABLE dbo.Staff_in_EDT
ADD CONSTRAINT FK_ Staff_in_EDT_tbl_person
FOREIGN KEY(Dept_No, ARS_No)
REFERENCES dbo.tbl_person (Dept_No, ARS_No)
October 1, 2009 at 8:38 am
Ken
ARS_No is not nullable field. So it does have many '11111' values which is the default when there is no ARS_No to enter. (From now on, with new code, this is corrected in tbl_person).
But even if it was nullable, if I had run the query to make them all NULL where they were not unique, that would not satisfy the unique constraint, right? For example, then there will be a lot of NULL values for ARS_No for Dept 'Sales' etc.
Dan
October 1, 2009 at 8:43 am
The idea is to get rid of duplicates and then add in unique negative numbers:
-- get rid of duplicates
UPDATE D
SET ARS_No = 11111
FROM
(
SELECT *
,ROW_NUMBER() OVER (PARTITION BY Dept_No/*, ARS_No*/ ORDER BY Person_ID) AS RowNum
FROM dbo.tbl_Person
WHERE ARS_No <> 11111
) D
WHERE RowNum > 1
UPDATE D
SET ARS_No = Junk
FROM
(
SELECT *
,- ROW_NUMBER() OVER (PARTITION BY Dept_No ORDER BY Person_ID) AS Junk
FROM dbo.tbl_Person
WHERE ARS_No = 11111
) D
October 1, 2009 at 8:46 am
repent_kog_is_near (10/1/2009)
If I make the two columns part of the primary key in tbl_person, will that help me to add the FK lookup?
Yes it will... but don't do it.
If you do, you've destroyed the primary key in tbl_person, making it possible to add more than 1 row with the same Person_ID (as long as they have different ARS_No/Dept_no).
If you really want to go down this route, instead add a UNIQUE constraint with those 3 columns.
I don't see anything wrong with a trigger in this case.
October 1, 2009 at 9:29 am
Ken
Sorry , I had missed the (-) part. It is brilliant.
Dan
October 1, 2009 at 9:31 am
Ian, Thank you. Both your ideas are good. (UNIQUE & trigger).
I will probably use trigger or the (-) idea of Ken's for this.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply