Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
newbie23
Starting Member
3 Posts |
 Posted - 2010-02-27 : 03:27:03
|
| i m having two tables..parent table(Cid(PK),password,email)Child table(Cid(FK),first_name,last_name,DOB,phone_no)i want to relate both tables using one to one relationship...i mean when i insert customer ID in parent table Child table should automatically create one row with same Customer Id with other values like first name ,last name SET to NULL.....how is that possible?in Visual Studio or using SQL server 2005?please help me. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-27 : 03:29:01
|
| why do you want this to happen automatically? actually in normal practise, this is done explicitly by means of insert procedure------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
newbie23
Starting Member
3 Posts |
Posted - 2010-02-27 : 03:35:39
|
| Visakh thank you for quick reply ..i want this to happen automaticaly because actually parent and child table are same but just child table contains some more details about customer..and it is not necessary that customer has to fill his first name and phone no..i strictly want that both tables have same no. of ROWS....thats what i really need if i delete rpw from any of the tables ...the other table should delete corresponding row automatically,So ur solution is like I need to use two seprate Insert Statements for both tables?? |
|
|
|
dportas
Yak Posting Veteran
53 Posts |
Posted - 2010-02-27 : 03:43:11
|
| If you want there always to be the same number of rows in each table then I suggest you combine them into one table. If you have a reason for not doing that then please explain why.If the data in the second table is optional then it would be better not to insert a row unless and until that data is supplied. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-27 : 04:56:20
|
| if you want to insert/delete from two tables simultaneously, then you can use trigger for that------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-27 : 05:11:15
|
| This is not strictly parent : child relationship, it is 1 : 1 - for every records in Table1 there must be a record in Table2 (and vice versa)"If you have a reason for not doing that then please explain why."We do this in a few instances:1) Large TEXT/VARCHAR(MAX) columns in second table - assist with inline rebuild of indexes on the first table (which is blockled if you have TEXT/VARCHAR(MAX) etc.)2) Very high update-rate columns in separate table. For Customer we would have an Archive table (row inserted every time Customer record changes) but we would not want, say, "number of pages viewed today" to cause an archive-table update, so we would put that in a second table that was not subject to the archive trigger. Another example would be a hierarchy table where we had columns for Sequence (walking order) and Path; then change when the record is moved elsewhere in the tree, or if other nodes are inserted, we therefore keep these in a separate table.Newbie23:My approach would be:Stored Procedure (SProc) for creating a new customer which takes parameters for password & email, and optional parameters for first_name, last_name, DOB, phone_noSProc inserts a row in Table1 (with Password + EMail) and uses scope_identity() to get the ID number that SQL Server allocated.If then inserts a row in Table2, using the new ID number, for first_name, last_name, DOB, phone_no (all of which may be NULL if the user did not provide anything, but the row exists in Table2)I would do both inserts in a TRANSACTION so that if one failed the other was rolled back - so either both exist, or neither.You applications calls the SProc with the parameters, and it creates both records (or returns an error) - and it probably returns the Customer ID to your application on as well.For delete I would put a DELETE TRIGGER on each table.For the trigger on Table1 I would delete the corresponding row in Table2.For the trigger on Table2 I would raise error if the row in Table1 still exists. (i.e. if someone tries to delete direct from Table2 that will raise error; if they delete from Table1 that will automatically delete Table2 row also.Or you could put an FKey constraint on Table1 : Table2 so that Table2 has a Cascade delete when the row is deleted from Table1 (I personally don;t use them and prefer to do it in my own code) |
|
|
|
newbie23
Starting Member
3 Posts |
Posted - 2010-02-27 : 10:54:41
|
| Thank you dportas,visakh16,Kristen..As mentioned by kristen i am going to create stored procedure and will insert two times in both tables. dportas there is nothing special i just dont want to make first table very big thats why i decided to put optional information about customers in second table... Thank you for answering so quickly it is really helping. |
|
|
|
|
|
|
|
|
noob question...about foreign key and primary key