creating relationships with sql server 08 express in visual web dev 2010

Question

OK so I have never ever touched asp.net and have absolutly no idea what I am doing. I create database structures via SQL Scripts not GUI enviornments. So I am trying to learn ASP.NET and I have started an MVC Web App project.

I have an account table and an address table and an account_addresses_xref table.

table: 
account
fields:
account_id
name
status_id
blah blah blah

table:
addresses
fields:
address_id
city
state
blah blah blah

table:
account_addresses_xref
fields:
account_id
address_id

Where account_id in the xref should be a FK and constrained to the accounts table and address_id in the xref should be a FK and constrained to the addresses table, and an entry of one record w/ an account_id/address_id combo should BE the PK to the xref table.

Creating these tables was fairly easy. I now want to make them constrained to each other. So I right clicked the account_id field on the xref table and chose 'relationships', It says to click "Tables and Columns Specifications" so I did. It askes for the Primary Key table in which I choose "accounts". It had defaulted the Forien key table to "account_addresses_xref" naturally. I click OK and it tells me "The new relationship must have at least on pair of realted columns"....oooook so I click "Help" and it tells me "The relationship must relate at least one column from the foreign-key table to at least one column in the primary key table. Select a column from each table or delete the relationship" -- OK I don't see where I can do this, I thought that is what I was doing.

Is there a way I can just write out my database structure in a script and run it and not have to use this crap?

Please advise both questions, how do I create relationships through the GUI and how do I hack in my own damn SQL script. Thanks in advance.

Answer 1

Before it will show the columns to select to match on, the column I right-click must be set as a primary key first.

Answer 2

To create your account_addresses_xref table you can use the below T-SQL query, providing account_id and address_id are primary keys of their tables.

CREATE TABLE account_addresses_xref
(
  account_id INT NOT NULL FOREIGN KEY REFERENCES account (account_id),
  address_id INT NOT NULL FOREIGN KEY REFERENCES addresses (address_id),
  PRIMARY KEY CLUSTERED (account_id, address_id)
)