Referencial Integrity Constraints Questions ?

Hi, Group
Please let me know how can I do the following:
Will prevent the user from deleting an entry if the value is used in a
foreign key.
Best regard
MarioMario,
If you have Foreign key setup, you should not be able to delete a parent
before deleting the child. Below is an example showing FK in effect.
e.g.
create table t1(i int primary key, j int)
create table t2(k int primary key,i int foreign key references t1(i),l int)
go
insert t1 values(1,1)
insert t2 values(2,1,2)
go
--this delete will fail
--due to FK constraint
delete t1
where i=1
go
drop table t2,t1
go
-oj
http://www.rac4sql.net
"Mario Reiley" <mreiley@.cantv.net> wrote in message
news:%23uVCIbV2DHA.1736@.TK2MSFTNGP09.phx.gbl...
> Hi, Group
> Please let me know how can I do the following:
> Will prevent the user from deleting an entry if the value is used in a
> foreign key.
> Best regard
> Mario
>|||OJ describes the default behavour of FK constraints, which seems to be the
behavour you want... You may add the cascade option to the constraint, which
will cause the child records to be automatically deleted when the parent row
is deleted. (Just an FYI)
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mario Reiley" <mreiley@.cantv.net> wrote in message
news:%23uVCIbV2DHA.1736@.TK2MSFTNGP09.phx.gbl...
> Hi, Group
> Please let me know how can I do the following:
> Will prevent the user from deleting an entry if the value is used in a
> foreign key.
> Best regard
> Mario
>|||OK my Friends but what can i do then and thanks so much for yours response.
Best Regard
Mario
"Mario Reiley" <mreiley@.cantv.net> escribió en el mensaje
news:%23uVCIbV2DHA.1736@.TK2MSFTNGP09.phx.gbl...
> Hi, Group
> Please let me know how can I do the following:
> Will prevent the user from deleting an entry if the value is used in a
> foreign key.
> Best regard
> Mario
>|||OK, let me try explaining.
I have two tables:
Personal (Is my Parent table)
Profession ( Is my Child table)
In Parent is: ProfessionId field.
The thing is how Can I void Delete a row in Profession table when there is a
value in Personal table.
Example:
IF the user not put the profession in the Personal record then no problem
the profession row Is OK (Delete) else the row profession can't be deleting.
Note: Sorry my English.
Best regard
MArio
"Mario Reiley" <mreiley@.cantv.net> escribió en el mensaje
news:%23uVCIbV2DHA.1736@.TK2MSFTNGP09.phx.gbl...
> Hi, Group
> Please let me know how can I do the following:
> Will prevent the user from deleting an entry if the value is used in a
> foreign key.
> Best regard
> Mario
>|||Here's how to add a the relationship.
ALTER TABLE Personal
ADD FOREIGN KEY (ProfessionId)
REFERENCES Profession (ProfessionId)
ON DELETE NO ACTION
"Mario Reiley" <mreiley@.cantv.net> wrote in message
news:%23gTfxJd2DHA.2620@.TK2MSFTNGP09.phx.gbl...
> OK, let me try explaining.
>
> I have two tables:
>
> Personal (Is my Parent table)
> Profession ( Is my Child table)
>
> In Parent is: ProfessionId field.
>
> The thing is how Can I void Delete a row in Profession table when there is
a
> value in Personal table.
>
> Example:
>
> IF the user not put the profession in the Personal record then no problem
> the profession row Is OK (Delete) else the row profession can't be
deleting.
>
> Note: Sorry my English.
>
> Best regard
> MArio
> "Mario Reiley" <mreiley@.cantv.net> escribió en el mensaje
> news:%23uVCIbV2DHA.1736@.TK2MSFTNGP09.phx.gbl...
> > Hi, Group
> >
> > Please let me know how can I do the following:
> >
> > Will prevent the user from deleting an entry if the value is used in a
> > foreign key.
> >
> > Best regard
> > Mario
> >
> >
>