Database Constraints -

[theexcession]

ALTER TABLE chicken ADD CONSTRAINT chickenREFegg FOREIGN KEY (eID) REFERENCES egg(eID) INITIALLY DEFERRED DEFERRABLE;

ALTER TABLE egg ADD CONSTRAINT eggREFchicken FOREIGN KEY (cID) REFERENCES chicken(cID) INITIALLY DEFERRED DEFERRABLE;

does this work?
;)

Ex

[Plotloss]

I was going to, then I wasnt, then I was, then I wasnt

But I think I am.

You sad tt.

Oh and surely egg is involuted...

[theexcession]

Excellent, now I have you attention, I do actually have a real question about contraints...

Imagine two tables, IP_Pools holds a list of IP Address ranges and User_Profiles has a bunch of User information so that when someone connects we know which IP_Pool to get them an Address from.

Now the choice of which IP Pool to go to also dpeends on which Access Point Name (APN) that they connect through - we only get this information at call setup time.

So in my IP_Pools I have columns

Pool_ID
APN
othe info

In the User Profile there is a Pool_ID column too - but there is no APN column - (we don't know this till they connect)

My IP_Pools table might look like this

Pool_ID APN
1 APN1
1 APN2
1 APN3
2 APN1
2 APN2
2 APN3

Since Pool ID is not unique I can't create a primary key on the table using this column.

However when I create a User_Profile record I wanted a FK Contraint to ensure that the record is created with a valid Pool_ID.

Any ideas how to go about this, I'm reading up on Composite Keys but not really getting anywhere at the moment.

cheers
Ex

ETA:
I guess my question is can I create a composite Primary Key on IP_Pools using Pool_ID and APN, and then create a FK Contraint on User_Profiles that references only the Pool_ID column of IP_Pools?







>> Edited by theexcession on Thursday 8th September 10:09

[Plotloss]

Hmmmnn...







Hmmmmnn...





Hang on, let me finish with Jobserve.

[pdV6]

Suspect the database is not suffiently normalised...

Perhaps you need a Pools table that has just unique Pool_IDs plus any other info relating to the Pool, an APN table and then the IP_Pools table is just as a link table between the 2 to define valid combinations.

Then, your contraint would be between UserProfile and Pools.

[Plotloss]

He's on the money.

As its stands its a many to many, there needs to be an Xref entity to resolve both side of the relationship.

Mind you, I havent done any relational work for 3 months so it could just be mush in there now...

[theexcession]

Agreed,

I'm no DB expert, but have done a few in the past.

I guess I'm torn between handling the 'constraint' in SQL at record creation time or trying to get the DB to do it internally.

I suppose this 'requirement' isn't really a propper constraint and likely my terminology is a bit up the spout.

I'll get right on it with thinking....

cheers
Ex

P.S. Plotty, did you post that disk?