dcsimg
www.webdeveloper.com
Results 1 to 6 of 6

Thread: Simple yet tricky and tough problem..

  1. #1
    Join Date
    Jun 2009
    Posts
    2

    Post Simple yet tricky and tough problem..

    hii every one,
    lets straight away ask my question..

    i have three tables
    table1
    -----------------
    id1 primary key <indentity column>
    tb1
    -----------------

    table2
    -----------------
    id2 primary key <indentity column>
    tb2
    -----------------

    table3
    -----------------
    id primary key <identity column>
    id3 foreign key (referencing table1-->id1,table2-->id2)
    -----------------
    **table3 has two referential constraint for table1 and table2

    now suppose i insert a tuple (say 'iop' in table1 and 'rey' in table2)
    and then i want to insert the same tuples in table3......
    THEN THE PROBLEM ARISES
    when i insert 'iop' then the table2's referential constarint is violated and conflicts
    and again
    when i insert 'rey' then the table1's referential constarint is violated and conflicts

    How do i basically implement a Many-to-one relationship in Microsoft Sql server 2005?

  2. #2
    Join Date
    Jan 2009
    Posts
    14

    Seems okay to me

    Table 1 primary key of 'iop' has been inserted.

    Table 2 primary key of 'rey' has been inserted.

    Table 3 primary key of 'you did not specify with 'ioprey' as the foreign key should work. Is that what you did?

  3. #3
    Join Date
    Jun 2009
    Posts
    2

    let me get more clearer

    the sql queries related to the problem is:

    create table table1(id1 decimal identity(12,2),id11 varchar primary key(id1))

    create table table2(id2 decimal identity(12,2),id22 varchar primary key(id2))

    create table table3(id3 decimal identity(12,2),id33 decimal primary key(id3)
    foreign key(id33) references table1,foreign key(id33) references table2)

    insert into table1 values('i')
    insert into table2 values('p')

    insert into table3 values(14)


    **the last line raises an error saying:
    The INSERT statement conflicted with the FOREIGN KEY constraint "FK__table3__id33__20C1E124". The conflict occurred in database "gh", table "dbo.table2", column 'id2'.
    The statement has been terminated.


    now suppose the value "14" had been present in both table 1 and table2
    then it would have been succesfully inserted into table3

    HOPE U GET THE PROBLEM

    ALL OF THE ABOVE QUERIES HAVE WRITTEN IN RESPECT TO MICROSOFT SQL SERVER 2005

  4. #4
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    then drop the constraint.

  5. #5
    Join Date
    Jan 2009
    Posts
    3,346
    Or in lou of dropping the constraint have 3 fields in table 3. One connecting table 1 PK and the other connecting to table 2 and the third of course being the PK.

  6. #6
    Join Date
    Jan 2009
    Posts
    14
    I don't know the DBMS syntax, but I think this
    "foreign key(id33) references table1,foreign key(id33) references table2)"
    should be more like this
    Foreign Key (id1x) references table1(id1)
    Foreign Key (id2x) references table2(id2).

    Also, the insert would be insert table3 values (12,'i','p');

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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
  •  
HTML5 Development Center



Recent Articles