May 17, 2010 at 12:20 am
CREATE TABLE Person
(
Person_id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
last_name VARCHAR(20) ,
first_name VARCHAR(20) ,
Address_id INT NOT NULL,
gender char(1) CHECK (gender IN ('M','F')),
CONSTRAINT Address_Address_id_fk
FOREIGN KEY (Address_id)
REFERENCES Address(Address_id)
)
GO
CREATE TABLE Address
(
Address_id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
Address_line1 VARCHAR(50) NOT NULL,
Address_line2 VARCHAR(50) NULL,
City VARCHAR(30) NOT NULL,
State VARCHAR(30) NOT NULL,
Postal_Code VARCHAR(10) NOT NULL,
)
GO
Now i have a registration form with entry fields for both person info and address info. Can anyone please help me by telling how can i store the address related info in the Address table and then Store the Address_id in the Person table while storing other Person attributes ?
Address_id is a foreign key in the Person table.
May 18, 2010 at 4:57 am
Check out SCOPE_IDENTITY in BOL here
May 18, 2010 at 10:14 am
You might want to think about reversing the foreign key. In you scenario, you will have to insert the address first and then get the ID to put into the Person table. Also, the an person can only belong to one address, but an address could be assigned to multiple people. If the foreign key were on PersonID and PersonID were on the Address Table, then, logically you would add the Person, get the ID and add it to the Address. I would think it would be easier searching for a person than an address. As I don't know your requirements, it's just my 2 cents.
Also, if a person can only have one address, why would you break it out into 2 tables?
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 18, 2010 at 11:51 am
Mike01 (5/18/2010)
Also, if a person can only have one address, why would you break it out into 2 tables?
I'm not sure of OP's requirements, but there could be scenarios where you would like to maintain two separate tables. Say for a food ordering system, where 100's of users can share the same address because they all are going to give the same delivery address -- of their office. I know this scheme gets very ugly for various other scenarios and this simplistic FK will not help. I just put it out as an example where it can be used. 🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply