Re: One to One

From: Florian Reiser <florian.reiser_at_ra-bc.de>
Date: Mon, 19 Mar 2007 08:30:03 +0100
Message-ID: <etle5v$sok$1@online.de>

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));
>
Received on Mon Mar 19 2007 - 02:30:03 CDT