How to check if a column is already a foreign key?

Question

I have a table named Person and column named ID how to check if the identifier is already FOREIGN KEY because I want to do this with this code:

ALTER TABLE Person ADD FOREIGN KEY(ID) REFERENCES Job(ID) ON DELETE CASCADE ON UPDATE CASCADE 

but if the ID is already a FOREIGN KEY, it gives me the following error: "may cause loops or multiple cascade paths" due to a two-cascade condition ... How to check if this is a FOREIGN KEY field to avoid this error?

Answer 1

You want to see in INFORMATION SCHEMA views

Although it is not as complete as it should be. This is the final request you want:

 SELECT KCU1.CONSTRAINT_NAME AS 'FK_CONSTRAINT_NAME' , KCU1.TABLE_NAME AS 'FK_TABLE_NAME' , KCU1.COLUMN_NAME AS 'FK_COLUMN_NAME' , KCU1.ORDINAL_POSITION AS 'FK_ORDINAL_POSITION' , KCU2.CONSTRAINT_NAME AS 'UQ_CONSTRAINT_NAME' , KCU2.TABLE_NAME AS 'UQ_TABLE_NAME' , KCU2.COLUMN_NAME AS 'UQ_COLUMN_NAME' , KCU2.ORDINAL_POSITION AS 'UQ_ORDINAL_POSITION' FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU1 ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2 ON KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG AND KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA AND KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION 

See here for more details.

http://msdn.microsoft.com/en-us/library/aa175805(v=sql.80).aspx

Answer 2

Here is a simple small version

 SELECT TOP(1) a.COLUMN_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS b JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE a ON a.CONSTRAINT_CATALOG = b.CONSTRAINT_CATALOG AND a.CONSTRAINT_NAME = b.CONSTRAINT_NAME WHERE a.COLUMN_NAME = *your column*) 

You can also easily add the table name and database name to the where clause