help with script in Query Analyzer
-
Can Anyone help me to draft a script in Query Analyzer to establish a relationship between two tables. A primary key table "center" with key clinicphone and a foreign key table "records" with foreign key clinicphone.
I'm not sure where to begin.....
thanks!
Kristin
-
ALTER TABLE dbo.records ADD CONSTRAINT
FK_records_center_FKNAME FOREIGN KEY
(
clinicphone
) REFERENCES dbo.center
(
clinicphone
)
-
This is the error I get...what do you make of it??
Server: Msg 547, Level 16, State 1, Line 1
ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'FK_records_center_FKNAME'. The conflict occurred in database 'MROresults', table 'center', column 'clinicphone'.
Kristin
-
that means that the syntax is correct, try doing a left join from the child table to the parent table to find missing keys. Correct those (either by recreating the parents or by deleting the orphaned rows).
-
hahahah! you lost me
how do I do that?sorry!!!!!
Kristin
-
Select DISTINCT R.clinicphone from dbo.records R left outer join dbo.center C on R.clinicphone = C.clinicphone where C.clinicphone is null
This will give you a list of clinicphones that exists in records but not in center. You will have to either delete those records, set their values to null, or create new centers with those ids. Or you could update the current data if it's been changed in one table and not the other.
Once you get no records from this query you'll be able to create the relationship.
-
ok, think I got it....did this:
SELECT center.clinicname, center.clinicphone, records.recordnum
FROM records
LEFT JOIN center on
center.clinicphone = records.clinicphone
WHERE center.clinicphone is null
then it executed successfully.
thanks for your help!!!!!
kristin
Kristin
-
What executed successfully?
-
the error you received is because child records exist which are not in the parent/referenced table;
run this query:
SELECT CLINICPHONE AS MISSINGCLINICPHONE,* FROM DBO.CENTER WHERE CLINICPHONE IS NOT NULL AND CLINICPHONE NOT IN (SELECT DISTINCT CLINICPHONE FROM DBO.RECORDS WHERE CLINICPHONE IS NOT NULL)
that will list all records in CENTER which do not have a value to reference in RECORDS
From there, you will need to decide whether to create the refernce records in RECORD, fix existing data in CENTER, or do something else altogether.
hope that helps
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! -
right....the query I have listed above gave me all the records that were "lost" or or did not have a value that could reference my primary key table, center.
Once I did that, I was able to fix my original problem which was to establish the relationship. Then I used your alter table script
ALTER TABLE dbo.records ADD CONSTRAINT
FK_records_center_FKNAME FOREIGN KEY
(
clinicphone
) REFERENCES dbo.center
(
clinicphone
)
which "executed successfully" (that is what I was referencing above)
thanks again for all your help. I learned alot through this.
Kristin
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply