Self Referential Database Table

May 21, 2007

Hello every body,

I need some clarification of concept regarding self referential
tables.

consider we have a "Person" table that stores data about employees of
an organisation.
in case a person change his name or any details we dont want to update
our database by loosing old information and adding new one. what we
need is to hold previous details as well as the new ones.
theoratically for me its easy i will just add another row and link
that row with one of the existing row. to do so i created a relation
between the "Emp_id" column of "Person" table to itself. so one
"Emp_id" could be related to another "Emp_id" in the same table.
after doing so i dont know how can i indicate while inserting a record
that this new record in linked with one of the previous records. means
in insert statement how the relation ship would be added.
here i am not sure if i am thinking in the right direction or not as
we may need to add another table or another column to indicate the
relation between old an new row. but if we have to add that new column
say "old_Emp_id" in "Person" table then what does the relationship
between "Emp_id" with itself serves.

thanks

Ali

View 4 Replies


ADVERTISEMENT

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

Can't Connect Remotely To WSUS Referential MSDE Database

Nov 9, 2006

Hi all,

WSUS is installed on my windows 2003 server. During the install, parameter DISABLENETWORKPROTOCOLS was not specified, and so no remote connection was possible.

I used SVRNETCN.exe to enable TCP/IP remote connection on port 1433. However it seems not to work.

FROM THE SERVER :

osql -E -S MYSERVERWSUS works fine but

osql -E -S 127.0.0.1WSUS doesn't work, i get the famous error

[DBNETLIB]Serveur SQL spécifié non trouvé.
[DBNETLIB]ConnectionOpen (Connect()).

If i user "osql -L" command, my instance doesn't appear in the list

FROM A CLIENT COMPUTER

osql -E -S MYSERVERWSUS and

osql -E -S MY_SERVER_IPWSUS don't work, i get the same error

I've tried tried to understand for hours, and i'm lost. I thought there may be a problem between SQL and 2003, and read SQL sp3a was required. I ran

select @@version from osql and got result

Microsoft SQL Server 2000 - 8.00.881 (Intel X86)

8.00.881 is not referenced in ms support as a patched version number !!!!!

Can anyone help me please ?



View 1 Replies View Related

Referential Integrity - Linking Multiple Tables To Transaction Table

Mar 3, 2006

I have transaction table where the rows entered into the transactioncan come a result of changes that take place if four different tables.So the situation is as follows:Transaction Table-TranId-Calc AmountTable 1 (the amount is inserted into the transaction table)- Tb1Id- Tb1AmtTable 2 (an amount is calculated based on the percentage and insertedinto the transaction table)-Tbl2Id-Tb2PercentageTable 3 (the amount is inserted into the transaction table)-Tbl3Id-Tbl3AmutTable 4 (an amount is calculated based on the percentage and insertedinto the transaction table. )-Tbl2Id-Tb2PercentageHow do I create referential integrity between the Transaction table andthe 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 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

Newbie Needs Referential Help.

Jan 9, 2007

I am new to MS SQL2005 but I have started working with it and found a difference between what MS Access and MS SQL will allow. I have a large DB with many tables but my problem relates to one specific issue. I have a table named tblAreaCodes which holds all area codes for the US. I have another table named tblSupplier where there are multiple colums such as name address etc. and three columns named phac, ph2ac, orderac, and faxac. All are the same type (Int) as is the Primary key in the tblAreaCode table. I have linked each of the columns in the tblSupplier table back to the primary in the tblAreaCode table. All is fine except I can not set the relationship to cascade update or delete. I am unsure what this means or how to proceed since this works and is allowed in MS Access. Any help and or direction would be greatly appreciated.

LKP

View 3 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 Constraint Vs Trigger

Jan 18, 2007

I am using SQL server 2000. I have a parent table 'MimeTypes' with primary key 'mimetype'. This is referenced as foreign key in table 'AssetTypes' as column 'BaseMimeType'.
The referential constraint is simple :
alter table AssetTypes
add constraint FK_ASSETTYPES_REF_BASEMIMETYPE foreign key (BaseMimeType)
references MimeTypes (MimeType) on update no action on delete no action;

I have FOR Update trigger on parent table ('MimeTypes') which is as follows:

CREATE TRIGGER trg_MimeTypes_update ON MimeTypes FOR UPDATE
AS
IF UPDATE(MimeType)
BEGIN
UPDATE AssetTypes
SET BaseMimeType = (select MimeType from inserted)
FROM AssetTypes as A
JOIN deleted as d on A.BaseMimeType = d.MimeType
END;

********
The problem is simple. I want to execute a update query on parent table ('MimeTypes') which modifies 'mimetype' value referenced by child. I dont want to include cascade option in constraint.
When such query is executed error comes as : UPDATE statement conflicted with COLUMN REFERENCE constraint 'FK_ASSETTYPES_REF_BASEMIMETYPE'. The conflict occurred in database 'default.db', table 'AssetTypes', column 'BaseMimeType'.
*****
Is there any way to do this?

View 5 Replies View Related

Multicolumn Primary Key && Referential Int.

Jan 10, 2004

I'm trying out this database layout under MS SQL (de), with primary multicolomn key, but ....


Let's say table A, has primary KEY on the field PK_A
Let's say table B, has primary KEY on the field PK_B

Now table C, has fields ID_A and ID_B, defined as multicolomn primary key, referencing obviuosly to the PK_A and PK_B values.



NOW THE PROBLEM !! :(

Let present you table D , with his beautiful field ID_C. :)
How can I succed in referencing with the ID_C field the primary multicolomn key of table C ??

I' designing the whole thing with access97+mde, but dragging the PK key of table C (ID_A & ID_B) over the table D (ID_C) doesn't work !!

Any help ??

Thanks in advance !!

H2O

View 5 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

How To Delete A Record Which Is Referential To Oth

Jun 6, 2008

Hi, I tried to delete a record in tblA which has an ID in tblB, i got error msg saying referential problem, please tell me what should i do? thanks.

View 2 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 Constraint Between Two Tables In Two Databases

Dec 7, 2007

Is it possible to define a referential constraint between two tables in two different databases (on two servers)? Or are there beter best practices methods/products to achieve this result.

View 4 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

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

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

Check Referential Ingerity Or Catch Error

Oct 6, 2004

Hi all,

Just wondering what would be the normal or more efficient practice to insert/update a record.

1. Check for existence of primary record (using SELECT in stored procedure)
2. Capture error and handling

My problem is that I try to execute an stored procedure from a VB client.. but unable to capture the errors in SP, it just prompts the error and thats it, not responding to my "SELECT @err = @@ERROR" after the insert statement.

so now, I'm thinking of capturing the error on the client (which I am able to do) and handle it from there.. or to make sure that RI is enforced by 'searching' for Pks in the primary tables before executing the INSERT statement in my stored procedure.

Any advise would be appreciated..

Cyherus

View 2 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

Converting Multi-column Referential Constraint Into A Trigger

Feb 28, 2008

Hi there,

I am looking for a way to define a trigger that is a replacement for a multi-column foreign key.

I know how to a convert a single-column foreign key constraint into a trigger (i.e., to resolve diamond-structured references).

CREATE TABLE parent_tab
(
col_a INTEGER NOT NULL,
CONSTRAINT pk PRIMARY KEY(col_a)
);

CREATE TABLE child_tab
(
col_x INTEGER NOT NULL,
CONSTRAINT fk FOREIGN KEY (col_x) REFERENCES parent_tab(col_a) ON DELETE CASCADE
);

The conversion would remove the foreign key definition and add this trigger:

CREATE TRIGGER tr_single
ON parent_tab INSTEAD OF DELETE
AS BEGIN
DELETE FROM child_tab WHERE (child_tab.col_x IN (SELECT col_a FROM deleted))
DELETE FROM parent_tab WHERE (parent_tab.col_a IN (SELECT col_a FROM deleted))
END;

Unfortunately, now I need to resolve a situation where there is involved a multi-column foreign key.

CREATE TABLE parent_tab
(
col_a INTEGER NOT NULL,
col_b INTEGER NOT NULL,
CONSTRAINT pk PRIMARY KEY(col_a, col_b)
);

CREATE TABLE child_tab
(
col_x INTEGER NOT NULL,
col_y INTEGER NOT NULL,
CONSTRAINT fk FOREIGN KEY (col_x, col_y) REFERENCES parent_tab(col_a, col_b) ON DELETE CASCADE
);

This does not work, because the temporary table "deleted" might contain more than one row. How do I make sure that the values belong to the same row?

-- incorrect trigger, might delete too many rows
CREATE TRIGGER tr_single
ON parent_tab INSTEAD OF DELETE
AS BEGIN
DELETE FROM child_tab WHERE (child_tab.col_x IN (SELECT col_a FROM deleted) AND child_tab.col_y IN (SELECT col_b FROM deleted))
DELETE FROM parent_tab WHERE (parent_tab.col_a IN (SELECT col_a FROM deleted) AND parent_tab.col_b IN (SELECT col_b FROM deleted))
END;

-- some magic needed :-)
CREATE TRIGGER tr_single
ON parent_tab INSTEAD OF DELETE
AS BEGIN
DELETE FROM child_tab WHERE (child_tab.col_x IN (SELECT col_a FROM deleted AS t1) AND child_tab.col_y IN (SELECT col_b FROM deleted AS t2) AND row_id(t1) = row_id(t2))
DELETE FROM parent_tab WHERE (parent_tab.col_a IN (SELECT col_a FROM deleted AS t1) AND parent_tab.col_b IN (SELECT col_b FROM deleted AS t2) AND row_id(t1) = row_id(t2))
END;

I know the trigger definition above is ***... but I hope that it helps to make clear what I need.

Btw., I use SQL Server 2005.

Thanks in advance,

slowjoe

View 3 Replies View Related







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