Relationship Problem

[supershroom]

Hello all,

OK - my problem is that my relationship(s) just doesn't seem to work.
I am currently using Microsoft SQL Server 2005, and working through the management studio. For my site coding, I'm using Coldfusion.
So you can expect that I don't know an awful lot of actual SQL code, and instead am relying on the interface.

I have already achieved making a fully functional registeration and login page for my site, so there's no hiccups there :)
But when I insert a new user to my "users" table, the userid doesn't seem to appear my other table; "characters". I should probably note that by characters, I mean personalities/people - not actual text characters or whatnot.

Here's the low-down on my current DB layout:
My first table is "users" and has the columns "userid", "username", "password", and "emailaddress". The userid column is a PK with identity specification. It is also an int datatype, while the other columns are varchar(20)s. Also, the userid doesn't allow nulls, obviously.

My second table is "characters" and has several columns, mostly of the same datatypes as the "users" table - so it's not relevant. The two important columns are "characterid" and "userid" - both are int datatypes and do not allow nulls. The characterid column serves as the PK and has identity specification.


Now comes the problem...; using a database diagram, I have created a relationship between "userid" from "users", and "userid" from "characters". The "userid" in "characters, in theory, is the FK.
Right. So I register myself on the site using a simple insert sql query (actually, I use <cfinsert>) - the insert goes directly into "users" with a username, password, and email address. A unique number is created in userid, as you'd expect... but nothing happens in "characters" at all!
How do I get the userid to pass on from "users" into "characters"?

Well crap, this post is extremely long now. I only hope it's understandable, and that someone can help me :(

Thanks a lot in advance!
Aiden

[iburyak]

FK is a logical object and not physical that can insert record for you.
You have to create a second insert statement into a ‘characters’ table but getting newly created userid first.


Good Luck.

[supershroom]

Hmm, thanks for the reply..

I think I tried doing that already.
I first used the insert sql statement and inserted a username, password, and email address into the "users" table - I can confirm that this was successful. That then created the userid automatically.
Then, another insert sql statement was ran straight after, which also inserted information into fullname, gender, and class in the "characters" table - this also was successful. The characterid was successfully added automatically, but nothing happened in the userid column (in "characters").

Am I going about this right?

More on the subject... I was under the impression that relationships were used to automatically make specific entries from a column in one table, copy over to a similar column in another table - therefore making queries from different tables always follow the same userid (for example). This would then make sure the site user always views only his or her row information, no matter the table queried.

[iburyak]

You have a wrong impression.
In second insert statement instead of userid use following statement
Insert into characters( "userid", …… ) values( (select userid from users where username = "username" and password = "password" and emailaddress = "emailaddress"), other columns here)
So my point is you can use select statement instead of userid.
FK works like this:
If you would try entering some userid into characters table that does not exist in users table it will give you an error.
I would recommend creating unique index on users table over (username, password, emailaddress) columns so you wouldn’t have duplicates which might cause problem at insert time. If you want to have multiple records for the same user then you can change your insert statement to this:
Insert into characters( "userid", …… ) values( (select max(userid) from users where username = "username" and password = "password" and emailaddress = "emailaddress"), other columns here)

Good Luck.

[supershroom]

I understand now :) I guess I was expecting something more automated with the use of relationships.

But I still don't entirely understand the point in having a relationship.
Even without the relationship, I can still add the userid from "users" into "characters" in the way that you described. So long as I have an identity specification on my userid in "users", there will never be an error related to a duplicate userid.
EDIT: I could also use code to check that the userid is currently available and exists from "users", before trying to create it within "characters", thus eliminating the need for a relationship? I think?...

That aside, your solution has been very helpful to me :) I can finally continue with the rest of my site!
Thanks
Aiden

[iburyak]

select userid from users where username = "username" and password = "password" and emailaddress = "emailaddress"

Statement above can return 2 different userids it will be generated for you but it will be escentially the same user. If you have identity column you can use @@IDENTITY global variable in the next statement instead of select statement above.

Foreign key just to make sure primary key exists in a parent table.

Godd Luck.