Post reply

Check Constraint Person Relations

  • October 22, 2009 at 1:17 am

    #212864

    Hi Guys. I have 2 tables. TPerson(PersonID, ...) and TPersonRelation(PersonRelationID, Person1ID (FK1), Person2ID(FK2), TypeOfRelationID). All fields are integers. The relations are the follow: PersonID --> Person1ID and the same PK Fields PersonID --> Person2ID. How can I implement both relations, if SQL Server don´t allow me to do 2 relations from the same field (PersonID). A trigger could solve this problem, but ... Do you know if exits a more better solution than the trigger?

    I need to:

    1)check Parent Child Relation, cascade deletion.

    2)build an index with unique key using Person1ID and Person2ID(FK2).

    Thanks in advance.. Luis

  • October 22, 2009 at 4:10 am

    #1069292

    If I understand your requirements correctly, then you have your constraints the wrong way round. You say

    How can I implement both relations, if SQL Server don´t allow me to do 2 relations from the same field (PersonID).

    Indicating that you think the constraint should be from TPerson to TPersonRelation.

    I assume you want to make sure that Person1Id and Person2ID in the TPersonRelation table both exists in the TPerson table, so the constraint is the other way round, and you actually want something like this.

    ALTER TABLE TPersonRelation ADD CONSTRAINT FK_Person1ID FOREIGN KEY (Person1ID) REFERENCES TPerson(PersonID)

    ALTER TABLE TPersonRelation ADD CONSTRAINT FK_Person2ID FOREIGN KEY (Person2ID) REFERENCES TPerson(PersonID)

  • October 22, 2009 at 3:53 pm

    #1069688

    Thanks for your reply.

    All your assumptions are correctly.

    Your suggest was my first approach to solve it, but ...

    SQL Server Throw an Error:

    Msg 547, Level 16, State 0, Line 2

    The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Person2ID". The conflict occurred in database "MYDATABASE", table "dbo.TPersons", column 'PersonID'.

  • October 22, 2009 at 4:06 pm

    #1069691

    Hi,

    This error message indicates that you have values in tPersonRelation in Person2ID column that do not exist in tPerson. Here's an example:

    create table tperson(personid int primary key, personname sysname)

    go

    create table tpersonrelation(person1 int, person2 int)

    go

    insert tperson (personid, personname) values(1, 'aaa')

    go

    --2 doesn't exist in tperson, but we can add this because there is no FK constraint yet

    insert tpersonrelation(person1, person2) values(1, 2)

    go

    alter table tpersonrelation add constraint fk_person1 foreign key(person1) references tperson(personid)

    go

    --this statement fails because of the insert above

    alter table tpersonrelation add constraint fk_person2 foreign key(person2) references tperson(personid)

    go

    drop table tpersonrelation

    drop table tperson

    Regards

    Piotr

    ...and your only reply is slàinte mhath

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply