Referential Integrity - Linking Multiple Tables To Transaction Table

Mar 3, 2006

I have transaction table where the rows entered into the transaction
can come a result of changes that take place if four different tables.
So the situation is as follows:

Transaction Table
-TranId
-Calc Amount

Table 1 (the amount is inserted into the transaction table)
- Tb1Id
- Tb1Amt

Table 2 (an amount is calculated based on the percentage and inserted
into the transaction table)
-Tbl2Id
-Tb2Percentage

Table 3 (the amount is inserted into the transaction table)
-Tbl3Id
-Tbl3Amut

Table 4 (an amount is calculated based on the percentage and inserted
into the transaction table. )
-Tbl2Id
-Tb2Percentage

How do I create referential integrity between the Transaction table and
the rest of the tables. When I make changes to the values in Table 1 -
4, I need to be able to reflect this in the Transaction table.

Thanks.

View 6 Replies


ADVERTISEMENT

Referential Integrity In Mutiple Tables. Need Help.

Oct 12, 2004

I have a lookup table called States, I have multiple other tables that use this lookup table, but I can only relate to one other table. My question is if I change a state name how do I enforce that change to the other tables that CANT be related? I know it is a design flaw and need some kind of joining table. I am having difficulty understanding and incorporating first normal form in my database. Here is what i have so far.

Example:

Table 1
========|============|=============|========|
CustomerID BillingAddressID ShippingAddressID OtherFields
========|============|=============|========|

Table 2
===========|======|========|
BillingAddressID StateID OtherFields
===========|======|========|

Table 3
=============|======|========|
ShippingAddressID StateID OtherFields
=============|======|========|

Table 4
======|====|
StateID Name
======|====|

How do I relate table 4 with table 2 & 3 for referential integrity? Or create a joiner table?

View 7 Replies View Related

Referential Integrity Constraints W/o Referential Integrity In The Db?

Mar 16, 2007

Using the new referential integrity constraints that will be made available, will it allow us to manually define the relationships between entities even if there is no true foreign key constraints setup in the database?

Lets say we deleted the FK_Orders_Customers in Northwind between orders and customers.

Or is this ability available now?

Thank for your time.

View 2 Replies View Related

Referential Integrity ?

Aug 11, 2001

Question:
What is used to enforce referential integrity ?
Answer: Triggers; Foriegn Keys.
what is Foreign Keys ?

View 1 Replies View Related

Referential Integrity

Oct 17, 2001

How do you enforce referential integrity in SQL between tables?

Thanks.

View 1 Replies View Related

I Have Referential Integrity. Now What

Feb 26, 2008

I used to work with little databases and throw everything into a table with no relation, and modify them all individually. Now I've learned about referential integrity, makes things a lot easier.

My question is, now what? Say I have a Customers table and an Orders table (to keep it simple). If custID is the primary key in customers, foreign key in orders, then if I want to insert into Orders, I need the custID. So those kinds of things I need to keep stored in a session state and insert them like that, correct?

View 1 Replies View Related

Referential Integrity

Oct 26, 2004

Hi guys,

Is there a way of finding the foreign keys for a table and therefore determine weather any referential integrity rules would be broken if a record was deleted?

For example. You have an author you want to delete, but that author has books. You call a procedure to delete the author. The stored procedure checks the foriegn keys, checks the tables and determines what is in them and if any tables have records that would be "orphaned" you return an error reporting what tables have the "would be orphaned" data in them.

It need to be fairly generic int he manner that it checks the foreign keys and the sub table data.

This is on behalf of a guy at work so "requirements" may change. ;)

Anyone got some ideas??

Cheers,
Shaun

View 5 Replies View Related

Referential Integrity

Feb 1, 2008

hi guys,
i was asked to drop some tables...but before dropping i was asked to check for referential integrity..
so where and how can i check this referential integrity????

View 2 Replies View Related

Referential Integrity

Dec 23, 2005

Hihow to set the referential integrity between 2 tables using enterprisemanager (microsoft SQL SERVER).. i tried and the tab doesn't allow meto choose. pls.help.thankssree--chavasreedharMessage posted via http://www.exforsys.com for all your training needs.

View 1 Replies View Related

Referential Integrity Done By Trigger

Nov 10, 1998

I'm not sure about the way you enforce the referential integrity in a SQL server 6.5 database (I'm new to this environment). Does any one have an exemple. The doc I have is useless...Thanks

View 1 Replies View Related

Referential Integrity Without Foreign Key

Apr 21, 2008

What ate the method/techniques i can use for enforcing referential integrity, Without using foreign key constraints ?

View 7 Replies View Related

Referential Integrity Problem

Jun 3, 2006

I'm using Microsoft SQL Server Management Studio Express 9.00.2047.00and expriencing problems with setting referential integrity on a linktable. The tables' schema is as follows:-------------------------------------------------------------------CREATE TABLE competencies (CID bigint identity(1,1) CONSTRAINT pk_CID PRIMARY KEY,LockedBy bigint DEFAULT 0 NOT NULLCONSTRAINT fk_UserIDREFERENCES usr_info(userID)ON DELETE SET DEFAULTON UPDATE CASCADE)---------------------------------------------------------CREATE TABLE usr_info (userID bigint IDENTITY(0,1) CONSTRAINT pk_UID PRIMARY KEY,ActiveFlag bit default 0 NOT NULL, --(1='Yes', 0='No')FirstName varchar(100) default '' NOT NULL,LastName varchar(100) default '' NOT NULL)-------------------------------------------------------CREATE TABLE competency_hdr (fkCID bigint default 0 NOT NULLCONSTRAINT fkCID_chREFERENCES competencies(CID)ON DELETE CASCADEON UPDATE CASCADE,ApprovedBy bigint default 0 NOT NULLCONSTRAINT fkUserID_chREFERENCES usr_info(userID)ON DELETE SET DEFAULT -- NO delete if user is deletedON UPDATE CASCADE)--------------------------------------------------------When I execute the above I get the following error message.Msg 1785, Level 16, State 0, Line 1Introducing FOREIGN KEY constraint 'fkUserID_ch' on table'competency_hdr' may cause cycles or multiple cascade paths. SpecifyON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGNKEY constraints.Msg 1750, Level 16, State 0, Line 1Could not create constraint. See previous errors.Now, if i swap the fields around then the error message changes tothat of the fkCID field.Basically what I want is:when I delete a competency record I need all references to this recordto be deleted.when I delete a user I want to set the foreign key to zero (the recordmust remain on the database).Obviously there is something I'm missing here. Any advice, anyone?---------------------------------------------------------------Join Bytes! : Remove your pants to reply---------------------------------------------------------------

View 6 Replies View Related

Referential Integrity (FK) Between Differents Databases

Aug 11, 2000

How I do, in SQL SERVER 7.0, referential integrity (Foreign Key constraints) between tables storaged in differents databases?.

Thank for any help.

View 1 Replies View Related

Referential Integrity Trigger---Urgent Pls

Oct 4, 2000

Hi,
I need to write a referential integrity trigger. I have two tables called Table1 and Table2. In both the tables i have common column called FieldA.
Now i need to write a trigger, whenever i updates Table1 this trigger automatically needs to update a FieldA in Table2.
Can anyone give me Code or suggestions , how to write this trigger. I am new to triggers.
Thank you!

---Ram

View 1 Replies View Related

Referential Integrity Trigger---Urgent Pls

Oct 4, 2000

Hi,
I need to write a referential integrity trigger. I have two tables called Table1 and Table2. In both the tables i have common column called FieldA.
Now i need to write a trigger, whenever i updates Table1 this trigger automatically needs to update a FieldA in Table2.
Can anyone give me Code or suggestions , how to write this trigger. I am new to triggers.
Thank you!
I know how to write a normal insert , update, delete triggers and using help from books online.. SO pls do it in useful.

---Ram

View 1 Replies View Related

Referential Integrity - Which Column Violates This?

Oct 4, 2004

Hi All,

I am inserting into a table that hold several foreign keys from several tables.
I'm performing this via a client (VB) and I only how to capture the error, but unable to determine which column/field is the one that violates referential integrity.

Any one can shed some light here? Many thanks!

Cyherus

View 7 Replies View Related

DELETING ROWS With REFERENTIAL INTEGRITY

Dec 7, 2006

hi there!

im having problems deleting rows in a reference table. is there any tools which tables to delete first before deleting the rows in the table which contains the primary key?

i have a lot of tables let say over 300 so its hard for me to guess which comes first... what should i keep in mind deleting rows with a referential integrity?

thank...

View 7 Replies View Related

Creating Referential Integrity Constraints

Aug 24, 2006

Two ways to do this... Creating the constraints when creating the data model OR using SQL to use the 'reference' constraint. Does 2005 provide any other automated method of creating the Primary Key - to Foreign key constraints without writing the SQL to do this?

Thx

View 9 Replies View Related

Problems With Procedural Referential Integrity

Nov 19, 2007

Hello Everyone.
I'm trying to set a procedural referential integrity on a table, files, which references to table users (a file is created by a user but one user can have more than one file).
Here, given that I can't create a referential integrity ON DELETE SET NULL (the reason il long to explain so just don't care about it), I would like to emulate the RI with a trigger.

On deleting a user the files associated to him have their IDUser set to null.
I have a problem, this is the trigger:


CREATE TRIGGER PRI_FilesOnUsers

ON Files

INSTEAD OF DELETE

AS

-- When a user is deleted the files must have no user associated, set IDUser to null

UPDATE Files

SET IDUser = NULL

WHERE IDUser = ?????????????????????????????????????????????????????

GO


WELL what do I heve to put there, how can i retrieve the values of the fields of the record that the user tried to delete?????Help me thanks

View 11 Replies View Related

Referential Integrity With Empty String And ZERO

Jul 7, 2007

Hi,
How could I define referential integrity using FK constraint which allow me to have empty string/ZERO number instead of NULL value ?

Thank you

View 1 Replies View Related

Problem Retaining Referential Integrity In My Data

Apr 12, 2007

I'm designing a database to store information about jobs that are in progress at a property. More than one job can be in progress at a property at one time and each different kind of job can contain different data, although they all share some common fields such as StartDate.

So I have a table that stores the property details PropertyDetails:

*ID
PropertyAddress
PropertyPostCode

Then I have a table that stores all of the jobs' shared details:

*PropertyID
*JobID - These three make up a compound primary key
*JobType
StartDate
EndDate

Then I have individual tables for each of the Jobs, for example BuildingWork:

*JobID
BuildingContractor
InsuranceCompany

Which works great, and enables me to query all basic job details from one table (JobDetails) rather than multiple tables for every job type.

BUT: I don't know how to enforce the referential integrity of the database. Obviously I can use a constraint to cascade deletes from the PropertyDetails table to the JobDetails table through the PropertyID, but there doesn't appear to then cascade the deletes from the JobDetails table to the individual Job tables as JobDetails has no idea what tables are there.

If I store the relevant individual table name as the JobType in the JobDetails table, could I use a trigger to somehow delete the related record from that table?

Littlecharva

View 7 Replies View Related

Disabling Referential Integrity Database-wide On Import

Apr 10, 2006

Is there a way to disable referential integrity on all destination tables for an import?
Thanks.

View 1 Replies View Related

How To Use Triggers To Enforce Cross-database Referential Integrity?

Aug 1, 2001

Can someone give me an example on how to enforce cross-database referential integrity with triggers in SQL Server 2000?

Thanks,
Joel

View 1 Replies View Related

SQL 2012 :: Server Referential Integrity Across Database - Schema?

Sep 19, 2014

In SQL Server 2012 (also 2005 and 2008), can you have Referential Integrity across a Database? Across a Schema?

View 1 Replies View Related

Linking Multiple Tables

Dec 8, 2014

URL...let's say every student in the left table, has a corresponding table to the right. If we were to take the average of all the classes in the corresponding table, how would you be able to transfer that in the table to the left, assuming there could be thousands of these individual tables.

View 4 Replies View Related

Linking Multiple Tables In An SQL Query

Jun 17, 2006

Hello,
I have a table called "ShoppingCart" which has fields for CartID, UniqueID and Quantity. At the moment I haven't specifed a primary key for this table becuase none of the fields are unique. I need to be able to link this ShoppingCart table to a table called "Size" using the UniqueID (both tables contain a field for UniqueID) in order to obtain the ProductID and CategoryID of the data item. I then need to link the ProductID and CategoryID I obtained from the first part of the SQL statement to a table called "plants" in order to get information from the following fields in the plants table: Common_Name, Latin_Name and Thumb_URL.
So far I've been able to use an INNER JOIN with the Size table to obtain the ProductID and Category ID using....
SELECT * FROM ShoppingCart INNER JOIN Size ON ShoppingCart.UniqueID = Size.UniqueID WHERE CartID = @CartID ORDER BY ProductID
I now need to be able to use the ProductID and Category ID I've obtained from this part of the query to reference the Common Name, Latin Name etc of the data item from the plants table. Is there any way that I could do this?
I do sometimes wonder if I'm making this whole thing a lot more complicated than it needs to be. At the moment I'm storing details about the different types of plants the garden centre sells in the "plants" table. However, some plants are available in different sizes so I've got a table called "Size" which links to the plants table using the ProductID and CategoryID of data items. The Size table has a unique primary key field called "UniqueID" which is used to uniquely identify every plant that the garden centre sells (this cannot be done using Product/CategoryID becuase different sizes of the same plant have the same ProductID/CategoryID). I'm then storing just the CartID, UniqueID and Quantity in the "ShoppingCart" table. There must be an easier way of structuring the whole thing!
I'd really appreciate any help you can offer me.
Many thanks,
Luke

View 1 Replies View Related

Multiple Tables, Inserts, Identity Columns And Database Integrity

Apr 30, 2008

Hi all,
I am writing a portion of an app that is of intensely high online eCommerce usage. I have a question about identity columns and locking or not.
What I am doing is, I have two tables (normalized), one is OrderDemographics(firstname,lastname,ccum,etc) the other is OrderItems. I have the primary key of OrderDemographics as a column called 'ID' (an Identity Integer that is incrementing). In the OrderItems table, the 'OrderID' column is a foreign key to the OrderDemographics Primary Key column 'ID'.
What I have previously done is to insert the demographics into OrderDemographics, then do a 'select top 1 ID from OrderDemographics order by ID DESC' to get that last ID, since you can't tell what it is until you add another row....
The problem is, there's up to 20,000 users/sessions at once and there is a possiblity that in the fraction of a second it takes to select back that ID integer and use it for the initial OrderItems row, some other user might have clicked 'order' a fraction of a second after the first user and created another row in OrderDemographics, thus incrementing the ID column and throwing all the items that Customer #1 orders into Customer #2's order....
How do I lock a SQL table or lock the Application in .NET to handle this problem and keep it from occurring?
Thanks, appreciate it.

View 2 Replies View Related

SQL - Cascading Delete, Or Delete Trigger, Maintaining Referential Integrity - PLEASE HELP ME!!!

Nov 13, 2006

I am having great difficulty with cascading deletes, delete triggers and referential integrity.

The database is in First Normal Form.

I have some tables that are child tables with two foreign keyes to two different parent tables, for example:

Table A
/
Table B Table C
/
Table D

So if I try to turn on cascading deletes for A/B, A/C, B/D and C/D relationships, I get an error that I cannot have cascading delete because it would create multiple cascade paths. I do understand why this is happening. If I delete a row in Table A, I want it to delete child rows in Table B and table C, and then child rows in table D as well. But if I delete a row in Table C, I want it to delete child rows in Table D, and if I delete a row in Table B, I want it to also delete child rows in Table D.

SQL sees this as cyclical, because if I delete a row in table A, both table B and table C would try to delete their child rows in table D.

Ok, so I thought, no biggie, I'll just use delete triggers. So I created delete triggers that will delete child rows in table B and table C when deleting a row in table A. Then I created triggers in both Table B and Table C that would delete child rows in Table D.

When I try to delete a row in table A, B or C, I get the error "Delete Statement Conflicted with COLUMN REFERENCE". This does not make sense to me, can anyone explain? I have a trigger in place that should be deleting the child rows before it attempts to delete the parent row...isn't that the whole point of delete triggers?????

This is an example of my delete trigger:

CREATE TRIGGER [DeleteA] ON A
FOR DELETE
AS
Delete from B where MeetingID = ID;
Delete from C where MeetingID = ID;

And then Table B and C both have delete triggers to delete child rows in table D. But it never gets to that point, none of the triggers execute because the above error happens first.

So if I then go into the relationships, and deselect the option for "Enforce relationship for INSERTs and UPDATEs" these triggers all work just fine. Only problem is that now I have no referential integrity and I can simply create unrestrained child rows that do not reference actual foreign keys in the parent table.

So the question is, how do I maintain referential integrity and also have the database delete child rows, keeping in mind that the cascading deletes will not work because of the multiple cascade paths (which are certainly required).

Hope this makes sense...
Thanks,
Josh


View 6 Replies View Related

Analysis :: Linking Multiple Fact Table At Different Granularity To Same Dimensions

Jul 15, 2015

I am modelling two fact tables of Actuals and Budget which are at different granularity, Actuals are at day, customer and product sub category level. Budgets are at month, Region and Product category level.

Month, Region and Product Category is present in Date, Region and Product Category dimension respectively. I have only three dimensions as Customer, Product and Date. Linking those dimensions to Actual Fact table is not an issue, what is the best way and options are there to link budget fact table to those three dimensions.

View 2 Replies View Related

Stored Procedure Not Inserting Into Linking Table Properly - Two Tables - Two Insert Statements

Dec 9, 2007

Hi can anyone help me with the format of my stored procedure below.
I have two tables (Publication and PublicationAuthors). PublicaitonAuthors is the linking table containing foreign keys PublicaitonID and AuthorID. Seeming as one Publication can have many authors associated with it, i need the stored procedure to create the a single row in the publication table and then recognise that multiple authors need to be inserted into the linking table for that single PublicationID. For this i have a listbox with multiple selection =true.
At the moment with the storedprocedure below it is creating two rows in PublicaitonID, and then inserting two rows into PublicationAuthors with only the first selected Author from the listbox??? Can anyone help???ALTER PROCEDURE dbo.StoredProcedureTest2
@publicationID Int=null,@typeID smallint=null,
@title nvarchar(MAX)=null,@authorID smallint=null
AS
BEGIN TRANSACTION
SET NOCOUNT ON
DECLARE @ERROR Int
--Create a new publication entry
INSERT INTO Publication (typeID, title)
VALUES (@typeID, @title)
--Obtain the ID of the created publication
SET @publicationID = @@IDENTITY
SET @ERROR = @@ERROR
--Create new entry in linking table PublicationAuthors
INSERT INTO PublicationAuthors (publicationID, authorID)
VALUES (@publicationID, @authorID)
SET @ERROR = @@ERROR
IF (@ERROR<>0)
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION

View 9 Replies View Related

Stored Procedure Not Inserting Into Linking Table Properly - Two Tables - Two Insert Statements

Dec 9, 2007

Hi can anyone help me with the format of my stored procedure below.
I have two tables (Publication and PublicationAuthors). PublicaitonAuthors is the linking table containing foreign keys PublicaitonID and AuthorID. Seeming as one Publication can have many authors associated with it, i need the stored procedure to create the a single row in the publication table and then recognise that multiple authors need to be inserted into the linking table for that single PublicationID. For this i have a listbox with multiple selection =true.
At the moment with the storedprocedure below it is creating two rows in PublicaitonID, and then inserting two rows into PublicationAuthors with only the first selected Author from the listbox??? Can anyone help???ALTER PROCEDURE dbo.StoredProcedureTest2
@publicationID Int=null,@typeID smallint=null,
@title nvarchar(MAX)=null,@authorID smallint=null
AS
BEGIN TRANSACTION
SET NOCOUNT ON
DECLARE @ERROR Int
--Create a new publication entry
INSERT INTO Publication (typeID, title)
VALUES (@typeID, @title)
--Obtain the ID of the created publication
SET @publicationID = @@IDENTITY
SET @ERROR = @@ERROR
--Create new entry in linking table PublicationAuthors
INSERT INTO PublicationAuthors (publicationID, authorID)
VALUES (@publicationID, @authorID)
SET @ERROR = @@ERROR
IF (@ERROR<>0)
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION

View 12 Replies View Related

Linking Tables From Different Databases Or Querying From Multiple Databases

Dec 10, 2007

Dear Readers,Is it possible, like in Access, to link to tables in other SQL databases that are on the same server? I have a query that I originally had in Access that queered from multiply databases. It did this by having those other tables in the other databases linked to the database that had the query. 
 

View 3 Replies View Related

Inserting Data Into Multiple Tables In 1 Transaction

Feb 7, 2007

Hello everyone, My web application uses SQL Server database and I am connecting via standard SqlConnection object and running stored procedures using SqlCommand object. In one of my page, I have data coming from 2 different tables. Now , data from 1 table comes as only single record. But from other table it comes as multiple records. Meaning, data that I read as 1 record, goes to different textbox and dropdown controls on page. Data that comes in multiple rows, I am binding that data with DataGrid. Now, in aspx page data from both table can be updated and on aspx page I only need to provide a single save button. Now, I am not sure how to save/insert/update a single row in 1 table and multiple rows in another table in 1 transaction. I thought of stored procedure. But I don't think its straightforward with stored procedures since table with multiple records, I am not sure how to pass all the records in stored procedure's arguments.Is there any way that I can control whole transaction in ASP .NET? Thanks,Ujjaval       

View 5 Replies View Related







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