If you prefer writing SQL for relations then you should read T-SQL documentation. Check out chapters for CREATE TABLE and ALTER TABLE statements.|||Yes. You can create realationships as mentioned. Is there specific information you would like to know.|||Thanks Guys.
Helps to know that I wasn't going to crazy trying to figure it out.
I have made a new table called 'customer' which i wish to tie into the userId of the db. I have used a db diagram to do this (there are keys on each side of the link and userId is the FK) . When i put the membership/profile view onto the form alongside the new customer table nothing displays in the customer table when i run the app., i dont even see the titles - any ideas (i'm new to this sql malarky btw - so its probably something unbelievably straightforward). Any help appreciated.
Thanks
Hi,
By default, the membership/profile view will not show your additional information.
Since you have added one table into the database, and need to show it on your view, you will need to use your own query to get the user info together with the data in customer table. This can be done either by creating a View or use JOIN statement directly.
Then, you can customize the web UI to show the additional info.
HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!
|||Hi, and thanks for replying.
I sort of rather optimistically and naively thought that because my new table was using pre-defined field variables including the key field (i.e. UserId (key), UserName etc.) that the system would know that these were what I required to be displayed (through the diagram), with no further programming, except to set to set the new field variables, that I've added, to new values within the program, and that the new fields would simply display the default value until I did something with them. - in another world that might have happened. After all, what have I created the diagram for?
What I didnt realise was that the aspnet.mdf tables are created, configured, and deleted through the Stored Procedures and that I needed to do the same for my table, even though there is a diagram telling the system that this 'linking in' of the aspnet_userId and my table is what I want to happen .
As I'm using the UserId as my key field I want the table to reflect the same membership as the aspnet.mdf membership tally - so I've sneaked in one or two statements within the createuser and deleteuser Stored Procedures to put in or remove UserId values from my table as new members are created or deleted respectively. I hope that that will solve my problem - it seems to have so far. I just cant help thinking that somewhere along the way my table is not going to tally with aspnet users for some reason, due to something that I'm not aware of (yet), caused by some insertion or deletion of a UserId without the createuser or deleteuser Stored Procedures being involved.
I'm sort of at a loss as to why I've created the diagram. I could now delete it and nothing would change.
Thanks again.
s ofI have two tables; each has multi-column primary keys. I need to create a foreign key relationship between the two tables.
Sales:
DKey, int, PK
OKey,int, PK
RKey,int, PK
...
Rep:
Bkey, int, PK, identity
Rkey, int, PK
...
When I try to like Rkey from both tables as a foreign key relationship, I get an error that the columns in one table "do not match an existing primary key or UNIQUE constraint. (I've tried it both ways and get the same error.)
How can I link these two tables?
Thanks.
Make an unique index on Rkey from Rep or Sales depends on the table you can delegate "Primary Key" (use Database Diagram for usability)
|||
Since you did not include rationale for the key selection, this is going to be just a guess.
The purpose of a Primary Key is to uniquely identify a row of data. It looks like each Rep is uniquely identified with the IDENTITY field BKey. I'm not sure why you have RKey also involved in the Primary Key for Rep.
You could recreate the Primary key for Rep using only the Bkey, and then add the Bkey field to Sales to relate Sales to Rep.
OR, if RKey is unique, use it as a Primary Key.
However, if for some silly reason you HAVE to have a combination of Bkey and Rkey as the Primary key in Rep, you will have to add both columns to Sales in order to create the relationship.
As a side note, the use of Bkey, Rkey, etc. for column names is very 'odd', and not only adds confusion, but will be difficult to maintain the pattern for all tables. A 'best practice' naming convention for an IDENTITY field (such as Bkey) is to use the table name and the suffix [ID]. So a preferred name for Bkey would be RepID. The name instantly communicates where the field comes from and what it is, expecially useful when it is added to another table.
|||I was trying to simplify the question....I probably should have included more details. I'm sorry, I never know how much detail to go into.
What I'm actually trying to do is create a many to many link between:
Sales:
DateKey, int, PK
OrganizationKey, int, PK
CustomerKey, int, PK
RepBridgeKey, int, PK
....
RepBridge:
RepBridgeKey, int, PK, identity
RepKey, int, PK
Rep:
RepKey, int, PK, identity
.....
I can link RepBridge and Rep via RepKey but not RepBridge with Sales via RepBridgeKey.
From your answer, it looks like I would have to include RepKey in my Sales table and do the FK link on both fields?
Thanks.
|||
No, I think you can only make an unique index on RepBridgeKey from RepBridge (is possible because is identity)
You can make the following links vis-a-vis your vision of table schema:
Rep <RepKey-->RepBridge
RepBridge <RepBridgeKey>Sales
|||John,
In RepBridge, I'm still not sure why you need RepKey as part of the Primary Key. It seems like BridgeRepKey is a unique identifier and would adequately serve as the PK (and the FK in Sales).
RepBridgeKey 'should' be all you need.
However, if, as said earlier, there is a business reason to complicate the Primary key in BridgeRep by adding RepKey -then you will have to add RepKey to Sales -at which point, the RepBridge table seems unnecessary. You would have to duplicate the rows in Sales for each Rep if there are multiple Reps involved with a single Sale.
Your sales table probably needs a better defined primary key. Typically a unique sales identifier is created -something like an INVOICE number, SalesOrder number, etc. In your Sales table above, only one sale would be allowed per day per Rep. That may be reasonable, but is it realistic? I would prefer a [SalesID int IDENTITY Primary Key] field
Typically, a 'bridging' table will have an IDENTITY field as PK, and the PK's from BOTH others tables as FK.
|||RepBridge
RepBridgeKey, int IDENTITY PK
RepKey FK to Rep
SalesKey Fk to Sales
I see what you're saying....the bridge table does not need the RepKey as part of its primary key. I was thinking that each new sales rep would have to trigger a new bridge record but this can be done with only one PK identity field.
There is a reason behind the key fields in the sales table, but perhaps I shall also revisit that side issue.
Thanks again.
