a primary record can only have 1 sub record?

I have table A with the following columns:

ID int identity(1,1) which is a primary key
Name varchar(20) not null

I have table B with the following columns:
ID int identity(1,1) which is a primary key
Type varchar(10) not null

There will be a foreign key between table A and B using the ID columns. A 
few questions about this kind of layout:

1. Table A's records can only have 1 record from table B linked to it. How 
do you do this?
2. Table A is the "parent" table and table B is the "child" table. How do 
you do this?


0
Andy
12/3/2009 11:50:37 AM
sqlserver.programming 1873 articles. 0 followers. Follow

5 Replies
676 Views

Similar Articles

[PageSpeed] 5

"Andy B." <a_borka@sbcglobal.net> wrote in message 
news:uuyVU7AdKHA.5608@TK2MSFTNGP05.phx.gbl...
>I have table A with the following columns:
>
> ID int identity(1,1) which is a primary key
> Name varchar(20) not null
>
> I have table B with the following columns:
> ID int identity(1,1) which is a primary key
> Type varchar(10) not null
>
> There will be a foreign key between table A and B using the ID columns. A 
> few questions about this kind of layout:
>
> 1. Table A's records can only have 1 record from table B linked to it. How 
> do you do this?

Remove the identity attribute from TableB.ID.  When you insert a row into 
TableA (which you must do anyways to support the foreign key), simply use 
the generated ID value for the insert of the child row in TableB.

> 2. Table A is the "parent" table and table B is the "child" table. How do 
> you do this?

Create a foreign key constraint. 


0
Scott
12/3/2009 1:18:48 PM
Are you sure that is the design you want?

Sounds like you really want something like this:

Table A {
  ID int identity(1,1) primary key
  Name varchar(20) not null
  TypeID int null
}

Table B {
  TypeID int identity(1,1) primary key
  TypeDescription varchar(10)
}

Add a foriegn key where B.TypeID is the Primary Key, and A.TypeID is the 
Foreign Key


"Andy B." wrote:

> I have table A with the following columns:
> 
> ID int identity(1,1) which is a primary key
> Name varchar(20) not null
> 
> I have table B with the following columns:
> ID int identity(1,1) which is a primary key
> Type varchar(10) not null
> 
> There will be a foreign key between table A and B using the ID columns. A 
> few questions about this kind of layout:
> 
> 1. Table A's records can only have 1 record from table B linked to it. How 
> do you do this?
> 2. Table A is the "parent" table and table B is the "child" table. How do 
> you do this?
> 
> 
> .
> 
0
Utf
12/3/2009 2:50:01 PM
Adding to others comments, I think you will need a trigger on table B to 
roll back any attempt to insert more than one (or a second+) record there 
for a given tableA PK.

-- 
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"Andy B." <a_borka@sbcglobal.net> wrote in message 
news:uuyVU7AdKHA.5608@TK2MSFTNGP05.phx.gbl...
>I have table A with the following columns:
>
> ID int identity(1,1) which is a primary key
> Name varchar(20) not null
>
> I have table B with the following columns:
> ID int identity(1,1) which is a primary key
> Type varchar(10) not null
>
> There will be a foreign key between table A and B using the ID columns. A 
> few questions about this kind of layout:
>
> 1. Table A's records can only have 1 record from table B linked to it. How 
> do you do this?
> 2. Table A is the "parent" table and table B is the "child" table. How do 
> you do this?
>
> 


0
TheSQLGuru
12/3/2009 4:31:25 PM
"Andy B." <a_borka@sbcglobal.net> wrote in message 
news:uuyVU7AdKHA.5608@TK2MSFTNGP05.phx.gbl...
>I have table A with the following columns:
>
> ID int identity(1,1) which is a primary key
> Name varchar(20) not null
>
> I have table B with the following columns:
> ID int identity(1,1) which is a primary key
> Type varchar(10) not null
>
> There will be a foreign key between table A and B using the ID columns. A 
> few questions about this kind of layout:
>
> 1. Table A's records can only have 1 record from table B linked to it. How 
> do you do this?
> 2. Table A is the "parent" table and table B is the "child" table. How do 
> you do this?
>
>
Hi

You should look up foreign keys in books online to see how to create the 
relationship between the two tables, but it is a pretty fundamental part of 
a relational database, so you may want to look up something more generic.

To enforce uniqueness you can use a unique constraint.

John 

0
John
12/3/2009 8:33:23 PM
> Adding to others comments, I think you will need a trigger on table B to 
> roll back any attempt to insert more than one (or a second+) record there 
> for a given tableA PK.
>

No trigger. If he really has a 1-1 relationship, then:

Table A {
  ID int identity(1,1) primary key
  Name varchar(20) not null
}

Table B {
  ID int primary key
  Type varchar
foreign key ID references Table A
}

But you would really, Really, REALLY need to have a need for a 1-1 
relationship between tables A & B. 


0
Jay
12/5/2009 6:31:01 PM
Reply:

Similar Artilces:

Can I fade the edges of photos that I use?
I am trying to use photos in a publisher document that I want to fade the edges of, is this possible to do with publisher? -- D:o) krazy-4-coke wrote: > I am trying to use photos in a publisher document that I want to fade the > edges of, is this possible to do with publisher? Not really. You should use dedicated image manipulation software for this, such as Photoshop Elements, Paint Shop Pro, or Microsoft Digital Image. -- Ed Bennett - MVP Microsoft Publisher http://ed.mvps.org Ed, Are any of these programs typically come loaded on a computer?? I have another question - ca...

Add a specific Record to a Table based on a check box
I have a Table called ServiceTypes. Based on a User's input on a ProposalForm, ServiceTypes need to be added to a ProposalServicesTable. For instance, I have a Check Box on the ProposalForm. When a Check Box is clicked Yes, Access must search the ServiceTypes Table, select a specific ServiceTypeID, and add the ServiceType to the ProposalServicesTable. How can I add the proper Service record from the ServiceTable to the ProposalServicesTable based on the Check Box? I wouldn't do it that way. I'd use a listbox (with multi-select set to YES) that was sourced to the ServiceTab...

Can I delete my switchboard and make a new one?
I do not like my switchboard and would like to delete this one and start all over. But when I did delete it I couldn't make another one, because I couldn't delete the default. What should I do?Thanks,Dustin Just delete the form and the table that were created by the wizard, then start over.-- Rick B"Dustin" <Dustin@discussions.microsoft.com> wrote in message news:42F94A44-9352-4D02-9104-65593869B271@microsoft.com...>I do not like my switchboard and would like to delete this one and start >all> over. But when I did delete it I couldn't make another one, beca...

Sub Folders
Hi Is there a way to check if a folder has a sub folder and if yes get its name, also to loop until no sub folder is found. C:\Weeks Post\Sundays Post\Mondays Post\Tuesdays Post etc. Create Post array Post(0) = Sundays Post Post(1) = Mondays Post Post(2) = Tuesdays Post something like this, can you help? regards Ron On 24/11/2009 09:52, LondonLad wrote: > Hi > Is there a way to check if a folder has a sub folder and if yes get its name, > also to loop until no sub folder is found. > > C:\Weeks Post\Sundays Post\Mondays Post\Tuesdays Post etc. > ...

Connection filtering record
1. I have set up connection filtering. How can I see what mail is being rejected? 2. Since setting up connection filtering the email tracking shows all emails being delivered to the intended recipient AND the badmail folder. Could someone explain this? To answer your first question, take a look here: http://www.martijnjongen.com/eng/html/log_analyzer.htm Not sure about the second question. -- Mark Fugatt Exchange MVP http://www.exchangetrainer.com http://www.msexchange.org "Nate" <nmorse@aimcomm.com> wrote in message news:ewUBm6C3DHA.2056@TK2MSFTNGP10.phx.gbl... > ...

how can I drag formulas with other cell references
example cell A1 contains formula "=stdev(a2:a7) cell a2 contains formula "=stdev(a8:a13) Is it possible to drag the formula down so that cell a3 contains "=stdev(a14:19) and cell a4 contains "=stdev(a20:a25) and so on for cells a5, a6, a7 etc. etc. Hi Bram, See http://www.mvps.org/dmcritchie/excel/snakecol.htm#snkAddr B1: =stdev(OFFSET($A$1,6*ROW()-5,0):OFFSET($A$1,6*ROW(),0)) B2: =stdev(OFFSET($A$1,6*ROW()-5,0):OFFSET($A$1,6*ROW(),0)) It was easier to test and verify using SUM instead of stdev, but the formula is same NOTE the formulas are in a different colu...

Deleting record from bindingsource
Hi I have a winform app with form control bound to a binding source TblClientsBindingSource. The binding source has a data table as its data source as per below code; dAdapter = New OleDbDataAdapter("SELECT * FROM tblClients WHERE ...", Conn) dAdapter.Fill(dTable) TblClientsBindingSource.DataSource = dTable My question is; how do I delete the record currently pointed to by the binding source from the backend database table tblClients? Thanks Regards John wrote: > Hi > > I have a winform app with form control bound to a binding source &g...

can i have more sender in outlook setting ?
i using win server 2003 , exchange 2003 , i using exchange client in outlook to send & receive , but my boss have more than one mail address , can i use another AD mail address for send out mail , i have try to add a pop3 , the sender also is computer user thank your ...

I can't get rid of 1 "Payment to Send' Reminder-M07
On my Home Page in the Reminder section, I have a "1 Payment to Send" listed there. But there is NO payment to send. I"ve opened up back-up copies as far back as May and the reminder is there, but there is no payment that needs sending. Does anybody have an idea how I can get rid of this Reminder? Just removing the Reminder from Home Page doesn't do it. When you put the Reminder back on the Home Page, the "Payment to Send" is still there. Clicking on the link just takes me to Bill Summary page. ...

IE8 can't access Microsoft Office site when all other browsers can
When trying to access the Microsoft Office page using IE8 I keep getting the "Internet Explorer cannot display the webpage" message. If I use any other browser (Firefox, Opera, Safari, Chrome) I have no problem accessing the page. This happens whether running in normal or No Add-on's mode. Despite how many people keep complaining about all the problems with IE8, neither the MVP's or Microsoft acknowledge that they exist. Asus P5E Intel E8400 Core2Duo 3.0GHz 4 GB PC2-6400 DDR2 Windows 7 Ultimate (with all the latest updates installed) NIS 2010 (all up to date)...

is there any way I can trap for the use of navigation buttons?
Hi Is there any way I can trap for the use of navigation buttons, such as Next, Previous and New record? Stapes Probably. Trap what and when? "Stapes" <steve.staple@gmail.com> wrote in message news:7fe0fd6b-0da1-495e-a369-29bc72ca3af9@s8g2000prg.googlegroups.com... > Hi > > Is there any way I can trap for the use of navigation buttons, such as > Next, Previous and New record? > > Stapes On Wed, 16 Jan 2008 05:14:20 -0800 (PST), Stapes <steve.staple@gmail.com> wrote: No. And there shouldn't be a reason for that. There are plenty of events in...

how can I look into store.exe
I have an email sobig that i cant even download it on my outlook express 6.0.... The adviced for is to locate it on the Queues in Outbound or inbound messages on IMC, but if it is not in there the message may be stuck up in Store. How can I locate it and delete the massega in store.exe they say I can use the MFCmapi but i'm a newbie how can do this. Do I have to download the MFCMapi? what is it? thanks ...

can't change/delete bills and deposits
suddenly one day i realized that when i tried to change a bill, i could click "ok" but nothing would happen. I thought it was because i upgraded to 2004, so i deleted the bills and rescheduled. about 5 of the bills will not delete either. the error message is: "Money could not write to your Money file. The operation could not be performed or another application finished this task before you. Please try again." Now some of the newly scheduled bills will not allow me to change the properties, i click ok but nothing happens when i edit the series. I have repaired ...

Can't Install IBF for CRM on CRM Server
first i'm install IBF On my CRM v1.2 (it's name testcrm) after that i'm try to install IBF for CRM on my CRM Server but in Step "Publish Metadata" i'm type "http://testcrm:8082" and click next button but it's show message box "The Information Bridge metadata service location is not avaliable" and can't install it. -_-' . but i can access "http://testcrm:8082/IBFWriteService.asmx" and "http://testcrm:8081/IBFReadService.asmx". so how can i install it ? I had the same problem - try using http://localhost:8082 for...

Microsoft CRM 1.2 database export failed 01-27-06
I am receiving this message when I try to upgrade my CRM 1.2 to CRM 3.0, does any one have any idea what this means? ...

Can Work thread get a windows class and manipulate it?
Suppose that I create a work thread when my dialog box started, then in the thread I get the pointer to the dialog mfc class and call updateData method. Does that work? I tried, but failed. Is there any other way to do? thanks!! No, it won't work, don't even waste time trying. It is almost guaranteed to fail. You do not manipulate the windows owned by one thread from a different thread. What you do is PostMessage requests from the worker thread to the main UI thread, usually to the window that contains the controls. The fact that you are using UpdateData already says you are in t...

Why can't I send email messages with Trial Outlook 2007?
I have a new HP laptop that came with the 60 day trial of Office'07. I receive email but the send/reply/compose functions are grayed out. I am certain my settings are correct. Why can't I send email? KK What happens when you use the Test Account Settings? What is the exact error message you get? -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. ALWAYS post your Outlook version. How to ask a question: http://support.microsoft.com/KB/555375 After furious head scratching, KK asked: | I have a new HP laptop that came with the 60 day...

How can I keep the header in view at all times
In Word, a header and footer s always in view when you work in th document. However, I cannot seem to do the same in Excel - any suggestions please. Thanks - M Select a cell below your column header and just right of your ro header, then goto "Window" and select Freeze Panes. HTH J -- pinmaste ----------------------------------------------------------------------- pinmaster's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=626 View this thread: http://www.excelforum.com/showthread.php?threadid=49759 ...

Outlook Level 1 Attachment Security
Hi, How do you disable Outlook 2002 Level 1 Security for attachments. Thanks Gerry Take a look here, it may help: http://www.slipstick.com/outlook/esecup/getexe.htm "Gerry" <Kandels@bigpond.com> wrote in message news:077b01c34a03$4eebf630$a501280a@phx.gbl... > Hi, > > How do you disable Outlook 2002 Level 1 Security for > attachments. Check out http://www.slipstick.com/outlook/esecup/getexe.htm "Gerry" <Kandels@bigpond.com> wrote in message news:077b01c34a03$4eebf630$a501280a@phx.gbl... > Hi, > > How do you disable Outlook 2002 Level...

Someone must have asked this before but I can't find it!
Being a relative Excel newcomer I,m not sure of all the terminology so may have used the wrong terms when searching. What I am trying to do is create a worksheet in which a cell shows one of 3 values (LOW,AVERAGE or HIGH) according to the value in the cell to the left of it, which itself is the sum of the 2 preceding cells. To be more specific, cell D6 is Men, E6 is Women and F6 is"=SUM(D6,E6)". I then want G6 to show "LOW" if F6 is less than 5, "AVERAGE" if it is between 5 and 20, and "HIGH" if it is over 20. If anyone understands the question and can h...

Print Full Bleed in Publisher? Can I eliminate the non-printable a
Hi there I have a "Print Merge" document I am printing. It's basically tickets 6 up on a page and I'm using Print Merge to number the tickets. Also have it set up to print multiple (page size is 2.75inch by 5.5inch). I have the margins set to ZERO and the horizontal, and sides set to ZERO as well. My printer supports full bleed and I can print full bleed from this PC using the same driver with all other programs (Adobe Illustrator, Photoshop, etc, etc), just not with Publisher. I've checked the forums here and can't seem to find a solution. I've selected t...

The code below worked for a few days and just totally stopped working. What can I do to make sure it continues to work? Thanks!
The code below worked for a few days and just totally stopped working. What can I do to make sure it continues to work? Thanks! ----- Private Sub Worksheet_Change(ByVal Target As Range) Dim C As Range, D As Range Set D = Intersect(Range("A:A"), Target) If D Is Nothing Then Exit Sub For Each C In D On Error Resume Next Target.Offset(0, 6).FormulaR1C1 = "=RC[-2]&RC[-5]" ' - For Column E Target.Offset(0, 7).FormulaR1C1 = "=RC[-2]&RC[-5]" ' - For Column F Target.Offset(0, 9).FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC7,I.O.! R2C1:R5...

code for moving to new record in datasheet view
Access 2003 What's the vba code for moving to a new record when in datsheet view. Here's what I've tried but is errors saying the subform is not opened and yet this code is on an event in the subform: If cmbInvLU <> 289 Then DoCmd.GoToRecord acForm, "Forms!frmWOTRH![frmWOTRL subform].Form", acNewRec Thanks for your help. SAC, Since your already in the subform... try... DoCmd.GoToRecord , , acNewRec -- hth Al Campagna Microsoft Access MVP 2006-2009 http://home.comcast.net/~cccsolutions/index.html &quo...

Post Exp Date in Master Rcd (row) from trans record
Hi, I'm using Access 2003 via Xp Office Pro. I'm working on a annual membership database. Each member has to renew each year if they want to stay a member. I want to keep a history of their membership. I have a membership table and will have a renewal table where I create a record / row each time a member renews. Key to both tables are automatic generated numbers. When I post the renewal transactions, I would like to change the expiration date that is on the master record. I realize that I would have the same data in both records, but for simplicity of the r...

Can't use my MS Office
I have recently purchased the above software package. I have not registered my product, as I have been travelling and it has=20 been less than 3 weeks since purchasing and installing. I am not sure=20 if all of the office programs have been working, as the only ones I have = been using these past 3 weeks are Entourage and MSN Messenger. =20 This morning, I tried to use Word and it froze just after opening the=20 new document and I was only able to enter one letter. Then I tried=20 excel and it froze at the first cell inside the new book. Then I tried = to=20 re-open my previously working E...