Constraint between tables in SQL Server, deleting one should delete the other

Question

I am creating two tables RegistrationHeader and RegistrationDetail. How can i add a constraint that when i delete RegistrationHeader table to automaicaly delete the RegistrationDetail table.

create table RegistrationHeader(
    RegistrationNo numeric
    ,BillingID varchar(30) not null
    ,RegistrationDate date not null
    ,PaymentType varchar check (PaymentType = 'CC' or PaymentType = 'PO' or PaymentType = 'Check') not null
    ,CCNumber numeric check(CCNumber >= 15 and CCNumber <=16)
    ,PONumber varchar(30)
    ,CheckNumber varchar(10)
    ,primary key(RegistrationNo)
    ,foreign key(BillingId) references Person(UserID) 
    ,constraint CC_CCNumber_constr check(
        (PaymentType = 'CC' and CCNumber is not null)
        or
        (PaymentType != 'CC' and CCNumber is null)
    )
    ,constraint PO_PONumber_constr check(
        (PaymentType = 'PO' and (PONumber is not null or PONumber != ''))
        or
        (PaymentType != 'PO' and PONumber is null)
    )
    ,constraint CheckNumber_type_constr check(PaymentType != 'Check' and CheckNumber is null)
);

create table RegistrationDetail(
    RegistrationNo numeric
    ,LineNumber numeric
    ,CourseID numeric(10) not null
    ,AttendeeID varchar(30) not null
    primary key(RegistrationNo,LineNumber)
);

Thanks for the help!

**SQL** is just the **query language** - but **not** a database product.... — marc_s, Jan 20 '14 at 19:38

score 5 · Accepted Answer · answered Jan 20 '14 at 19:42

5

You will need to

1) Set up foreign key relations between RegistrationHeader.RegistrationNo and RegistrationDetail.RegistrationNo

2) Add ON DELETE CASCADE to RegistrationDetail Foreign Key definition

I haven't tested this, but I believe this should be what you're looking for in SQL Server

ALTER TABLE RegistrationDetail
ADD CONSTRAINT FK_RegistrationHeader_RegistrationDetail_Cascade
FOREIGN KEY (RegistrationNo) REFERENCES RegistrationDetail (RegistrationNo) ON DELETE CASCADE

answered Jan 20 '14 at 19:42

Tom

7,640
1
23
47

If i do this i get: Introducing FOREIGN KEY constraint 'FK_RegistrationHeader_RegistrationDetail_Cascade' on table 'RegistrationDetail' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. – setlio Jan 20 '14 at 20:50
See the answer [here](http://stackoverflow.com/questions/851625/foreign-key-constraint-may-cause-cycles-or-multiple-cascade-paths). You may need to create triggers if you're not able to modify your constraints. – Tom Jan 20 '14 at 20:59

score 1 · Answer 2 · answered Jan 20 '14 at 19:42

Constrains are affecting only database columns such as primary/foreign keys or regular columns, not whole tables. You can add OnDelete or OnUpdate constraints for foreign keys to delete/keep the refrences from one table to other but not the whole table. That is mixing DML(select,update,insert,delete) with DDL(create,drop). Try to read more about DML and DDL.

Here's a good article about that

Constraint between tables in SQL Server, deleting one should delete the other

2 Answers2