Re: trigger question

Hi Sybrand,

I'd like to comment on your comment that if you have two tables with a 1-1 relationship, it's a case of bad table design. Sometimes it is necesary for data integrity.. For example if I have a table with some basic personal info. This will contain e.g. first name, last name and address. Now let's say the address as a whole is optional, but if you decide to fill the address columns, the address line 1 and zip are mandatory. How do you think to implement this on table level? You could do it with a trigger, but it is relatively a lot of work for such a simple table. It's easier to create two tables, one for personal info, with a foreign key to the address table. Make the foreign key column optional, and in the address table make the address line 1 and zip mandatory and voila! Integrity is ensured. Looks much easier.. Yes, the insert operation is a bit more complicated, but personally I always like it when the datamodel itself applies as much rules as possible.

I agree with you that in most cases two tables with a 1-1 relationship is bad design, but still I think that sometimes you may wish to choose to do so. What do you think?

Cheers,

Bastiaan Schaap
Oracle web development,
Desyde BV - Baarn
http://www.desyde.nl/
tel. +31355411711

Sybrand Bakker <postbus_at_sybrandb.demon.nl> wrote in message news:965242751.28509.0.pluto.d4ee154e_at_news.demon.nl...
> No, the syntax I provided you with is correct. Either trust me or read the
> manual, and you will see I am correct.
> Also AFAIK a column can not act as a primary key and a foreign key at the
> same time, or you have a case of bad table design. If there is a truly
> 1-on-1 relationship between table1 and table2, one of the two tables
> shouldn't have existed at all.
>
> Hth,
>
> Sybrand Bakker, Oracle DBA
>
>
>
> "luio" <lctNOlcSPAM_at_hotmail.com.invalid> wrote in message
> news:1cc42a50.73022d87_at_usw-ex0104-028.remarq.com...
> > alter table table2 add constraint <whatever name> foreign key
> > (id) references table1 *on delete cascade*
> > (provided table1.sid is the primary key of that table)
> >
> > there is on exception. how about the table2.id is the primary
> > key of table2. but sid the primary key of table?
> > then
> > I use
> > alter table table1 add constraint <whatever name> foreign key
> > (id) references table2 *on delete cascade*
> > to implement the delete on table 2 whenever I delete sth from
> > table1?
> >
> >
> >
> > thanks
> >
> >
> >
> >
> > The solution I posted previously should also work, but it is
> > more cumbersome.
> >
> > Hth,
> >
> >
> >
> >
> > -----------------------------------------------------------
> >
> > Got questions? Get answers over the phone at Keen.com.
> > Up to 100 minutes free!
> > http://www.keen.com
> >
>
>
Received on Thu Aug 03 2000 - 00:00:00 CDT