Results 1 to 14 of 14
  1. #1
    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.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,558
    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.

  3. #3
    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

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,558
    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.

  5. #5
    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

  6. #6
    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

  7. #7
    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.

  8. #8
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,558
    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.

  9. #9
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,558
    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.

  10. #10
    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

  11. #11
    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;

  12. #12
    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.

  13. #13
    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

  14. #14
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •