Re: One to One

Florian Reiser wrote:
> Hello,
>
> I think you should try it the following way:
>
> CREATE TABLE CUSTOMER
> (
> customer_id NUMBER NOT NULL,
> customer_type VARCHAR2(32 BYTE) NOT NULL,
> customer_display_name VARCHAR2(35 BYTE) NOT NULL,
> primary_contact_id NUMBER NOT NULL,
> CONSTRAINT pk_customer PRIMARY KEY (customer_id),
> CONSTRAINT idx_primary_contact UNIQUE (primary_contact_id)
> )
>
> CREATE TABLE PERSON
> (
> person_id NUMBER NOT NULL,
> first_name VARCHAR2(15 BYTE) NOT NULL,
> middle_initial CHAR(1 BYTE),
> last_name VARCHAR2(25 BYTE) NOT NULL,
> telephone_number CHAR(10 BYTE) NOT NULL,
> email_address VARCHAR2(35 BYTE),
> notes VARCHAR2(50 BYTE),
> CONSTRAINT pk_person PRIMARY KEY (person_id)
> )
>
> ALTER TABLE CUSTOMER ADD (CONSTRAINT fk_primary_contact FOREIGN KEY
> (Primary_contact_id) REFERENCES PERSON (person_id));
>
> The Foreign key has to be the other way around because CUSTOMER is
> referencing PERSON.
>
> Greets
> Florian
>
>
> "rm" <groups_at_rlmoore.net> schrieb im Newsbeitrag
> news:1174269711.533298.245540_at_e1g2000hsg.googlegroups.com...

>> I am trying to create a one to one releationship between two tables.
>> The tables and the constraints are described below. I do not believe
>> that I am accomplishing my goal.May I please have a input?
>>
>> The business rull is that one person should exist as the primary
>> contact for the customer.
>>
>>
>> CREATE TABLE CUSTOMER
>> (
>> customer_id   NUMBER   NOT NULL,
>> customer_type   VARCHAR2(32 BYTE)   NOT NULL,
>> customer_display_name   VARCHAR2(35 BYTE)   NOT NULL,
>> primary_contact_id   NUMBER   NOT NULL,
>> CONSTRAINT pk_customer   PRIMARY KEY   (customer_id),
>> CONSTRAINT idx_primary_contact   UNIQUE   (primary_contact_id)
>> )
>>
>> CREATE TABLE PERSON
>> (
>> person_id   NUMBER   NOT NULL,
>> first_name   VARCHAR2(15 BYTE)   NOT NULL,
>> middle_initial   CHAR(1 BYTE),
>> last_name   VARCHAR2(25 BYTE)   NOT NULL,
>> telephone_number   CHAR(10 BYTE)   NOT NULL,
>> email_address   VARCHAR2(35 BYTE),
>> notes   VARCHAR2(50 BYTE),
>> CONSTRAINT pk_person PRIMARY KEY (person_id)
>> )
>>
>> ALTER TABLE PERSON ADD (CONSTRAINT fk_primary_contact FOREIGN KEY
>> (person_id) REFERENCES CUSTOMER (primary_contact_id));

You can not enforce a 1:1 with a referential constraint.

The business rule requires an referential constraint AND a unique constraint.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org