CODEWITHSUNDEEP
sql-serverdatabaseconstraints
Ryan Ische
Ryan Ische

Reputation: 3676

SQL Server 2000 constraint involving column on different table

I would like a constraint on a SQL Server 2000 table column that is sort of a combination of a foreign key and a check constraint. The value of my column must exist in the other table, but I am only concerned with values in the other table where one of its columns equal a specified value. The simplified tables are:

import_table:
part_number  varchar(30)
quantity     int

inventory_master:
part_number  varchar(30)
type         char(1)

So I want to ensure the part_number exists in inventory_master, but only if the type is 'C'. Is this possible? Thanks.

Upvotes: 0

Views: 630

Answers (2)

Node
Node

Reputation: 22345

You could use an INSTEAD OF INSERT trigger to emulate that behaviour.

Check value existence when an insert is about to occur.

Upvotes: 1

Ilya Kochetov
Ilya Kochetov

Reputation: 18463

You could use a trigger on INSERT and UPDATE statements which would ensure the integrity

CREATE TRIGGER syntax: http://msdn.microsoft.com/en-us/library/ms189799.aspx

Upvotes: 1

Related Questions