relationships, primary key sql2000 question.

Hello,

I have 2 tables with a field called userid which is uniqueidentifier and they are both primary keys.

When I add a record to table1 and the userid field is filled, what is the best way to update table 2 with the same record.

Is there an sql function that will update automatically or do I have to write code in vb.net to select the record form table1 and insert into table2.

Thanks

Peter

If you wanted to do it relationship, one of the keys would have to be a foreign key and the other the primary key, probably using a 1 to 1 relationship.

If you want to use two primary keys, you would either need to do it by T-SQL or on the .NET Server end, ex. with VB.NET.

|||

What you are looking for is called a DRI(declarative referential integrity) constraint. You create it with the enable relationship dialog box at the top of Management Studio and look for option Cascade on Update. The two links below one explains DRI and the second is a walkthrough to enable it. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms177288.aspx

http://msdn2.microsoft.com/en-us/library/ms186973.aspx

|||

Thanks guys, working through links now.

Peter

sql

Relationship to UserId

Hi there everyone, this is my first post so go easy on me :)

Basically I am trying to get my database to copy the value in the UserId (unique identifier field) from the aspnet_Users table to a foreign key UserId in a table called userclassset. I have made this field the same datatype and created a relationship between the two. Unfortunately, when I add a user using the ASP.Net configuration tool it does not automatically copy this value into my own custom table. I have noticed it is however automatically copied into the aspnet_Membership table. Any pointers on how to solve this would be great!

Thanks :)

ascension:

?I have noticed it is however automatically copied into the aspnet_Membership table. Any pointers on how to solve this would be great!

This is because when you?use?.NET2.0?built-in?function?to create user,?the?aspnet_Users_CreateUser?stored?procedure?is?called.?This?stored?procedure?will?not?only?insert?a?row?in?aspnet_users?table,?but?also?do?some?operations?on?aspnet_Memebership?table,?you?can?use?sp_helptext?to?view?the?definition:

sp_helptext aspnet_Users_CreateUser

So if you want to insert some row into your own custom table, you can either: alter the aspnet_Users_CreateUser to add your own statements (preferred); orcreate a?insert trigger?on?the?aspnet_users?table|||Brilliant, worked perfectly. Thanks :)

Relationship problem

Hi All... Two of my tables are:

Users - primary key is UserId, an int with identity turned on.

Messages - has a column named UserId that references the same in Users.

I'm using Visual Studio 2005 against a SQL 2005 database.

Using both the diagram tool and table data, I'm trying to set up the relationship implied above and am getting the following error:

Users table saved successfully.

Messages table

- unable to create relationship 'FK_Messages_UserId'.

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint 'FK_Messages_UserId'. The conflict occurred in database 'XXXX', table 'dbo.Users', column 'UserId'.

I've done several other similar relationships without incident. But this one (and one or two others) refuse to work. I'm a bit of newbie with these rascals, so that doesnt help much... Any ideas what this things trying to tell me? Thanks! -- Curt

You might already have entries in your child table which have no parent entry in the parent table.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Hi Jens... Thanks for the reply. You threw me a little at first on your use of "parent" and "child", but yeah you nailed it. To try and tie those terms to the tables in my original post, I had some records in Messages (child) that referenced a primary key that did not exist in Users (parent). Geez, these FKs really help us keep a clean house, dont they... Thanks again!! Curt

Relational databases

I have a membership database and a profile database with a userid column in both. The foreign key is in the profile database where i want a persons profile details to go like location,occupation and what not. How do I make it so when a new user registers a userId is created in the profile database that matched the userid in the membership database so i can query out profile details based on a registered members userid?

There's a couple of ways -

- Add a separate column for the membership id to the profile.

- Make the UserID in the Profile non-auto generated and set it when you create the record as under

INSERT INTO MembershipTable .....DECLARE @.UserID INTSET @.UserID = SCOPE_IDENTITY() -- gets the identity of the lastnew record-- create the profile recordINSERT INTO ProfileTable (UserId, .... VALUES(@.UserID, ....

registry access

Dear Professional
Through Sql Server Stored procedure can I fetch the userid and password
which saved in Registry.
Thanks
NOOR
hi Noor,
"Noor" <noor@.ngsol.com> ha scritto nel messaggio
news:%23SKzZyyuEHA.1264@.TK2MSFTNGP12.phx.gbl
> Dear Professional
> Through Sql Server Stored procedure can I fetch the userid and
> password which saved in Registry.
> Thanks
> NOOR
you can have a look at xp_regread undocumented strored procedure...
http://www.mssqlcity.com/FAQ/Devel/xp_regread.htm
please note that undocumented features can be modified/dropped at any time,
without notification... and there's no support for them
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply