If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > Column Update Based on Another Column in Same Table

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Jul 2011
Posts: 63
Column Update Based on Another Column in Same Table

Hi Everyone,

Is there a way to have a column update based on another column in the same table?

I have an [Employee] table with Emp_ID, Name, and Department columns.

I also have a [Department] table with Dept_ID, Dept_Name, and Manager_ID

The Manager_ID column uses the Emp_ID from the [Employee] table. Is there a way to add a Manager_Name column in the [Department] table that will update based on what I enter into the Manager_ID column?

I can't have the Manager_Name as a foreign key to the [Employee] table because there can be duplicate names. So right now I have the Manager_ID link to the Emp_ID. I can also just use an inner join but I am just curious to know if there is another solution.

Thank you all for your help!

Last edited by tkepongo; 07-12-11 at 11:52.
Reply With Quote
  #2 (permalink)  
Old
Annie's Dog Walker
 
Join Date: Nov 2004
Location: on the wrong server
Posts: 8,535
composite key. cascading updates?
__________________
“If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Jul 2011
Posts: 63
Quote:
Originally Posted by Thrasymachus View Post
composite key. cascading updates?
I get the 'There are no primary or candiate keys in the referenced table 'Employee' error. Here is my table structure. I apologize for my ignorance. I'm an intern without support from experienced people

CREATE TABLE Employee
(
Emp_ID smallint not null identity(1,1),
Full_Name varchar (50),
Dept_Name varchar(50) null,
UserName varchar(50) null,
OfficePhone varchar(50)
Primary key (Emp_ID, Full_Name)
)


CREATE TABLE Department
(
Dept_ID smallint identity(1,1),
Dept_Name varchar(50),
Dept_Manager smallint,
Manager_Name varchar (50)
Constraint pk_DepartmentID Primary Key (Dept_ID, Dept_name),
Unique (Dept_Name)
)

ALTER TABLE Department
add constraint fk_DeptManager
foreign key (Manager_Name) References tbl_Employee(Full_Name)ON UPDATE CASCADE ON DELETE CASCADE
Reply With Quote
  #4 (permalink)  
Old
Annie's Dog Walker
 
Join Date: Nov 2004
Location: on the wrong server
Posts: 8,535
try this. it might work...

ALTER TABLE Department
add constraint fk_DeptManager
foreign key (Dept_Manager,Manager_Name) References tbl_Employee(Emp_ID,Full_Name)ON UPDATE CASCADE ON DELETE CASCADE

I have never done cascades with composite keys. Not to my memory.
__________________
“If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Mar 2007
Location: Holmestrand, Norway
Posts: 332
I would NEVER recommend having a superkey as primary key. In this example,both dept_id and dept_name will be unique, there is no need to include both of them in the primary key, pick one of them, in this case I would recommend using dept_id.

Also, please keep in mind that a foreign key does not need to reference a primary key, it only need to reference a unique index. A primary key does always include a unique index.
__________________
Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Jul 2011
Posts: 63
Quote:
Originally Posted by Thrasymachus View Post
try this. it might work...

ALTER TABLE Department
add constraint fk_DeptManager
foreign key (Dept_Manager,Manager_Name) References tbl_Employee(Emp_ID,Full_Name)ON UPDATE CASCADE ON DELETE CASCADE

I have never done cascades with composite keys. Not to my memory.
It worked! Thank you!

However, when I insert data into the table, the columns still wont automatically update. For example, if I insert employee ID number '001' into the Dept_Manager column, I still need to manually type in the 'John Doe' into the Manager_Name column. Is there a way around this?

Thanks

Last edited by tkepongo; 07-12-11 at 15:45. Reason: Readability
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Jul 2011
Posts: 63
Quote:
Originally Posted by roac View Post
I would NEVER recommend having a superkey as primary key. In this example,both dept_id and dept_name will be unique, there is no need to include both of them in the primary key, pick one of them, in this case I would recommend using dept_id.

Also, please keep in mind that a foreign key does not need to reference a primary key, it only need to reference a unique index. A primary key does always include a unique index.
Can you elaborate on why you wouldn't use a superkey? The reason why I have a composite key is because having dept_id as the primary key would still allow me to add duplicate dept_name.

Thanks.
Reply With Quote
  #8 (permalink)  
Old
Annie's Dog Walker
 
Join Date: Nov 2004
Location: on the wrong server
Posts: 8,535
There is another thread here where the sql jihadists are fighting this out. It is naughty design that is not normalized and you are storing redundent data etc... blah... blah..., but it solves your little problem.

A kludge to your follow on issue can probably be fixed by an INSTEAD OF trigger.
__________________
“If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.
Reply With Quote
  #9 (permalink)  
Old
Annie's Dog Walker
 
Join Date: Nov 2004
Location: on the wrong server
Posts: 8,535
so let the haters hate...
__________________
“If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
Join Date: Mar 2007
Location: Holmestrand, Norway
Posts: 332
You can't have duplicate department names as long as you have a unique constraint on department name. As for your wish, even if you only use the identity column as primary key, you may have multiple departments with the same name (as long as you remove the unique constraint on dept_name).
__________________
Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
Join Date: Sep 2010
Posts: 144
Smile Trigger

tRIGEGR IS ALSO an othr option u have.. you can write after insert trigger on the first table.

create trigger <trigger_name> on table_name after insert
AS
insert into 2nd table(manager_name)
select i.emp_name inserted i;
Reply With Quote
  #12 (permalink)  
Old
Registered User
 
Join Date: Sep 2010
Posts: 144
i ll go for trigegr because wt if insertion takes place instead of updation. if you are inserting names in your table then would update cascade works? i don't think so.
Reply With Quote
  #13 (permalink)  
Old
Registered User
 
Join Date: Nov 2004
Posts: 1,377
This is the way I would model your tables and their relationship.
Code:
CREATE TABLE Employee
(
	Emp_ID		smallint	not null identity(1,1),
	Full_Name	varchar(50),
	Dept_ID		smallint	null,
	UserName	varchar(50)	null,
	OfficePhone	varchar(50)
	Constraint pk_Employee Primary key (Emp_ID)
)

CREATE TABLE Department
(
	Dept_ID		smallint	not null identity(1,1),
	Dept_Name	varchar(50),
	Manager_Emp_ID	smallint,
	Constraint pk_Department Primary Key (Dept_ID),
	Unique (Dept_Name)
)

ALTER TABLE Department
add constraint fk_DeptManager
	foreign key (Manager_Emp_ID) References Employee(Emp_ID)
Both Emp_ID and Dept_ID are IDENTITY columns and are defined as PK's. There is no need to add extra columns to get a superkey.
Employee.Dept_Name is replaced by Employee.Dept_ID that is a FK to Department.
Department.Dept_Manager is renamed to Department.Manager_Emp_ID to make clear it is a FK to Employee.
Department.Manager_Name is deleted, to get a normalised data model.
I removed the UPDATE and DELETE CASCADEs. I never like having those lurking in my databases. I prefer to receive an error when I try to DELETE or UPDATE a referenced record, to something that happens behind the curtains, biting me off guard at a moment when I least expect it. Besides, I couldn't think of any reason why someone would ever want to UPDATE the PK of a referenced record, when that PK is an IDENTITY column.

To get the data about an employee, including his/her department and the manager of that department, you would have to JOIN those tables. As an employee can exist without being assigned to a department, one needs to use a LEFT OUTER JOIN to JOIN the Employee with the Department table. And you need another LEFT OUTER JOIN to get the data of the manager of that department. This is quite some coding, so I would define a view that takes away the hassle.
Code:
CREATE VIEW V_Employee
AS
SELECT E.Emp_ID, E.Full_Name, E.UserName, E.OfficePhone, 
	D.Dept_ID, D.Dept_Name, 
	M.Full_Name as ManagerFullName, M.OfficePhone as ManagerPhone
FROM Employee as E
	LEFT OUTER JOIN Department as D ON
		E.Dept_ID = D.Dept_ID
	LEFT OUTER JOIN Employee as M ON
		D.Manager_Emp_ID = M.Emp_ID
Now you have a functional normalised data model. No need for CASCADEs or TRIGGERs or whatever you were forced to in the original data model. The only thing that was a bit complex were the LEFT OUTER JOINs, but those can be hidden by defining a VIEW.
__________________
With kind regards . . . . . SQL Server 2000/2005/2012
Wim

Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages
Reply With Quote
  #14 (permalink)  
Old
Registered User
 
Join Date: Jul 2011
Posts: 5
I sure wish I could 'Like' a post or give it karma or something. Wim...I totally agree. Reading through I was thinking that exact same layout.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On