| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.tools -> Re: trigger question
Bastiaan Schaap <bschaap_at_desyde.nl> a écrit dans le message :
newscache$84cpyf$5kj$1_at_lnews.actcom.co.il...
> 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.
You can do it with a check constraint at table level:
v734> create table t (name varchar2(10) primary key, address1 varchar2(50), 2 address2 varchar2(50), zip varchar2(10), 3 constraint t_chk check ((address1 is null and zip is null) 4 or (address1 is not null and zip is not null)));
Table created.
v734> insert into t (name) values('JONES');
1 row created.
v734> insert into t (name, address1, zip) values ('SMITH','10 Main Street','92000');
1 row created.
v734> insert into t (name, address1, zip) values ('SMITH','10 Main Street',null); insert into t (name, address1, zip) values ('SMITH','10 Main Street',null)
*
ERROR at line 1:
v734> insert into t (name, address1, zip) values ('SMITH',null,'92000'); insert into t (name, address1, zip) values ('SMITH',null,'92000')
*
ERROR at line 1:
>
> 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
> > >
> >
-- Have a nice day MichelReceived on Thu Aug 03 2000 - 00:00:00 CDT
 |
 |