Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Foreign Keys? Cascades?

Author  Topic 

barrabutus
Starting Member

6 Posts
Posted - 2011-02-01 : 21:37:02
Hi All

After 3 hours of trying to get this to work, I gave up and came here lol

I understand that Foreign Keys are the "links" between tables.

As im new to SQL relations, I thought i would try it out

I created 2 tables (Users,Passwords)

Users Table - 3 Fields (user_id,username,password_id)

Now trying to use password_id (Auto_increment field as the identifier from Passwords table)
Passwords Table - 2 Fields(password_id,password)
Tables Image

The below code does exactly what i want when i run it once.
but i run it again and the table returns with 4 rows :S
It shows me i have 4 rows 2 with identical user_id,s even when that row is set to auto increment.
I have no idea why this is?



--Insert a new password
INSERT INTO passwords(password ) VALUES ('TestPass')
--Insert a new user and get the last insert (Insert above to retrieve the password ID)
INSERT INTO users(username,password_id) VALUES ('NewUser',@@IDENTITY )
--Display the comined tables.
SELECT * FROM users,passwords

Image Below




DELETE FROM users WHERE username = 'NewUser'

It just deletes the users name but the linked FK of password_id still remains.
The password of the deleted user still remains in the password table (Just to clarify)

If anyone can point me to a decent tutorial which uses non-technical terms of how i can use FK,s and perform cascading deletions of data, It would be muchly appreciated.

Alternatively if anyone has any solutions to how i can perform these tasks.

Many Thanks

Sorry for the long post Barrab :)

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts
Posted - 2011-02-02 : 00:05:01
1) Well through out your post i don't see any sort of Foreign Key's definition (Constraint) over the pwdID field of Users table :).

So if not created, create a foreign key constraint.

2) but i run it again and the table returns with 4 rows :S

There are two records in each of the table so as per your select query it has (and should have) returned 4 rows ... :D


Cheers!
MIK
Go to Top of Page

barrabutus
Starting Member

6 Posts
Posted - 2011-02-02 : 05:35:02
Thanks for your reply ;)

I will post an image with the relationship



As you can see i have Upon Delete Cascade set, So thinking that if i delete a user then the password associated to the user should be deleted as well from the passwords table.

Q: When creating a relationship within MSSQL is the left table the parent table and the right table the child ? So take from the child and put the value into the parent ?

I am asking as each tutorial/video i found which showed how to do this did not clarify in basic english which each side of the relationship was what.

Or would this only be relevant IF i delete from a foreign key like so 


DELETE FROM users WHERE password_id = TheUserPasswordID


Is this correct?

Many Thanks Barrab
Go to Top of Page

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts
Posted - 2011-02-02 : 05:56:57
Can you script the definition of the foreign key's table to Create To and post the code here ? It should work if the cascade delete is turned on!


Cheers!
MIK
Go to Top of Page

barrabutus
Starting Member

6 Posts
Posted - 2011-02-02 : 07:50:04
Sorry for the excessively long reply but im trying to give as much information as i possibly can to make it easier for you to understand what im trying to achieve.

The scripted FK 

USE [testDB]
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_users_passwords]') AND parent_object_id = OBJECT_ID(N'[dbo].[users]'))
ALTER TABLE [dbo].[users] DROP CONSTRAINT [FK_users_passwords]
GO

Which looks like


I tried this code and have written each step to try and solve this :), I tried commenting out each DELETE command to try each one but both resulted in the same problem.

Maybe this will help


--Clear the tables 
DELETE FROM users;DELETE FROM passwords
--Insert new Password 
INSERT INTO passwords(password) VALUES ('AlexPass')
--Insert New User
INSERT INTO users(username,password_id) VALUES ('Alex',@@IDENTITY )

--Delete Alex from the users table. (This should remove AlexPass from password also)
DELETE FROM users WHERE username = 'Alex'

-- Try to delete by the linked password_id
DELETE FROM users WHERE password_id = ThePasswordID
--Result : Still the same Password still remains in password table

-- After deletion Alex (Users Table) and AlexPass (Passwords Table) both should not exist.

-- Check the tables ( Should only return 2 empty tables)
SELECT * FROM users
SELECT * FROM passwords

--FAILS AlexPass still remains in password table..But Alex has been deleted from Users.
--RESULT : We have a password which has no user..


Many thanks for your replys and help and taking the time to read my seriously long posts.

Barrab :)
Go to Top of Page

barrabutus
Starting Member

6 Posts
Posted - 2011-02-02 : 08:01:44
UPDATE :

If i use 
DELETE FROM passwords WHERE password_id = thePasswordId


This actually works and drops the user Alex from Users table and removes AlexPass from the password table.

Am beginning to think that i have my relationship the wrong way around ... lol

So i have to delete the users password by its ID for it to remove the user and password together ? (Surely a password cannot exist without a user??)

I thought it would make sense to store any "links" between users and passwords into the users table but it doesnt seem to be working for me that way.

Again Thanks :)

Barrab
Go to Top of Page

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts
Posted - 2011-02-03 : 00:23:30
dear sorry if i was not clear but i asked for the "create table" syntax of the table having foreign key. Any how go through the below example hope you may point out the problem of your design your selves!!!



Create table tab1(ID1 int primary key,Col1 varchaR(10))
Create table tab2(ID2 int ,Col2 varchaR(10),ID1 int)

ALTER TABLE tab2
WITH CHECK
ADD CONSTRAINT FK_Table1_ID FOREIGN KEY (id1)
REFERENCES tab1 (ID1)
ON Delete cascade
On update cascade;


Insert into tab1 values(1,'aaa'),(2,'aaa')
Insert into tab2 values(1,'ssfr',1),(2,'dewggg',2)

Select * from tab1
Select * from tab2

-- AS you see that both primary keys values are inserted into the tab2. Now try to delete one of both of them

Delete from tab1 where id1=2
-- And Check now

Select * from tab1
Select * from tab2


Cheers!
MIK
Go to Top of Page

blindman
Master Smack Fu Yak Hacker

2365 Posts
Posted - 2011-02-03 : 10:32:58
quote:
Originally posted by barrabutus


Users Table - 3 Fields (user_id,username,password_id)
Passwords Table - 2 Fields(password_id,password)

This implies that a user may have only one password, but that a password may belong to more than one user.
Are you sure that is the scenario you are trying to model?
It would make more sense to set your tables up like this:
Users Table - 2 Fields (user_id, username)
Passwords Table - 3 Fields(password_id, user_id, password)


________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2011-02-03 : 12:41:53
quote:
Originally posted by blindman

quote:
Originally posted by barrabutus


Users Table - 3 Fields (user_id,username,password_id)
Passwords Table - 2 Fields(password_id,password)

This implies that a user may have only one password, but that a password may belong to more than one user.
Are you sure that is the scenario you are trying to model?

I completely agree. My assumption was that this is just an investigatory exercise.
quote:

It would make more sense to set your tables up like this:
Users Table - 2 Fields (user_id, username)
Passwords Table - 3 Fields(password_id, user_id, password)
Although that prevents mutiple users from using the same password, it's not really a good solution. Why surrogate? Why put an atrribute of an entitity in another table that isn't even an entity?
Go to Top of Page

blindman
Master Smack Fu Yak Hacker

2365 Posts
Posted - 2011-02-03 : 13:08:13
quote:
Originally posted by Lamprey

Although that prevents mutiple users from using the same password, it's not really a good solution. Why surrogate?

This is probably not a good thread to resurrect the entire "surrogate vs natural key" debate.
The important issue here is modeling the user/password relationship, not the implementation.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2011-02-03 : 14:16:08
I don’t think my question about the surrogate is a revival of that debate because UserID is not a natural key. And I agree with you that the model is very important. That is why I was asking question to see if you had some reason for modeling it that way. As I mentioned it seems like a really bad way to model a User/Password. However, I don’t know what your thought process is. Perhaps you were just showing one way to prevent the same password from being used by multiple users and that’s fair. But, I’d hate for a beginner to think that modeling it that way would be a good idea unless there is a very compelling reason to put the password in its own table.
Go to Top of Page

blindman
Master Smack Fu Yak Hacker

2365 Posts
Posted - 2011-02-03 : 14:54:24
<Sigh> If you insist.
quote:
Originally posted by Lamprey

As I mentioned it seems like a really bad way to model a User/Password.

No. You are absolutely 100% incorrect. It is an F'ing fantastic way to model users and passwords, and the only method that would be deemed acceptable by any dba with even a modicum of experience and sense.
It allows a user to have multiple passwords. You got a problem with that? For multiple systems, or for maintaining password histories, or whatever.
You got a problem with this? Let them flame-games begin.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2011-02-03 : 15:22:28
You obviously made a bunch of assumptions about how a password is/might be used that you didn't articulate. That's why I was asking questions. Thanks for being comletely rude about it though.
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts
Posted - 2011-02-03 : 15:35:41
I don't allow cascading foreign keys ever.

They introduce difficult to track down bugs. They cause data to be deleted/modified unintentionally. They change history -- a huge no no. They are the lazy programmers way out of actually understanding what they are doing.
Go to Top of Page

blindman
Master Smack Fu Yak Hacker

2365 Posts
Posted - 2011-02-03 : 16:28:34
quote:
Originally posted by Lamprey

You obviously made a bunch of assumptions about how a password is/might be used that you didn't articulate.

As did you. Of course, I did not raise these concerns at the time, because they were completely irrelevant to the subject of the post.

quote:
Originally posted by Lamprey

Thanks for being comletely rude about it though.

Thanks. To be fair though, I wasn't the first one to break the rudeness barrier.
quote:
Originally posted by Lamprey

Although that prevents mutiple users from using the same password, it's not really a good solution.


quote:
Originally posted by Lamprey

As I mentioned it seems like a really bad way to model a User/Password.

I'm just more open and honest and blatant about it.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________
Go to Top of Page

blindman
Master Smack Fu Yak Hacker

2365 Posts
Posted - 2011-02-03 : 16:30:22
quote:
Originally posted by russell

I don't allow cascading foreign keys ever.

They introduce difficult to track down bugs. They cause data to be deleted/modified unintentionally. They change history -- a huge no no. They are the lazy programmers way out of actually understanding what they are doing.

I used to implement cascading deletes, but now I agree with you that the developers should really be aware of and handle such things in their code. I still use cascading updates on occasion, though.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts
Posted - 2011-02-04 : 03:51:53
Barrab: someone obviously had a bad day when responding to your questions...I'll try to address a few of them. First of all, cascading updates/deletes are regarded as "bad" because it makes stuff happen automatically in your database without the use of code and as your system grows this will become less and less obvious what's going on behind the scenes. I try to avoid triggers as well for that very same reason...I think it reduces control over the stuff that's going on. You will be a lot better off by staying away from cascading updates/delets altogether.

Now about this select you had that returned 4 rows; this is because you have no join condition. This will give you the correct result: SELECT * FROM users a inner join passwords b on a.password_id = b.password_id. What you did will create what is called a cartesian product which is basically all combinations of all rows -> http://en.wikipedia.org/wiki/Cartesian_product



- Lumbago
My blog-> www.thefirstsql.com
Go to Top of Page

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts
Posted - 2011-02-04 : 04:48:53
Hi folks, it was the following part onto which i had this discussion with OP: "If anyone can point me to a decent tutorial which uses non-technical terms of how i can use FK,s and perform cascading deletions of data, It would be muchly appreciated."

Yes the (cascade delete/update) approach is Bad to solve such problems. However i just replied in light of the above question.

Cheers!
MIK
Go to Top of Page

barrabutus
Starting Member

6 Posts
Posted - 2011-02-04 : 15:25:53
Many Thanks for all your replys

Sorry for the delayed reply,

Ok just to clarify my thought process was that if i had a user and then any tables "linking" information to a user could be pulled from calling the users table. But as it looks like Cascading and Auto-Deletion is not really a good idea i think i will probably leave that alone and use manual queries like i used to in PHP, I just thought that with a relational system + cascading it would save me deleting from 1 table and then searching for an ID in 1+ more tables and then deleting any rows linked to the initial table.

The main idea was a test program for me to learn to use tables in a relational way , So this was NOT a serious program just for self-educational purposes.

Thank you for the SQL explaining why i received 4 rows from the SQL query i ran, That was very helpful :)


Just to make the table thought process completely clear , So you can tell me if this is wrong (Bear in mind this is not a real table system ), I just need to know if im thinking of relationships the wrong way.

3 Tables
Users (users_id,username,password_id,details_id)
Details (details_id,Fname,Lname)
Password (password_id,password)

So we have a user named Joe Bloggs with a Password of Bloggy123 and he uses the username Joey to login.

I am thinking that 1 user must have a First Name + Last Name and a password so this would be a 1-1-1-1 relationship throughout the tables because a Fname,Lname,Password will have no Rows without a user.I am thinking its more like a "Segregated" users table instead of having

user_id,password,fname,lname all contained in 1 table each table has a responsibility to hold a "part" of the users information.

We end up with 1 row in Users like so -- > (1,Joey,1,1)

And DELETE from users WHERE user_id = 1 ( should truncate through the details and password tables and delete all rows with an id of 1)

Im guessing this is the wrong way to perform this, I wish i understood relational tables a lot better (All the tutorials/videos i see show different things), So thats why im trying the practical approach.

Thanks again for all your replys and taking the time to read my post

Barrab.
Go to Top of Page

barrabutus
Starting Member

6 Posts
Posted - 2011-02-04 : 15:30:19
quote:
Originally posted by Lamprey

Perhaps you were just showing one way to prevent the same password from being used by multiple users and that’s fair. But, I’d hate for a beginner to think that modeling it that way would be a good idea unless there is a very compelling reason to put the password in its own table.


This is just an exercise to try and understand and learn how to use a relational table setup (Because my prior db designs have always been manually cascaded by hand) with no relations setup at all only when i have written in PHP and told it to do all the followup deletions so sending a lot of querys.
Go to Top of Page

4dbobort
Starting Member

1 Post
Posted - 2011-04-29 : 15:07:44
I think the problem might be the way you are selecting the data.
You ran this query:
SELECT * FROM users, passwords


I think what you intended to do was this:
SELECT * FROM users INNER JOIN passwords ON users.password_id = passwords.password_id
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site