a primary record can only have 1 sub record?
in [Programming SQL]
|
Prev: SQL Server 2000 log shipping monitor
Next: Create text file
From: Andy B. on 3 Dec 2009 06:50 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?
From: Scott Morris on 3 Dec 2009 08:18 "Andy B." <a_borka(a)sbcglobal.net> wrote in message news:uuyVU7AdKHA.5608(a)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.
From: Justin Blanding on 3 Dec 2009 09:50 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? > > > . >
From: TheSQLGuru on 3 Dec 2009 11:31 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(a)sbcglobal.net> wrote in message news:uuyVU7AdKHA.5608(a)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? > >
From: John Bell on 3 Dec 2009 15:33
"Andy B." <a_borka(a)sbcglobal.net> wrote in message news:uuyVU7AdKHA.5608(a)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 |