SQL add Foreign key constraint with check

Question

I have the following query which is adding contraint. but in order to add, i want to check if this key has already been used or not?

ALTER TABLE HL7_MessageHierarchy  
 ADD CONSTRAINT fk_vMessageType FOREIGN KEY (vMessageType) 
       REFERENCES HL7_MessageType(vMessageType);

for example. if i have to add a column, i can easily check if the table exists in sysobjects and its respective column exists in syscolumns.

Is it possible to use the query multiple times without GO and without making any error indeed? if yes then how ???

[EDIT]

I don't know why my browser not allowing me to add comments so i am adding to Edit.

I want to check if there exists any foreign key with same name. so if there is no data even then the query can make problem because the key may already be existing. I want to run the above script clean (ofcourse resident data does matter but that is perhaps a straight forward check?) [EDIT]

my bad, i must have known that version is important... I believe its 2005... (will love to know if someone can tell for other versions too)

gbn · Accepted Answer · 2011-06-15 12:41:38Z

3

I assume you mean

check the HL7_MessageHierarchy for values not inHL7_MessageType"

So, a query like this will tell you

SELECT *
FROM HL7_MessageHierarchy H
WHERE NOT EXISTS (SELECT *
      FROM HL7_MessageType T 
      WHERE H.vMessageType = T.vMessageType)

Also, I'd recommend using WITH CHECK too

ALTER TABLE HL7_MessageHierarchy WITH CHECK ADD
 CONSTRAINT fk_vMessageType FOREIGN KEY (vMessageType) 
       REFERENCES HL7_MessageType(vMessageType);

answered Jun 15, 2011 at 12:41

gbn

435k85 gold badges602 silver badges690 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Umer Over a year ago

Thanks, Although its extremely important to check but @AakashM got my question...

AakashM · Accepted Answer · 2011-06-15 13:56:40Z

2

In SQL 2005, the recommended way of checking for the existence of objects is Catalog Views. The one you want is sys.foreign_keys:

IF NOT EXISTS ( SELECT * FROM sys.foreign_keys 
                WHERE name = 'fk_vMessageType' )
BEGIN
    EXEC ('
    ALTER TABLE HL7_MessageHierarchy  
     ADD CONSTRAINT fk_vMessageType FOREIGN KEY (vMessageType) 
           REFERENCES HL7_MessageType(vMessageType)
    ')
END

I have wrapped the creation in EXEC to avoid confusing the parser.

answered Jun 15, 2011 at 13:56

AakashM

63.5k17 gold badges154 silver badges190 bronze badges

1 Comment

Umer Over a year ago

Actually i had some problem on my work place - couldn't accept as usefull answer earlier. Thanks. I needed something like this.

Collectives™ on Stack Overflow

SQL add Foreign key constraint with check

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related