Confused with some constraints

[Nessa]

Hello all,

I'm new to database programming, and I'm trying to set up my first database. I'm having a bit of trouble implementing a couple of constraints, my mind seems to be stuck even though it must be simple :confused:

Firstly, I have a table where ColumnA (primary key) is referenced by ColumnB. What I want is basicly two things:

a) I want ColumnA and ColumnB to not be equal. This one was easy to implement, no trouble here.

b) If there is an entry where ColumnA equals, say, 2 and ColumnB equals, say, 5 then there should be no entry where ColumnA=5 and ColumnB=2. This is where I need your help. :(

Secondly, I have another table, let's call it TableA, where one column (Reference) references TableB's primary key (PK). I want to avoid entries in TableA where TableA's column "ID" would equal TableB's column "ID" of the corresponding row (that is, when I join them on "Reference"="PK"). I don't know how to do that, either.

Any help would be appreciated.

I'm using PostgreSQL.

Thanks in advance. :)

[ryanbutler]

My only response to that entire post is "huh?" If table A's primary key is being referenced by table B, then they would have to match, otherwise, a primary key to foreign key INNER JOIN would never work and defeats the purpose of relational data in a relational database.

Can you clarify a bit more about what you're doing?

[Nessa]

If table A's primary key is being referenced by table B, then they would have to match, otherwise, a primary key to foreign key INNER JOIN would never work and defeats the purpose of relational data in a relational database.

Yes, I know that. These two columns do match, of course. It's the two other columns that aren't supposed to match.

[Nessa]

I think I should give an example here.

If TableB is

PK ID
1...5
2...4
3...8

Then TableA may be

PK..Reference..ID
1......3...........5
2......2...........8
3......1...........23

But TableA may NOT be

PK..Reference..ID
1......3..........8
2......2..........4
3......1..........5

In the second case, all three rows are unacceptable.

[chazzy]

hmm i think it makes sense to say that columna and columnb are essentially a separate entity from the rest of the table. in this case, the values for a and b are therefore interchangeable; and as a result i would think that you should setup a 2nd table to house just a and b, make a always the lower value, b always the higher value, and make that tuple itself a primary key.

[Nessa]

Thanks for your answer :)

I assume you mean something like this:

LowerValue........HigherValue........LowerValueIsColumnA(boolean)

I guess that would work. A separate table would complicate things, though, since I would need to perform complicated joins all the time.