how to set up a 'cascading delte' in mssql

I'm used to working with Access but have moved to MSSQL 2005.
I have these tables:
tblCustomer: customerID (auto), name (nvarchar,50)
tblOrders: orderID (auto), customerID (int)
I use SSMSE. How can I set it up so that the related records in tblOrders
are deleted if I delete a customer.
Thanks.
Jesper
Jesper F (askfortheemail@.ask.com) writes:
> I'm used to working with Access but have moved to MSSQL 2005.
> I have these tables:
> tblCustomer: customerID (auto), name (nvarchar,50)
> tblOrders: orderID (auto), customerID (int)
> I use SSMSE. How can I set it up so that the related records in tblOrders
> are deleted if I delete a customer.
ALTER TABLE tblOrders ADD
CONSTRAINT fk_order_customers FOREIGN KEY (customerID)
REFERENCES tblCustomers (customerID) ON CASCADE DELETE
Although, I must say that from my corners, this looks like a funny
business rule...
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
|||> ALTER TABLE tblOrders ADD
> CONSTRAINT fk_order_customers FOREIGN KEY (customerID)
> REFERENCES tblCustomers (customerID) ON CASCADE DELETE
> Although, I must say that from my corners, this looks like a funny
> business rule...
Those were imaginary tables, but thanks for pointing me in the right
direction.
Jesper

How to separate users in one database (MSSQL 2000)

I wonder how I can separate between different user's rights in the same
database?
In one database -i have user A and user B, how do I set the rights so
that user A can not access user B's objects and opposite? This rights
should be valid for all previously created objects and for objects
created later.
RegardsIf there is truly no interaction between the two sets of objects I question
why they are even in the same database? But there are several things you
can do. First remember that no one by default can access any objects unless
you give them permissions to do so. You can create a role for each set of
objects and add each user to the respective roles. Then only grant
permissions to those objects for that role that you want them to access.
This gives you the flexibility of adding other users later on as well. You
can also create the object with a particular owner to separate them but you
still need to set up the permissions appropriately. Check out GRANT and
DENY in BooksOnLine.
Andrew J. Kelly SQL MVP
<tormorteng@.gmail.com> wrote in message
news:1141309610.551856.52120@.i39g2000cwa.googlegroups.com...
>I wonder how I can separate between different user's rights in the same
> database?
> In one database -i have user A and user B, how do I set the rights so
> that user A can not access user B's objects and opposite? This rights
> should be valid for all previously created objects and for objects
> created later.
> Regards
>