Define More Than One Relationship Per Table?

Apr 11, 2004

Why is it not possible to define more than one relationship per table?





i have a primary table that i would like to cascade deletes to 2 other foreign tables in 2 separate relationships. why can't i do this and what are my alternatives?





thank you

View 4 Replies


ADVERTISEMENT

Define A Relationship In Express With A Concatenated Foreign Key?

Jul 4, 2015

I'm having trouble defining a relationship in SQL Express with a concatenated foreign key.

I have 3 tables. Date, Company and Detail

Date has a PK1

Company has a PK2

Detail has a concatenated foreign Key of PK1, PK2

When I go to set the foreign key relationship in Management Studio 'Tables and Columns', it seems I can only select 1 primary key table on the left for my 2 foreign keys. How can I add 2 primary key tables to point to each FK in my detail table?

View 4 Replies View Related

How To Dynamically Enter Error Code And Description In A Pre-define Table?

Aug 4, 2006

Hi,

I want to implement error handling my SSIS package. for this I am putting an execute SQL task for a container(which contains different interlinked tasks) in event handler.

Say my first task fails in this container.Immedaitely my sql task which inserts the error code and description should exceute in the event handler and populate my error table in OLAP database.

Any help will be great SSIS gurus.

Thanks in advance.

Regards,

Aman

View 4 Replies View Related

DB Design :: Define Automatic Sequence On A Table Primary Key Field?

Jun 13, 2015

I am designing a database. I want to define a automatic sequence  on a table primary key field. what is the best solution for it?

I know I can enable identity property for a field, but  it has some problems ( for example its seed jumps on restart and unsuccessful events)

I also can use some calculated sequences. for example I can calculate max of the filed values and after incrementing use it as key for new inserted record.

which one is better?

View 3 Replies View Related

Why I Got The Message As You Need To Create The Many-to-one Relationship Between The Case Table And The Nested Table?

May 30, 2007

Hi, all experts here,



Thank you very much for your kind attention.



I am trying to create a new mining structure with case table and nested table, the case table (fact table) has alread defined the relationships with the nested table(dimension table), and I can see their relationship from the data source view. But why the wizard for creating the new mining structure showed that message? Why is that? And what could I try to fix it?

Hope it is clear for your help.

Thanks a lot for your kind advices and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

View 4 Replies View Related

How Can I Create A One-to-one Relationship In A SQL Server Management Studio Express Relationship Diagram?

Mar 25, 2006

How can I create a one-to-one relationship in a SQL Server Management Studio Express Relationship diagram?

For example:
I have 2 tables, tbl1 and tbl2.

tbl1 has the following columns:
id {uniqueidentifier} as PK
name {nvarchar(50)}

tbl2 has the following columns:
id {uniqueidentifier} as PK
name {nvarchar(50)}
tbl1_id {uniqueidentifier} as FK linked to tbl1.id


If I drag and drop the tbl1.id column to tbl2 I end up with a one-to-many relationship. How do I create a one-to-one relationship instead?

mradlmaier

View 3 Replies View Related

Power Pivot :: Use Relationship Not Overriding Conflicting Active Relationship?

Oct 14, 2015

I have four tables with relationships as shown. They have a circular relationship and so one of the relationships is forced to be inactive.

   Operation (Commodity, OperationKey)   ==========> 
Commodity (Commodity)
                      /                                                                        
/
                       |                                                                         
|
   Advice (OperationKey)       ====== (inactive)=======>
Operation Commmodity (Commodity, OperationKey)

I have the following measure:

Advice No. Commodity:=CALCULATE (
COUNTROWS ( 'Advice' ),
USERELATIONSHIP(Advice[OperationKey],'Operation Commodity'[OperationKey]),
operation
)

However, the userelationship function does not override the active relationship between Operation & Advice and so the measure is limited to Advices directly filtered by the Operation table.

If I delete the relationship between Operation and Advice, then the measure works as expected i.e. Operation indirectly filters Operation Commodity which filters Advice.

View 9 Replies View Related

Table Relationship

Apr 5, 2007

Hello, I created some SQL 2005 tables using Microsoft SQL Server Management Studio. I need to get the script code of those tables. I was able to do that by right clicking over each table. But how can I get the code for the relationships between the tables? Can't I create relationships between two tables by using T-SQL? Thanks, Miguel

View 3 Replies View Related

What Relationship To Use Between These Two Table?

Feb 7, 2008

Hi I have two tables:
1.) Operator-OperatorID{PK, int, not null}-OperatorName{varchar(100), not null}-Enabled{bit, not null}-PasswordChange{bit, not null}-BirthDate{datetime, not null}
2.) Password-PasswordID{PK, int, not null}-Password{varchar(50), not null}-ExpirationDate{datetime, not null}
I'm not sure how to design and layout these two tables. The layout of these two tables is completely flexible as the application has not been deployed. I'm open to any good suggestions.
For each Operator I want to stored up to 3 previous passwords plus their current password. The password change field is so that if the operator's password expires or gets reset, they will be forced to enter a new password. This is a simple internal company application, so password encrypting is not necessary. The ExpirationDate indicates the date that the password will expire.

Hope to hear from someone soon! Thank you!

View 3 Replies View Related

Relationship Inside The Same Table

Sep 19, 2005

i ve got a database that has a table...that table has a relationship between its primary key,and another field,actuelly i did it for doing menus and sub menus,so each menu has an ID say menuID and it has DEPTH and parentID which is the menuID of the parent...the problem is that i can not use "Cascade update Related Fields" or "Cascade Delete Related Records" which are really necessary ...for example when deleting parent ,not to have a child lost :)i hope i ll have an answer soon,and thanks in advancedPS: i am using MSSQL 2000 evaluation

View 4 Replies View Related

Naming Of Relationship Table

Jun 10, 2006

We have two tables. Users and Projects and there is a many-to-many relationship.Ex. A user can be assigned into multiple projects.For the relationship table, should the table name be UserProjects or ProjectUsers?Also should it be singular or plural? (ex. UsersProjects or ProjectsUsers)?

View 2 Replies View Related

Create Table With Many-to-many Relationship...

Dec 5, 2006

Hi, I come back again.
Can anyone help me to create table with many-to-many relationship. Here is my three tables
tbl_Networks
(
NID int identity(1,1) primary key,
NetworkName nvarchar(256)
)

tbl_Categories
(
CID int identity(1,1) primary key,
CateName nvarchar(256),
NID int
)

tbl_Sim
(
SID int identity(1,1) primary key,
NID int,
CID int,
NameOfSim nvarchar(256)
)
My problem is 1 value in tbl_Sim may have multiple values in table tbl_Categories and vice versal. And I don't know how to organise them


So I need some help...

View 5 Replies View Related

Table Relationship. What Am I Doing Wrong?

Feb 27, 2008

Hello,

I have 3 tables: Articles, ArticlesTags and Tags.
ArticleTags relate Articles and Tags records.

When I try to delete a record in Tags I get the following error:

The DELETE statement conflicted with the REFERENCE constraint

"FK_ArticlesTags_Tags". The conflict occurred in database "MyDB", table "dbo.ArticlesTags", column 'TagID'.
The statement has been terminated.

What am I doing wrong?

Here is how I am creating my tables:

create table dbo.Articles
(
ArticleID uniqueidentifier not null
constraint PK_Article primary key clustered,
Body nvarchar(max) not null

)
create table dbo.ArticlesTags
(
ArticleID uniqueidentifier not null,
TagID uniqueidentifier not null,
constraint PK_ArticlesTags
primary key clustered (ArticleID, TagID),
constraint FK_ArticlesTags_Articles
foreign key(ArticleID)
references dbo.Articles(ArticleID)
on delete cascade,
constraint FK_ArticlesTags_Tags
foreign key(TagID)
references dbo.Tags(TagID)
)
create table dbo.Tags
(
TagID uniqueidentifier not null
constraint PK_Tag primary key clustered,
[Name] nvarchar(200) not null
)

Basically, what I need is:

1. If an Article is deleted then:
> Delete all records for that Article in ArticlesTags
> Don't delete any Tag in Tags.

2. If an Tag is deleted then:
> Delete all records associated with it in ArticlesTags.
> Don't delete any Article in Articles.

What am I missing?

Thanks,
Miguel

View 2 Replies View Related

Table Relationship In VB Express

Dec 19, 2006

I
have 3 tables I am trying to relate for a music player. I was
following the example in the msdn however, my relationships do not seem
right. Here are tables i want to relate:

Table 1:
ArtistID
ArtistName:

Table 2:
RecordingID
RecordingName
ArtistID

Table 3:
TrackID
TrackName
TrackNumber
TrackLocation
RecordingID

So
the main idea here is that the foreign keys are recordingId and
artistID. So what i did is created the 3 tables and then make a
diagram to create the relationships. I then was reading this post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=219696&SiteID=1

What
i want to do is use 2 different text boxes lets say and as i move from
the records in the Record_table (2) the corresponding artist will
change with it. However in the dataset the relationship looks like
this:
Artist -> Recording -> Track.... inorder for me to get
this relationship to work correctly i have to change all the
relationships in the dataset diagram. This way the dataset would look
like this: Track -> Recording ->Artist. This way i can use the
2 bindings to reference each other as stated in the link above. why
doesnt the relationship of the database know this already? why do i
have to change the relationship in the datasets.

View 3 Replies View Related

Problems Creating Second Relationship To The Same Table

Apr 6, 2007

I have two tables: ads and categories. I have an existing relationship: categories.id (PK) and ads.categoryid (FK). Now I want to create additional relationship with categories.id (PK) on ads.SecondCategoryID (FK). When I try to save it in SQL Manager I get the following error:
- Unable to create relationship 'FK_classifieds_Ads_classifieds_Categories2'.  The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_classifieds_Ads_classifieds_Categories2". The conflict occurred in database "mydb", table "dbo.classifieds_Categories", column 'Id'.
 

View 6 Replies View Related

Search On Table With Recursive Relationship

Apr 15, 1999

I am using tables with recursive relationships extensively. For example the table tbComponent has a primary key called Co_ID and a foreign key called Co_Co_ID which references the field Co_ID. This allows a component to have unlimited child components, and each child component can have an unlimited number of tiers of children. I have a few question for which I have seen no documentation on.

1. How can I create a view or a SP that will return a component record and all of its children and children's children records down to the last/lowest child record?

2. I need to be able to do a search in this table. Example:

Table: tbComponent
Columns: Co_ID Integer
Co_Co_ID Integer
Co_Name Text
Co_Attribute Text
Co_Category Text


Note: the data for Co_Category comes from a lookup table with also has a recursive relationship to itself where a category can have an unlimited number of tiers of children categories.

A typical group of records could be something like this:


Co_ID Co_Co_ID Co_Name Co_Attribute Co_Category

1 1 Car Blue Ford
2 1 Body Steel Parts
3 2 Door Front Parts
4 3 Invoice April 1 1999 Accounting

Ok, say there is over a million records in this table. Say I want to query this table and return all of the invoices for cars between March 1 1999 and May 1 1999. Say for example that there are less records where Co_Name has a value of Car then there are records with a value of Invoice, so logically I would set some kind of criteria to limit only invoices with where Co_Name = "Car". That's easy, I can return a result set of all the records Where Co_Name = "Car" and I can hold these in a view or a temp table. Now I need to query this View or temp table and see if it has any children records records Where Co_Name = "Invoice" . The problem is that the Invoice child could be a child record directly under the "Car" record, or 10 levels of children records down. The logic for this would be:

If Co_Name = "Car" Then Select * Where Co_Co_ID = 1, then take all of those record's values in Co_ID and run another statement Where Co_Co_ID = X and so on until there are no more children to search. If any of those records have a value of Invoice in Co_Name then return them.

Please don't give any speeches on Normal Relationship Database design suggesting a typical design using something like a Car table, a parts table, and a invoice table. I used the example above to demonstrate the dynamics of doing a search on a recursive relationship. I understand relationship theory and a recursive relationship is what we need to use in our situation.

Thanks for any and all input!

View 1 Replies View Related

Create A View For One-to-many Relationship Table

May 8, 2006

hi..

I would like to create a view for two tables which have a one-to-many relationship.


Code:


Table: Supplier
Supp_ID
1

Table:Supplier_category
Supp_ID,StockCategoryID
1,56
1,57
1,90



How can i create a view that has columns like below:
Supp_ID, Stock
1,[56,57,90]

Thanks in advance.

View 1 Replies View Related

Using Same Table Having Parent And Child Relationship?

Mar 23, 2014

Below is my sample data of my table named "Groups"

Code:
with Groups as (
select 1 as GroupId,'Oracle' as GroupName,0 as IdParentGroup union all
select 2 as GroupId,'Microsoft' as GroupName,0 as IdParentGroup union all
select 3 as GroupId,'IBM' as GroupName,0 as IdParentGroup union all
select 4 as GroupId,'SunMicrosystem' as GroupName,1 as IdParentGroup union all
select 5 as GroupId,'peoplesoft' as GroupName,1 as IdParentGroup union all
select 6 as GroupId,'mysql' as GroupName,1 as IdParentGroup union all
select 7 as GroupId,'Nokia' as GroupName,2 as IdParentGroup union all
select 8 as GroupId,'EShop' as GroupName,2 as IdParentGroup union all
select 9 as GroupId,'Meiosys' as GroupName,3 as IdParentGroup union all
select 10 as GroupId,'UrbanCode' as GroupName,3 as IdParentGroup )
select * from groups;

Expected result:

Code:
with ExpectedResult as (
select 'Oracle' as GroupName,'SunMicrosystem' as SubGroup union all
select '' as GroupName,'peoplesoft' as SubGroup union all
select '' as GroupName,'mysql' as SubGroup union all
select 'Microsoft' as GroupName,'Nokia' as SubGroup union all
select '' as GroupName,'EShop' as SubGroup union all
select 'IBM' as GroupName,'Meiosys' as SubGroup union all
select '' as GroupName,'UrbanCode' as SubGroup )
select * from ExpectedResult;

some sample query to how to achieve this parent-child has the same table.

View 9 Replies View Related

SQL 2012 :: Junction Table In Many To Many Relationship

May 5, 2014

Why do we need a junction table in a many to many relationship? Why can't everything be in just 2 tables?

View 9 Replies View Related

Create Table With Recursive Relationship

May 16, 2006

I am fairly new to SQL and I am currently trying to createa SQL table (using Microsoft SQL) that has a recursiverelationship, let me try to explain:I have a piece of Data let's call it "Item" wich may again contain onemore "Items". Now how would I design a set of SQL Tables that arecapable of storing this information?I tried the following two approaches:1.) create a Table "Item" with Column "ItemID" as primary key, somecolums for the Data an Item can store and a Column "ParentItemID". Iset a foreign key for ParentItemID wich links to the primarykey"ItemID" of the same table.2.) create separate Table "Item_ParentItem" that storesItemID-ParentItemID-pairs. Each column has a foreign key linked toprimary key of the "Item" Column "ItemID".In both approaches when I try to delete an Item I get an Exceptionsaying that the DELETE command could not be executed because itviolates a COLUMN REFERENCE constraint. The goal behind these FK_PKrelations is is that when an Item gets deleted, all childItems shouldautomatically be deleted recursively.How is this "standard-problem" usually solved in sql? Or do I inned toimplement the recursive deletion myself using storedprocedures or something ?

View 3 Replies View Related

Want 1 To Many Relationship When Child Table Has No Primary Key

Jul 20, 2005

I want to create a 1-many relationship. Parent table has a primarykey, child table has no primary key. The child table does have anindex with all four fields of the parent's PK. How can I do this?Thanks, Bob C.

View 9 Replies View Related

How Can I Get Primary Key Col And Other Two Cols In The Same Table Become One To One Relationship.

Jun 12, 2006

For ex.

Table Match_List ( MatchID, UserID_A, UserID_B)

constraints like

MatchID primary key

UserID_A <> UserID_B

unqiue index (UserID_A,UserID_B)

but I wish to exclude duplicated rows like 1,1,2 & 2,2,1,

cause UserA to UserB, and UserB to UserA are the same thing.

How can I get this?

View 7 Replies View Related

Transact SQL :: How To Update Table In Many To Many Relationship

Nov 30, 2015

I am having challenge to update the redemption table from the multiple card activation table.  I want to update the redemption table with the activation date closest to the redeem date.  

For example:  Redeem date 20071223, I need to update the top row Date, Year, Period fields from the Card activation table.

Redeem date 20071228, I want to refer to the second row in the Card activation table date 20071228.  
Redeem date 20080316 or later, I want to use the last row in the card activation table date 20080316.

How to modify the update query to select the right activation row accordingly?

Below is my partial code I used but it always pick the 20071223 date to update my redemption table.

 CREATE TABLE #Card
 (
       [CardNumber] varchar(20)
 ,[ Date] int
 ,[ Year] int
 ,[ Month] int
 ,[ Period] int
  )  
 
[Code] ....

View 13 Replies View Related

Trying To Enforce Ref Integrity On Hierarchy Table Relationship

Dec 28, 2007

I've got a table that includes:CREATE TABLE [dbo].[Content] (  [Id] int IDENTITY(1, 1) NOT NULL,  [ParentId] int NULL,
I'm wanting to make sure that a ParentId must be in the table as Id someplace else.  When I try to do it by making it a foreign key  get the error:
--------------- SQL ---------------
ALTER TABLE [dbo].[Content]ADD CONSTRAINT [Content_fk3] FOREIGN KEY ([Id])   REFERENCES [dbo].[Content] ([ParentId])   ON UPDATE NO ACTION  ON DELETE NO ACTIONGO
---------- ERROR MESSAGE ----------
There are no primary or candidate keys in the referenced table 'dbo.Content' that match the referencing column list in the foreign key 'Content_fk3'.Could not create constraint. See previous errors.
 
Any ideas? 
 
ALTER TABLE [dbo].[Content]ADD CONSTRAINT [Content_fk3] FOREIGN KEY ([Id])   REFERENCES [Content].[dbo] ([ParentId])   ON UPDATE NO ACTION  ON DELETE NO ACTIONGO
 

View 3 Replies View Related

T-SQL (SS2K8) :: Using Same Table Having Parent And Child Relationship

Mar 23, 2014

Below is my sample data of my table named "Groups"

with Groups as (
select 1 as GroupId,'Oracle' as GroupName,0 as IdParentGroup union all
select 2 as GroupId,'Microsoft' as GroupName,0 as IdParentGroup union all
select 3 as GroupId,'IBM' as GroupName,0 as IdParentGroup union all
select 4 as GroupId,'SunMicrosystem' as GroupName,1 as IdParentGroup union all

[Code] ....

Expected result:

with ExpectedResult as (
select 'Oracle' as GroupName,'SunMicrosystem' as SubGroup union all
select '' as GroupName,'peoplesoft' as SubGroup union all
select '' as GroupName,'mysql' as SubGroup union all

[Code] ....

How to achieve this parent-child has the same table.

View 5 Replies View Related

Any Built-in Function To Show Table Relationship?

Feb 5, 2007

Hi guys

I have a table which I need to analyze, what jobs this is working with, what stored procedure is calling this table etc. Is there any built-in function to show me or anybody know how to do it? This is my new job, and there are hundreds of stored procedures and jobs, do i really have to go thru one by one?

Please help.

Michelle

View 3 Replies View Related

Database Table Data Modifications With PK/FK Relationship

Mar 21, 2007

I have two tables (T1 and T2). In T1 I have a field FT1 that is aprimary key in T2 I have a field FT2 that is a foreign key linked toFT1. These fields have been populated with data. Lets say that in onerow of data I have in T1 under FT1 "my cell" as the data entry,similarly with T2 under FT2 I have 2 rows of data that also have "mycell" as the data entry. What is the best line of action is I wantedto change "my cell" to "my data"?

View 3 Replies View Related

Table Relationship Question For Unique Record

Apr 9, 2008

Hello Hello,
This Noob has a question.
I have been atempting for days to figure this out.
I have this set up: (See Below)

The Problem I am having is occuring at the Table named OU in Maroon below.
It combines 2 chains of keys into one table to try and make a unique record.
The problem is that it is not.

Pasted below is a Query run on the data within the tables:
As you can see certain data is getting duplicated in this tabel.
I want 1 Unique record in the OU table based upon the Study ID Primary Key and the zCombined Primary Key.

Any Ideas as to what is wrong, or how to make it work would defineatly be appreciated.

Thanks a Bunch


Table: Sponsor
PK: Sponsor ID

Table: Protocol
PK: ProtocolID
FK: Sponsor ID

Table: Study
PK: StudyID
FK: ProtocolID
Table: OUPK: OUIDFK: StudyIDFK: zCombinedID

Table:zCombined
PK: zCombinedID
FK: TempID ~ To Temp Table
FK: ShipTypeID ~ To Ship Type Table
FK: CoordinatorID ~ To Coordinator Table
FK: BoxTypeID ~ To Box Type Table







Sponsor

Protocol

Study

OU~Column1

OU~Column2

Box

Coordinator

Ship Type

Temp


Omni

AAAAA

XPG

S0101

NA

Wheaton

Raul Vargas

Daily

-70


Omni

AAAAA

XPG

S0101

NA

Matrix

Raul Vargas

Daily

-70


Omni

AAAAA

XPG

S0101

NA

B-1

Raul Vargas

Daily

-70


Omni

AAAAA

XPG

S0101

NA

9 x 9 4mL

Raul Vargas

Daily

-70


Omni

AAAAA

XPG

S0101

NA

9 x 9 2mL

Raul Vargas

Daily

-70


Omni

AAAAA

XPG

S0101

S0201

Wheaton

Mike Keane

DNA

-40


Omni

AAAAA

XPG

S0101

S0201

Matrix

Mike Keane

DNA

-40


Omni

AAAAA

XPG

S0101

S0201

B-1

Mike Keane

DNA

-40


Omni

AAAAA

XPG

S0101

S0201

9 x 9 4mL

Mike Keane

DNA

-40


Omni

AAAAA

XPG

S0101

S0201

9 x 9 2mL

Mike Keane

DNA

-40

View 3 Replies View Related

Link Table Versus XML Column For One-to-many Relationship

Jun 10, 2007

I am designing a database schema where several tables have one-to-many relationships to records in other tables. One way to implement it is to create a link table for each pair of tables that have a relationship:





identity
Table1_ID
Table2_ID

1
12
9

2
12
15

3
18
42With the SQL 2005 support for the XML data type, there is the possibility of storing the IDs in an XML column. The XML stored with a record (equivalent to record 12 from the above example) might look like this:

<Links>
<Table2_LinkType>
<ID>9</ID>

<ID>15</ID>
</Table2_LinkType>
<Table8_LinkType>

. . .

</Table8_LinkType>
</Links>The XML column method has the advantage of not requiring that a separate table be created but does not enforce referential integrity. The link table method has the advantage of allowing constraints to enforce referential integrity but has the disadvantage of requiring the creation of a separate table for each pair of tables having a relationship and joining to an additional table has performance implications. Implementing standard Add, List and Delete operations for the link table method is straightforward. As a test and to familiarize myself with the new XML features, I created Set, List and Delete stored procedures for the XML method. Both methods will work.



In deciding which method to go with are there any other issues I should be considering besides database integrity, complexity and possible performance issues?
From the standpoint of best practices and coding standards is one method preferred over the other?
Here is some additional information. The data set I will be working with has table sizes numbering in the hundreds of thousands. Any given record will only be linked to at most a half-dozen records in any other table.

I searched on-line for information and I was able to find plenty of good articles discussing how to use the new XML data type in SQL. However, I was not able to find any information on when not to use the XML data type over equivalent joined-table methods.

View 1 Replies View Related

SQL Tools :: Server ImportExport Wizard - Table Relationship

May 27, 2015

I'm using SQL Server 2012. I've 2 databases one is CopyDatabase and other is PasteDatabase.

CopyDatabase has 2 tables with relationship to each other, and the PasteDatabase is empty. I want to Export Data from CopyDatabase to PasteDatabase. If I do that, it does work but I came to realize that it destroys the relationship between these 2 tables.

Same is for the Import, in case of PasteDatabase. How to maintain the relationship between them while moving the tables.

View 4 Replies View Related

Insert Statement For Junction Table On Many To Many Relationship Ms Sql Server 2005

Oct 10, 2007

Hello,

This seems like such a simple problem but I am new developer even through I have been on the administration end of things for some time. I will go into more detail about my tables and there relationships below. Anyway, I am trying to create a many-to-many relationship within ms sql server 2005. I have created both of my primary tables and also a junction table per the directions on microsoft's website all per ms's instructions as stated here...

http://msdn2.microsoft.com/en-us/library/ms178043.aspx

At then end of these instruction it states as a NOTE: The creation of a junction table in a database diagram does not insert data from the related tables into the junction table. For information about inserting data into a table, see How to: Create Insert Results Queries (Visual Database Tools).

http://msdn2.microsoft.com/en-us/library/ms189098.aspx

and these directions do not go into detail on how to do an insert on a junction table. And I cant find out how to do this anywhere on the internet... I did create a T-SQL INSERT statement in a trigger as listed below but I end up getting an error AS LISTED BELOW....

Here is how I set everything up...

PetitionSet table consists of:

PetitionSetID int auto-increment primary key
PetitionSetName varchar(50) no nulls
PetitionSetScope varchar(50) no nulls


the Petition table consists of:

PetitionID int auto-increment primary key
PetitionSetID int no nulls
PetitionName varchar(50) no nulls


the SetToPetitionJunction table consists of:
PetitionSetID int
PetitionID int

And, there is a composite key made up of both the PetitionSetID and PetitionID fields.

I have created the foreign key relationships with DEFAULT VALUES from the SetToPetitionJunction table to each column's respective corresponding column in each of the tables: PetitionSet and Petition.


The trigger is on the Petition table and it has the following code:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: Name
-- Create date:
-- Description:
-- =============================================
ALTER TRIGGER .[SetToPetitionJunctionTrigger]
ON .[dbo].[Petition]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

INSERT INTO SetToPetitionJunction
(PetitionID, PetitionSetID)
SELECT Petition.PetitionID, PetitionSet.PetitionSetID
FROM Petition INNER JOIN
PetitionSet ON Petition.PetitionSetID = PetitionSet.PetitionSetID

END

I have created an asp.net 2.0 front end to insert values into the PetitionSet table and the Petition Table. And in the detailsview for the Petition table I manually insert the PetitionSetID field to the number that corresponds to an auto-generated number on the primary key of the PetitionSet table. So I am maintaining referential integrity...

The first time it works and inserts one record in the Junction table containing the PetitionSetID from the PetitionSet table and the PetitionID from the petition table.

Then when I try to add in another petition for the same petition set number just like I did the first time and then I get this error...

Violation of PRIMARY KEY constraint 'PK_SetToPetitionJunction'. Cannot insert duplicate key in object 'dbo.SetToPetitionJunction'.
The statement has been terminated.



David

All Rights Reserved in All Media







View 3 Replies View Related

How To Define Field Attribute For A Numeric Field In SQL Table?

Jan 19, 2005

I need create a field to store tax rate. I need only 2 decimal points. I defined the field as decimal, precision=5 and scale=2. Does it mean that it can hold value from 0.00 to 999.99?

View 12 Replies View Related

How To Write A Select Query To Return Records In A Many-to-Many Relationship - Junction Table???

Oct 26, 2006

Can Somebody please show me how to acheive this, using the order details in Northwinddatabase or any other good example. as much details as possible. Many Thanks!  

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved