Self Referencing foreign key

[lyonnyte]

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 ;

[Giuseppe Maxia]

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 CHARSET=latin1


ciao
gmax

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

[lyonnyte]

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

[Bill Karwin]

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]

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.

[lyonnyte]

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

[Bill Karwin]

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.