| Author |
Topic |
|
rico1931
Starting Member
37 Posts |
 Posted - 2009-02-19 : 11:22:47
|
| Hello Everyone,I am trying to use the IF EXISTS statement properly and running into problems. I am trying to make it so a user can't delete a parent if children are present. So here is an exampleTbl A is the Parent Table. This tbl contains a KEYKey:1 = Animal2 = PlantTbl B is the child table. This tbl contains PARENT_KEY1 = Lion2 = Daisy1 = Cat2 = RoseI want to make a record set to not allow a user delete a Key in tbl A e.g they can't delete "Animal" If in tbl B Lion and Cat are still present. Can someone help me write the if statement to check the table to see if one key matches the other. Thanks in advance |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-19 : 11:26:58
|
| you dont have write any query for this. just create a foreign key constraint in tblB PARENT_KEY column to refer to KEY field of tblA.once fk is present, it wont allow deletion of values from table unless it has no references. |
 |
|
|
rico1931
Starting Member
37 Posts |
Posted - 2009-02-19 : 11:44:51
|
| ok so I guess this is where i'm new to SQL..i'm using SQL 2008 and in the past I tried doing this relationship in the diagram and the boxes that were popping up were confusing me. Any way you can explain how to make this relationship. I understand the concept I just need help on how to make it in SQL 2008 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-19 : 12:01:00
|
just use ALTER TABLE [Tbl B] ADD CONSTRAINT FK_TblA_TblB PARENT_KEY REFERENCES [Tbl A] (KEY) |
|
|
|
rico1931
Starting Member
37 Posts |
Posted - 2009-02-19 : 13:14:27
|
| its telling me that I have incorrect syntax near PARENT_KEY. Expecting UNIQUE, PRIMARY, FOREIGN, DEFAULT, REFERENCES, or CHECK. But everything else is fine. What am I doing wrong? |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-19 : 14:06:11
|
quote:
Originally posted by visakh16
just use ALTER TABLE [Tbl B] ADD CONSTRAINT FK_TblA_TblB FOREIGN KEY (PARENT_KEY) REFERENCES [Tbl A] (KEY)
|
|
|
|
rico1931
Starting Member
37 Posts |
Posted - 2009-02-24 : 10:00:50
|
| Thanks for the help guys I really appreciate it |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-24 : 10:05:12
|
Good  |
|
|
|
rico1931
Starting Member
37 Posts |
Posted - 2009-02-24 : 11:09:50
|
| sodeep I have another question for you. If you wanted to keep this same principal but use the UPDATE function so if say Tbl A primary key '2' is changed to '4' how can i automatically detect that in Tbl A to UPDATE tbl B = '2' to UPDATE to '4'....hmm i might of just confused my self there.. let me know if this makes sense. Thank you |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-24 : 13:13:42
|
quote:
Originally posted by rico1931
sodeep I have another question for you. If you wanted to keep this same principal but use the UPDATE function so if say Tbl A primary key '2' is changed to '4' how can i automatically detect that in Tbl A to UPDATE tbl B = '2' to UPDATE to '4'....hmm i might of just confused my self there.. let me know if this makes sense. Thank you
for that you need to specify ON UPDATE CASCADE clause along with FK definition.Once this is done, any update to key value gets automatically reflected in child tables. |
|
|
|
|
IF EXISTS