Adding Unique Contstraint

[nlaliberte]

Good Afternoon,

I have a simple relationship table that I need to add a contrainst to. The table is supposed to be a parent/child relationship, as well as the date the relationship is effective. See Below:

CREATE TABLE relation
(
child_id INT
,parent_id INT
,acq_date DATETIME
)


We have found in the table the following two records;

Child_id Parent_id Acq_date
12 6 10/31/2008
6 12 10/31/2008


Obviously the son of a father can't be the father's father. (I know it sounds confusing but I'm sure you all understand).

So I need to put a contraint on that does not allow a record to be entered if there is already a relation where the child is the father.



If it helps, here is how I would apply the constraint to an insert statement, maybe this will clarify the logic I'm looking for.

INSERT INTO relation
(
child_id
,parent_id
,acq_date
)
SELECT
i.child_id
,i.parent_id
,i.acq_date
FROM input i
WHERE NOT EXISTS (
SELECT r.*
FROM relation r
WHERE r.child_id = i.parent_id
AND r.parent_id = i.child_id
)


Can anyone help me with this piece of code? It would be greatly appreciated.