Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Integrity: How can I enforce at least one child record

Posted on 2006-10-26
19
Medium Priority
?
359 Views
Last Modified: 2008-02-01
That question has probably been asked before, but I haven't found a trace.

I have the following:

    create table parent (
        code varchar(100) not null primary key);

    create table child (
        parentCode varchar(100) not null foreign key references parent(code),
        code varchar(100) not null);

How can I make sure a record in 'parent' can't exist without at least one record in 'child' ? I can put a trigger for the deletion of 'child', that's easy, but what about insertions ?
I.e.
    insert into parent values ('a')

should fail, but

    begin tran;
    insert into parent values ('a');
    insert into child values ('a', 'a');
    commit tran;

should work.

This seems like such a standard issue, I'm surprised it's not built in FOREIGN KEY constraints already (or I haven't found it !!).
I'm using SQL Server 2005.
One solution I can think of is postponing the 'real' validation until commit-time (à la Oracle's 'deferred'), but I don't know how !

Thanks a lot.
0
Comment
Question by:dbman666
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
  • +2
19 Comments
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 17814853
You can create a FK constraint that will prevent a child from being inserted w/o a parent, but not vice versa.

In particular, there is *NO* way to do this in SQL Server:
>> I.e.
    insert into parent values ('a')
should fail, but
    begin tran;
    insert into parent values ('a');
    insert into child values ('a', 'a');
    commit tran;
should work. <<

There is no deferred resolution in SQL Server, so this cannot be done.
0
 
LVL 11

Expert Comment

by:rw3admin
ID: 17815102
I am sure you can write a 'for insert' and 'for delete' triggers on parent that will do the job for you

rw3admin
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 17815149
I'd love to see a sample, because I don't see how it could possibly be done.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17815226
I have to agree basically with scottpletcher that you cannot really do that.

actually, the FOREIGN KEY constraint is to ensure that there is a parent row for the child record, it's not about a parent row to fail when there is no child record.

however, just as an idea, you could solve your requirement like this:

INSERT TRIGGER on the parent table, which would automatically insert a "dummy" child record on the child table
INSERT TRIGGER on the child table, that would automatically delete the "dummy" record for the related parent record
DELETE TRIGGER on the child table, that would rollback (+RAISERROR) the delete if the delete was deleting the last child record for that parent

0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 17815262
But what if no insert on the child table ever came?  You would have allowed an insert on the parent w/o an insert on the child.
0
 
LVL 11

Expert Comment

by:rw3admin
ID: 17815273
yes AngelIII thats what I am saying, a for insert will automatically insert a record in a child and in for delete user can write a code to first query 'deleted' table and delete records from child before deleting from parent, a for delete can also be a instead of delete but with same logic

rw3admin
0
 
LVL 11

Expert Comment

by:rw3admin
ID: 17815306
oh and yea angelIII is right we would need a 'instead of delete' on child table as well.

rw3admin
0
 

Author Comment

by:dbman666
ID: 17815376
I wanted to avoid writing stored procs for that, but it seems I am doomed !

Is there any way, using the new C# triggers in SQL Server 2005, to trap the 'commit' and actually do some work there ??
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 17815395
But then you're not enforcing a rule you're just dodging around it.  Inserting a dummy row yourself is NOT the same as insuring that an actual data row already exists in the table **or has been inserted in the same batch** -- that part I don't see *ANY WAY* to do.
0
 
LVL 39

Expert Comment

by:appari
ID: 17815476

how about creating a view joining parent and child tables disable insert permission on parent and child tables and allow inserting data in to the view instead of tables directly. Write an instead of insert trigger on the view to insert data in to parent and child tables.
0
 
LVL 11

Accepted Solution

by:
rw3admin earned 336 total points
ID: 17815482
ok if we step back and take a look at it, its basically a classic case of chicken and egg, what came first... ofcourse we all know a dirty joke around the same theme but thats outside the scope of discussion here, and I will be kicked out of EE by Scott.
according to dbman666
>>insert into parent values ('a')
    should fail, but

    begin tran;
    insert into parent values ('a');
    insert into child values ('a', 'a');
    commit tran;
    should work.<<


then I would suggest allow data manipulation in these tables ONLY through application, and write your logic in that application, ofcourse you WILL end up writing some sort of triggers on parent and child tables to only allow your application... :)
0
 
LVL 39

Expert Comment

by:appari
ID: 17815502

i think you should allow insert on child table and disable inserts on parent table.
0
 

Author Comment

by:dbman666
ID: 17815541
appari: That's a new approach. Interesting.

rw3admin: Chicken and egg in a way, yes. But if you delay validation until commit-time, it becomes trivial. That's why I'm looking at a way to trap the start and end of the commit ! I know traces do it, but it's asynchronous, so it doesn't cut it.
0
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 332 total points
ID: 17815558
>> But if you delay validation until commit-time <<

"Ay, there's the rub" as Shakespeare might say -- there's no way to do that in SQL Server.
0
 
LVL 39

Assisted Solution

by:appari
appari earned 332 total points
ID: 17815566
>>appari: That's a new approach. Interesting.
why dont you give it a try?
do you want sample code? I am leaving for work now. i can respond may be  after 1.5 hours. someone else can provide sample based on my idea or after 1.5 hours i will post sample code.
0
 

Author Comment

by:dbman666
ID: 17815578
No that's fine. I just want to think of the pros and cons. Thanks.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question