#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2011
    Posts
    3
    Rep Power
    0

    One to one Relationship


    Hi,
    I am new to Firebird RDBMS and I want to use Firebird database in my VisualStudio projects, with the support of Microsoft Entity Framework.
    Since in my projects, luckily due to the Entity Framework, I don't have to learn the syntax of the Firebird SQL Commands, I used Firebird Maestro to create my database.
    Now I have a problem: I need to create a one-to-one relationship between two tables, but Firebird Maestro allows me to create only a Foreign Key, which sets up a one-to-many relationship.
    What may I do? May someone give me at least some information about how to create a one to one relationship by SQL statements?
    Thanks!!
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,055
    Rep Power
    383
    Give the Foreign Key field a unique index.

    Why do you want a one to one?
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2011
    Posts
    3
    Rep Power
    0
    Originally Posted by clivew
    Why do you want a one to one?
    There are two tables, Bill Entries (which stores the entries of the bills, by which you can also buy a cupon) and Cupons.
    When I have a Bill Entry which is related to the purchase of a cupon, I need to bind that entry with a single entry of the table Cupons. I need to keep the two tables separated.

    Originally Posted by clivew
    Give the Foreign Key field a unique index.
    May you kindly explain me better? When I set up a Foreign Key, I may set the following fields: Foreign Colum (in the referencing table), Foreign Table, Foreign Table Columns, Update and Delete rule.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,055
    Rep Power
    383
    I did not mean to confuse you.
    Set up a unique index on the same field as the foreign key.
    The unique index will prevent the database ffrom accepting duplicate values.

    However, unless there is some strong reason to do otherwise, I would suggest making that field the Primary Key in both tables and then add the foreign key constraint.
    That would be a superior design.

    Clive
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2011
    Posts
    3
    Rep Power
    0
    Originally Posted by clivew
    However, unless there is some strong reason to do otherwise, I would suggest making that field the Primary Key in both tables and then add the foreign key constraint.
    That would be a superior design.
    Clive
    In my case it's impossible, since the Bill Entries Table has as primary key the ID of the entry, it wouldn't make sense to set the "CuponId" as Primery Key.
    Thanks for help, I will give a try tomorrow.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,055
    Rep Power
    383
    Originally Posted by loripino21
    In my case it's impossible, since the Bill Entries Table has as primary key the ID of the entry, it wouldn't make sense to set the "CuponId" as Primery Key.
    Thanks for help, I will give a try tomorrow.
    For the record. I did not say make CouponId the PK in both tables
    Make the bill entry PK the PK of the coupons table (and drop the couponId field from the bill entries table).

    Of course, you know your situation better than me. I am talking about a better technical schema design.

    Clive

IMN logo majestic logo threadwatch logo seochat tools logo