Foreign Key constraints - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Foreign Key constraints

Author

Topic

ben_53
Yak Posting Veteran

67 Posts

Posted - 2011-08-02 : 15:08:01

Hi Everyone,

I want to update a table with column named status with 'Fail' if the foreign key constraints are not matched.

Lets take an example.

my_Table is tablename
columns: id, Pname, Loc, Ptype, Pcategory, status
now, id Pname and Loc are inserted values
Ptype , Pcategory are derived values by a logic.

Now I have reference tables Type and Category.
So, value Ptype should look in "Type" ref to check if that derived type exists in ref table.
similarly check for Pcategory value in underlying ref table.

If not then update column "status" with 'Fail'.

please write a general query.
Many Thanks.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-08-02 : 15:14:17

Something like this (best I can give ya without DDL)

UPDATE	m
SET	status = 'Fail'
FROM	my_Table m
LEFT JOIN
	Type t
On	t.keyvalue = m.keyvalue
WHERE	t.someValue IS NULL;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-03 : 00:56:27

you need to check both type and category or only one?
also what happens if one is matched and other cannot be found?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-08-03 : 01:36:13

Was wondering the same thing Visakh :)

Thought I'd show an example of how to proceed to get the OP going, but you're right, my example only works for one or the other. Not both.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources