Results 1 to 3 of 3
  1. #1
    Join Date
    May 2013
    Posts
    2

    Unanswered: unable to create one-to-one relationship

    I'm unable to create one-to-one relationship in multiple databases ( mysql, db2). here is my declaration:

    --<ScriptOptions statementTerminator=";"/>

    CREATE SCHEMA "mydb";

    CREATE TABLE "mydb"."first" (
    "id" BIGINT NOT NULL
    )
    DATA CAPTURE NONE
    COMPRESS NO;

    CREATE TABLE "mydb"."second" (
    "id" BIGINT NOT NULL
    )
    DATA CAPTURE NONE
    COMPRESS NO;

    ALTER TABLE "mydb"."first" ADD CONSTRAINT "prima_PK" PRIMARY KEY
    ("id");

    ALTER TABLE "mydb"."second" ADD CONSTRAINT "seconda_PK" PRIMARY KEY
    ("id");

    ALTER TABLE "mydb"."first" ADD CONSTRAINT "first_second_FK" FOREIGN KEY
    ("id")
    REFERENCES "mydb"."second"
    ("id")
    ON DELETE CASCADE;
    this declaration produces one-to-many relationship both on mysql and db2. anyone could help me on this topic ?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Since a 1:1 relationship is forbidden by relational algebra (it denotes that each table is a logical subset of the other, which is impossible), very few if any SQL database engines will support it.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jan 2009
    Location
    United Kingdom
    Posts
    77
    You could create a third table with a foreign key to the other two tables, and the foreign key are Unique Indices. However, this is normally for one to many relationship

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •