|
|
Posted by Erland Sommarskog on 06/11/07 22:01
Wojto (jestem.wojtek@interia.pl) writes:
> Another day, another problem... :-)
>
> I've got something like this:
>
> CREATE TABLE A (
> pk_A INT CONSTRAINT primarykey_A PRIMARY KEY
> );
>
> CREATE TABLE B (
> pk_B INT CONSTRAINT primarykey_B PRIMARY KEY,
> fk_B_A INT CONSTRAINT foreginkey_B REFERENCES A
> );
>
> and I want to force connection 1 to 1..n between tables A and B.
> Inserting into B and than into A is impossible because of reference
> constraint in table B. So I insert into A and then into B. To enforce
> the relation (1-1..n) I'll probably need a trigger.
> I wrote it like this:
If I understand this correctly, you (or rather your professor) want to
enforce that for each row in A, there is at least one row in B.
While this requirement makes sense in many cases - for instance "an
Order must have at least one line item", it is not enforceable in SQL
Server, since it would require commit-time triggers/constraints/assertions.
That is, you would need a mechanism to violate the rules while in
the transaction, but when you commit you must have sorted things out.
There a few possible ways around it, but none of them are palatable.
Once is to have mutual constraints, and then use ALTER TABLE to
disable the constraints you temporary need to violate, and enable the
constraint at the end again. But since this would recheck the entire
table, this is not a viable option. And in any case, you should use
metadata operations in application code.
A slightly better option is to have a view that covers both tables,
and that only give permissions on that view. The view would have an
instead of trigger that inserts data into the right places.
And, of course, rather than a view, a stored procedure could do.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|