| Author |
Topic |
|
mono
Starting Member
36 Posts |
 Posted - 2002-01-15 : 11:38:47
|
| How do I define such a constraint (no triggers please) ? |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2002-01-15 : 12:06:51
|
| Essentially, you want a one to many relationship. You define this type of relationship by placing the primary key of the ONE table as a foreign key in the MANY table.For Example, a teacher may teach many classes, but a class can only have one teacher.The TeacherID would be a primary key in the TEACHER table, and a foreign key in the CLASS table.HTH-Chad |
 |
|
|
mono
Starting Member
36 Posts |
Posted - 2002-01-15 : 12:12:15
|
| that's just a one-to-many which allows for zero on the many side. I specified one-to-ONE-OR-MORE. Not essentially or otherwise the same thing ;-) |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2002-01-15 : 12:20:02
|
| Don't allow for nulls in the Foreign key. This requires at least 1.-Chad |
|
|
|
mono
Starting Member
36 Posts |
Posted - 2002-01-15 : 12:24:13
|
Chad,quote:
Don't allow for nulls in the Foreign key. This requires at least 1.
That only prevents orphans. I want to prevent childless parents too. |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2002-01-15 : 12:33:02
|
Gotcha, by fault I was thinking about it in reverse  I don't think you could maitain a relationship like that without utilizing triggers. I could be wrong, but I can't think of a way.-Chad |
|
|
|
Teroman
Posting Yak Master
115 Posts |
Posted - 2002-01-15 : 12:33:34
|
| if you cant have any orphans or childless parents how do you hope to populate your tables, youll need to drop constraints to get the data in which kind of makes it a bit strange, to my mind at least.anyway, to do what you want dont you just have two foreign keys as described above, one going each way between the two tables?col |
|
|
|
mono
Starting Member
36 Posts |
Posted - 2002-01-15 : 12:39:15
|
Teroman,quote:
how do you hope to populate your tables
Of course to add a preat I have to add a child at the same time. Transactions allow me to do this (without any drop constraints). That's what htey are for : they turn a number of actions into a single Atomic (see ACID) operation.quote:
dont you just have two foreign keys as described above, one going each way between the two tables?
No. Think about it. Or try it. |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2002-01-15 : 12:50:52
|
| How about this?Create a third table as you would for a many to many relationship, but add a unique constraint to the 'one' foreign key in this table.Then add triggers to insert into this table when records are inserted into the other table(s).That is the simplest solution I can think of, but it uses triggers.-Chad |
|
|
|
mono
Starting Member
36 Posts |
Posted - 2002-01-15 : 13:35:38
|
| Chad,People pulling triggers makes me nervous... My therapist said to avoid violence.The intermediate table idea is at least an idea, but if it requires triggers, we could drop the table and do it all with triggers.I'm pathologically lazy and the prospect of writing all these 6 or so (2 tables times 3 operations) makes me ill. I think I'll have a little lie down now...There must be a better way. |
|
|
|
Lavos
Posting Yak Master
200 Posts |
Posted - 2002-01-17 : 02:17:35
|
| Mono, transactions do make things atomic, to the outside world, but IIRC each individual statement would still have to follow any constraints on a table.This means you'd have to disable the constraint and renable it after the insert, so in other words it'd probably be worthless.You're going to have to use triggers, but not as many as 6.Setup a NOT NULL FOREIGN KEY ON DELETE CASCADE ON CHANGE CASCADE constraint on your child table. That takes care of the child insertion, parent deletion/update to the primary key.For the child deletion and update, you are just going to have to use triggers. Sorry, they are the easiest way. You can probably use one after trigger for both actions if you accept that an update to a child table can delete a row in the parent table due to it losing it's last child. Just run through the deleted table, and for each ParentID, check if it exists in the child table. If not, then delete the row from the parent table. I'd try the correlated subquery approach:DELETE FROM ParentTableWHERE ParentID IN (SELECT ParentID FROM Deleted d1 WHERE NOT EXISTS (SELECT ParentID FROM ChildTable c1 WHERE c1.ParentID = d1.ParentID))For the last operation (insert on parent), I'd suggest removing insert permissions from everyone, and require a stored procedure to do the insert. It's the only way to make sure there is a child to insert at the same time.That's one constraint, 1 trigger, and 1 stored proc, covering insert/delete/update on both tables. That's probably about as good as you're going to get. A more unified approach is to wrap everything in stored procedures, but if this arangement is documented, I don't see any real problem.(I'd Love to see someone do better though, It's the only way I'm going to learn.)----------------------"O Theos mou! Echo ten labrida en te mou kephale!"Edited by - Lavos on 01/17/2002 02:19:44 |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-01-17 : 02:44:10
|
quote:
People pulling triggers makes me nervous... My therapist said to avoid violence.
I would suggest you to change your Therapist . Another Therapist might suggest you not to use constraints too so better put your head high dont lie down and learn Triggers Mono, Triggers Are no that complex as you are made to think.Read these Articles by Grath and you will give a kick to all your Therapists  http://www.sqlteam.com/item.asp?ItemID=3850http://www.sqlteam.com/item.asp?ItemID=6494----------------------------------"True love stories don't have endings." |
|
|
|
|
one to one or more