Professional Web Applications Themes

Self Referencing foreign key - MySQL

I am trying to create an employee/supervisor relationship. Since both are essentially employees, I created a foreign key relationship which referenced itself. I was wondering if there is a better way to do this? Maybe without having to create the first record? CREATE TABLE employee ( employeeid longint(20) unsigned NOT NULL auto_increment, employeefirstname varchar(20) NOT NULL, employeelastname varchar(20) NOT NULL, employeetitle varchar(10) , supervisorid longint(20) unsigned, primary key(employeeid), ); INSERT INTO employee VALUES ('1', 'Chief', 'Officer', 'CEO', ' '); ALTER TABLE 'employee' ADD FOREIGN KEY(supervisorid) REFERENCES 'employee'('employeeid') ON DELETE CASCADE ;...

  1. #1

    Default Self Referencing foreign key

    I am trying to create an employee/supervisor relationship. Since both
    are essentially employees, I created a foreign key relationship which
    referenced itself. I was wondering if there is a better way to do
    this? Maybe without having to create the first record?

    CREATE TABLE employee
    (
    employeeid longint(20) unsigned NOT NULL auto_increment,
    employeefirstname varchar(20) NOT NULL,
    employeelastname varchar(20) NOT NULL,
    employeetitle varchar(10) ,
    supervisorid longint(20) unsigned,
    primary key(employeeid),
    );
    INSERT INTO employee VALUES
    ('1', 'Chief', 'Officer', 'CEO', ' ');

    ALTER TABLE 'employee' ADD FOREIGN KEY(supervisorid) REFERENCES
    'employee'('employeeid') ON DELETE CASCADE ;

    lyonnyte Guest

  2. #2

    Default Re: Self Referencing foreign key

    lyonnyte wrote:
    > I am trying to create an employee/supervisor relationship. Since both
    > are essentially employees, I created a foreign key relationship which
    > referenced itself. I was wondering if there is a better way to do
    > this? Maybe without having to create the first record?
    >
    > CREATE TABLE employee
    > (
    > employeeid longint(20) unsigned NOT NULL auto_increment,
    > employeefirstname varchar(20) NOT NULL,
    > employeelastname varchar(20) NOT NULL,
    > employeetitle varchar(10) ,
    > supervisorid longint(20) unsigned,
    > primary key(employeeid),
    > );
    > INSERT INTO employee VALUES
    > ('1', 'Chief', 'Officer', 'CEO', ' ');
    >
    > ALTER TABLE 'employee' ADD FOREIGN KEY(supervisorid) REFERENCES
    > 'employee'('employeeid') ON DELETE CASCADE ;
    >
    You can do that in one pass:

    CREATE TABLE employee (
    employeeid int(10) unsigned NOT NULL auto_increment,
    employeefirstname varchar(20) NOT NULL,
    employeelastname varchar(20) NOT NULL,
    employeetitle varchar(10) default NULL,
    supervisorid int(10) unsigned default NULL,
    PRIMARY KEY (employeeid),
    FOREIGN KEY (supervisorid) REFERENCES employee (employeeid) ON DELETE CASCADE
    ) ENGINE=InnoDB

    The engine will do the rest:
    show create table employee\G
    *************************** 1. row ***************************
    Table: employee
    Create Table: CREATE TABLE `employee` (
    `employeeid` int(10) unsigned NOT NULL auto_increment,
    `employeefirstname` varchar(20) NOT NULL,
    `employeelastname` varchar(20) NOT NULL,
    `employeetitle` varchar(10) default NULL,
    `supervisorid` int(10) unsigned default NULL,
    PRIMARY KEY (`employeeid`),
    KEY `supervisorid` (`supervisorid`),
    CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`supervisorid`) REFERENCES `employee` (`employeeid`) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHT=latin1


    ciao
    gmax

    --
    _ _ _ _
    (_|| | |(_|>< The Data Charmer
    _|
    [url]http://datacharmer.blogspot.com/[/url]
    Giuseppe Maxia Guest

  3. #3

    Default Re: Self Referencing foreign key

    I'm a newbie in mysql and was wondering whether the engine came with
    the intial install or do I have to d/l it? Also, is the same engine
    available in Oracle's SQL ?

    Thanks

    lyonnyte Guest

  4. #4

    Default Re: Self Referencing foreign key

    Giuseppe Maxia wrote:
    > CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`supervisorid`) REFERENCES `employee` (`employeeid`) ON DELETE CASCADE
    So when a supervisor leaves the company, all his direct reports are laid
    off? :-)

    I'd rather use ON DELETE SET NULL (actually, I'd prefer ON DELETE SET
    DEFAULT, but MySQL doesn't support that).

    Regards,
    Bill K.
    Bill Karwin Guest

  5. #5

    Default Re: Self Referencing foreign key

    lyonnyte wrote:
    > I'm a newbie in mysql and was wondering whether the engine came with
    > the intial install or do I have to d/l it?
    Typically the MySQL engine (the server) is part of the product.

    The exception seems to be Linux, where you can download different
    subsets of the product separately.
    > Also, is the same engine available in Oracle's SQL ?
    No, Oracle and MySQL are different products, even though they both
    recognize a large part of the SQL language in common.

    Regards,
    Bill K.
    Bill Karwin Guest

  6. #6

    Default Re: Self Referencing foreign key


    Bill Karwin wrote:
    > Giuseppe Maxia wrote:
    > > CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`supervisorid`) REFERENCES `employee` (`employeeid`) ON DELETE CASCADE
    >
    > So when a supervisor leaves the company, all his direct reports are laid
    > off? :-)
    >
    > I'd rather use ON DELETE SET NULL (actually, I'd prefer ON DELETE SET
    > DEFAULT, but MySQL doesn't support that).
    >
    > Regards,
    > Bill K
    Thanks for catching that, would've really messed up the database if
    someone hadn't seen it.
    Wouldn't ON DELETE SET DEFAULT be what normally happens if you don't
    put on ON DELETE CASCADE? .

    lyonnyte Guest

  7. #7

    Default Re: Self Referencing foreign key

    lyonnyte wrote:
    > Wouldn't ON DELETE SET DEFAULT be what normally happens if you don't
    > put on ON DELETE CASCADE? .
    Nope; in MySQL, ON DELETE RESTRICT and ON DELETE NO ACTION are
    equivalent to omitting the ON DELETE clause.

    See
    [url]http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html[/url]
    for more information on this.

    Regards,
    Bill K.
    Bill Karwin Guest

  8. #8

    Default Re: Self Referencing foreign key

    Self Referencing foreign key
    [URL=http://www.livetvizle.com]Tv izle[/URL]
    [URL=http://www.livetvizle.com/trhepsi/showtv_canli_izle.asp]Show Tv izle[/URL]
    [URL=http://www.livetvizle.com/tnt-tv-izle.asp]TNT izle[/URL]
    remixindir is offline Junior Member
    Join Date
    Jan 2012
    Location
    Türkiye
    Posts
    2

Similar Threads

  1. Referencing the Table Name
    By Dan J. in forum MySQL
    Replies: 1
    Last Post: March 4th, 09:00 AM
  2. Referencing web service
    By John in forum ASP.NET Web Services
    Replies: 4
    Last Post: November 16th, 01:18 AM
  3. Referencing Objects using 'This'
    By 0Visual in forum Macromedia Flash Actionscript
    Replies: 4
    Last Post: March 5th, 07:39 PM
  4. referencing problem, or so i think
    By oiv in forum Macromedia Flash
    Replies: 0
    Last Post: November 18th, 12:16 PM
  5. Picture referencing???
    By Ant in forum Microsoft Access
    Replies: 1
    Last Post: July 16th, 08:25 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139