Dear MySQL developers,
Could some experienced Database developer please take a look at this ?
It is supposed to be plain SQL2.
How can it be coded under MySQL
Especially all referential triggered actions.
According to the manual :
"7.5.5.2 Foreign Key Constraints
Starting from version 3.23.43b InnoDB features foreign key constraints.
InnoDB is the first MySQL table type which allows you to define foreign
key constraints to guard the integrity of your data."
Yours Sincerely
Morten Gulbrandsen
===
--------------
DROP TABLE IF EXISTS EMPLOYEE
--------------
Query OK, 0 rows affected (0.01 sec)
--------------
CREATE TABLE EMPLOYEE
(
FNAME VARCHAR(15) NOT NULL,
MINIT CHAR,
LNAME VARCHAR(15) NOT NULL,
SSN CHAR(9) NOT NULL,
BDATE DATE,
ADDRESS VARCHAR(30),
SEX CHAR,
SALARY DECIMAL(10,2),
SUPERSSN CHAR(9),
DNO INT NOT NULL DEFAULT 1,
CONSTRAINT EMPPK
PRIMARY KEY (SSN),
CONSTRAINT EMPSUPERFK
FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(SSN)
ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT EMPDEPTFK
FOREIGN KEY (DNO) REFERENCES DEPARTMENT(DNUMBER)
ON DELETE SET DEFAULT ON UPDATE CASCADE) TYPE = InnoDB
--------------
Query OK, 0 rows affected (0.00 sec)
--------------
DROP TABLE IF EXISTS DEPARTMENT
--------------
Query OK, 0 rows affected (0.00 sec)
--------------
CREATE TABLE DEPARTMENT
(
DNAME VARCHAR(15) NOT NULL,
DNUMBER INT NOT NULL,
MGRSSN CHAR(9) NOT NULL DEFAULT '888665555',
MGRSTARTDATE DATE,
CONSTRAINT DEPTPK
PRIMARY KEY (DNUMBER),
CONSTRAINT DEPTSK
UNIQUE (DNAME),
CONSTRAINT DEPTMGRFK
FOREIGN KEY (MGRSSN) REFERENCES EMPLOYEE(SSN)
ON DELETE SET DEFAULT ON UPDATE CASCADE ) TYPE = InnoDB
--------------
Query OK, 0 rows affected (0.00 sec)
--------------
DROP TABLE IF EXISTS DEPT_LOCATIONS
--------------
Query OK, 0 rows affected (0.00 sec)
--------------
CREATE TABLE DEPT_LOCATIONS
(
DNUMBER INT NOT NULL,
DLOCATION VARCHAR(15) NOT NULL,
PRIMARY KEY (DNUMBER, DLOCATION),
FOREIGN KEY (DNUMBER) REFERENCES DEPARTMENT(DNUMBER)
ON DELETE CASCADE ON UPDATE CASCADE ) TYPE = InnoDB
--------------
Query OK, 0 rows affected (0.00 sec)
--------------
DROP TABLE IF EXISTS PROJECT
--------------
Query OK, 0 rows affected (0.00 sec)
--------------
CREATE TABLE PROJECT
(
PNAME VARCHAR(15) NOT NULL,
PNUMBER INT NOT NULL,
PLOCATION varchar(15),
DNUM int NOT NULL,
PRIMARY KEY (PNUMBER),
UNIQUE (PNAME),
FOREIGN KEY (DNUM) REFERENCES DEPARTMENT (DNUMBER)) TYPE = InnoDB
--------------
Query OK, 0 rows affected (0.02 sec)
--------------
DROP TABLE IF EXISTS WORKS_ON
--------------
Query OK, 0 rows affected (0.00 sec)
--------------
CREATE TABLE WORKS_ON
(
ESSN CHAR(9) NOT NULL,
PNO INT NOT NULL,
HOURS DECIMAL(3,1) NOT NULL,
PRIMARY KEY (ESSN, PNO),
FOREIGN KEY (ESSN) REFERENCES EMPLOYEE(SSN),
FOREIGN KEY (PNO) REFERENCES PROJECT (PNUMBER))TYPE = InnoDB
--------------
Query OK, 0 rows affected (0.00 sec)
--------------
DROP TABLE IF EXISTS DEPENDENT
--------------
Query OK, 0 rows affected (0.00 sec)
--------------
CREATE TABLE DEPENDENT
(
ESSN CHAR(9) NOT NULL,
DEPENDENT_NAME VARCHAR(15) NOT NULL,
SEX CHAR,
BDATE DATE,
RELATIONSHIP VARCHAR(8),
PRIMARY KEY (ESSN, DEPENDENT_NAME),
FOREIGN KEY (ESSN) REFERENCES EMPLOYEE(SSN))TYPE = InnoDB
--------------
Query OK, 0 rows affected (0.00 sec)
--------------
SHOW CREATE TABLE EMPLOYEE
--------------
+----------+------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
--------------+
| Table | Create Table
|
+----------+------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
--------------+
| EMPLOYEE | CREATE TABLE `employee` (
`FNAME` varchar(15) NOT NULL default '',
`MINIT` char(1) default NULL,
`LNAME` varchar(15) NOT NULL default '',
`SSN` varchar(9) NOT NULL default '',
`BDATE` date default NULL,
`ADDRESS` varchar(30) default NULL,
`SEX` char(1) default NULL,
`SALARY` decimal(10,2) default NULL,
`SUPERSSN` varchar(9) default NULL,
`DNO` int(11) NOT NULL default '1',
PRIMARY KEY (`SSN`)
) TYPE=MyISAM |
+----------+------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
--------------+
1 row in set (0.00 sec)
--------------
SHOW CREATE TABLE DEPARTMENT
--------------
+------------+----------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
-+
| Table | Create Table
|
+------------+----------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
-+
| DEPARTMENT | CREATE TABLE `department` (
`DNAME` varchar(15) NOT NULL default '',
`DNUMBER` int(11) NOT NULL default '0',
`MGRSSN` varchar(9) NOT NULL default '888665555',
`MGRSTARTDATE` date default NULL,
PRIMARY KEY (`DNUMBER`),
UNIQUE KEY `DNAME` (`DNAME`)
) TYPE=MyISAM |
+------------+----------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
-+
1 row in set (0.00 sec)
--------------
SHOW CREATE TABLE DEPT_LOCATIONS
--------------
+----------------+------------------------------------------------------
------------------------------------------------------------------------
-------------------------------------------------+
| Table | Create Table
|
+----------------+------------------------------------------------------
------------------------------------------------------------------------
-------------------------------------------------+
| DEPT_LOCATIONS | CREATE TABLE `dept_locations` (
`DNUMBER` int(11) NOT NULL default '0',
`DLOCATION` varchar(15) NOT NULL default '',
PRIMARY KEY (`DNUMBER`,`DLOCATION`)
) TYPE=MyISAM |
+----------------+------------------------------------------------------
------------------------------------------------------------------------
-------------------------------------------------+
1 row in set (0.00 sec)
--------------
SHOW CREATE TABLE PROJECT
--------------
+---------+-------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
----------------------------------------------------------+
| Table | Create Table
|
+---------+-------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
----------------------------------------------------------+
| PROJECT | CREATE TABLE `project` (
`PNAME` varchar(15) NOT NULL default '',
`PNUMBER` int(11) NOT NULL default '0',
`PLOCATION` varchar(15) default NULL,
`DNUM` int(11) NOT NULL default '0',
PRIMARY KEY (`PNUMBER`),
UNIQUE KEY `PNAME` (`PNAME`)
) TYPE=MyISAM |
+---------+-------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
----------------------------------------------------------+
1 row in set (0.02 sec)
--------------
SHOW CREATE TABLE WORKS_ON
--------------
+----------+------------------------------------------------------------
------------------------------------------------------------------------
--------------------------------------------------------------+
| Table | Create Table
|
+----------+------------------------------------------------------------
------------------------------------------------------------------------
--------------------------------------------------------------+
| WORKS_ON | CREATE TABLE `works_on` (
`ESSN` char(9) NOT NULL default '',
`PNO` int(11) NOT NULL default '0',
`HOURS` decimal(3,1) NOT NULL default '0.0',
PRIMARY KEY (`ESSN`,`PNO`)
) TYPE=MyISAM |
+----------+------------------------------------------------------------
------------------------------------------------------------------------
--------------------------------------------------------------+
1 row in set (0.00 sec)
--------------
SHOW CREATE TABLE DEPENDENT
--------------
+-----------+-----------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
--+
| Table | Create Table
|
+-----------+-----------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
--+
| DEPENDENT | CREATE TABLE `dependent` (
`ESSN` varchar(9) NOT NULL default '',
`DEPENDENT_NAME` varchar(15) NOT NULL default '',
`SEX` char(1) default NULL,
`BDATE` date default NULL,
`RELATIONSHIP` varchar(8) default NULL,
PRIMARY KEY (`ESSN`,`DEPENDENT_NAME`)
) TYPE=MyISAM |
+-----------+-----------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
--+
1 row in set (0.00 sec)
--------------
SHOW TABLE STATUS FROM company LIKE "EMPLOYEE"
--------------
+----------+--------+------------+------+----------------+-------------+
-----------------+--------------+-----------+----------------+----------
-----------+---------------------+------------+----------------+--------
-+
| Name | Type | Row_format | Rows | Avg_row_length | Data_length |
Max_data_length | Index_length | Data_free | Auto_increment |
Create_time | Update_time | Check_time | Create_options
| Comment |
+----------+--------+------------+------+----------------+-------------+
-----------------+--------------+-----------+----------------+----------
-----------+---------------------+------------+----------------+--------
-+
| employee | MyISAM | Dynamic | 0 | 0 | 0 |
4294967295 | 1024 | 0 | NULL | 2003-07-31
14:37:48 | 2003-07-31 14:37:48 | NULL | | |
+----------+--------+------------+------+----------------+-------------+
-----------------+--------------+-----------+----------------+----------
-----------+---------------------+------------+----------------+--------
-+
1 row in set (0.00 sec)
--------------
SHOW TABLE STATUS FROM company LIKE "DEPARTMENT"
--------------
+------------+--------+------------+------+----------------+------------
-+-----------------+--------------+-----------+----------------+--------
-------------+---------------------+------------+----------------+------
---+
| Name | Type | Row_format | Rows | Avg_row_length | Data_length
| Max_data_length | Index_length | Data_free | Auto_increment |
Create_time | Update_time | Check_time | Create_options
| Comment |
+------------+--------+------------+------+----------------+------------
-+-----------------+--------------+-----------+----------------+--------
-------------+---------------------+------------+----------------+------
---+
| department | MyISAM | Dynamic | 0 | 0 | 0
| 4294967295 | 1024 | 0 | NULL |
2003-07-31 14:37:48 | 2003-07-31 14:37:48 | NULL |
| |
+------------+--------+------------+------+----------------+------------
-+-----------------+--------------+-----------+----------------+--------
-------------+---------------------+------------+----------------+------
---+
1 row in set (0.00 sec)
--------------
SHOW TABLE STATUS FROM company LIKE "DEPT_LOCATIONS"
--------------
+----------------+--------+------------+------+----------------+--------
-----+-----------------+--------------+-----------+----------------+----
-----------------+---------------------+------------+----------------+--
-------+
| Name | Type | Row_format | Rows | Avg_row_length |
Data_length | Max_data_length | Index_length | Data_free |
Auto_increment | Create_time | Update_time | Check_time
| Create_options | Comment |
+----------------+--------+------------+------+----------------+--------
-----+-----------------+--------------+-----------+----------------+----
-----------------+---------------------+------------+----------------+--
-------+
| dept_locations | MyISAM | Dynamic | 0 | 0 |
0 | 4294967295 | 1024 | 0 | NULL |
2003-07-31 14:37:48 | 2003-07-31 14:37:48 | NULL |
| |
+----------------+--------+------------+------+----------------+--------
-----+-----------------+--------------+-----------+----------------+----
-----------------+---------------------+------------+----------------+--
-------+
1 row in set (0.00 sec)
--------------
SHOW TABLE STATUS FROM company LIKE "PROJECT"
--------------
+---------+--------+------------+------+----------------+-------------+-
----------------+--------------+-----------+----------------+-----------
----------+---------------------+------------+----------------+---------
+
| Name | Type | Row_format | Rows | Avg_row_length | Data_length |
Max_data_length | Index_length | Data_free | Auto_increment |
Create_time | Update_time | Check_time | Create_options
| Comment |
+---------+--------+------------+------+----------------+-------------+-
----------------+--------------+-----------+----------------+-----------
----------+---------------------+------------+----------------+---------
+
| project | MyISAM | Dynamic | 0 | 0 | 0 |
4294967295 | 1024 | 0 | NULL | 2003-07-31
14:37:48 | 2003-07-31 14:37:48 | NULL | | |
+---------+--------+------------+------+----------------+-------------+-
----------------+--------------+-----------+----------------+-----------
----------+---------------------+------------+----------------+---------
+
1 row in set (0.00 sec)
--------------
SHOW TABLE STATUS FROM company LIKE "WORKS_ON"
--------------
+----------+--------+------------+------+----------------+-------------+
-----------------+--------------+-----------+----------------+----------
-----------+---------------------+------------+----------------+--------
-+
| Name | Type | Row_format | Rows | Avg_row_length | Data_length |
Max_data_length | Index_length | Data_free | Auto_increment |
Create_time | Update_time | Check_time | Create_options
| Comment |
+----------+--------+------------+------+----------------+-------------+
-----------------+--------------+-----------+----------------+----------
-----------+---------------------+------------+----------------+--------
-+
| works_on | MyISAM | Fixed | 0 | 0 | 0 |
81604378623 | 1024 | 0 | NULL | 2003-07-31
14:37:48 | 2003-07-31 14:37:48 | NULL | | |
+----------+--------+------------+------+----------------+-------------+
-----------------+--------------+-----------+----------------+----------
-----------+---------------------+------------+----------------+--------
-+
1 row in set (0.00 sec)
--------------
SHOW TABLE STATUS FROM company LIKE "DEPENDENT"
--------------
+-----------+--------+------------+------+----------------+-------------
+-----------------+--------------+-----------+----------------+---------
------------+---------------------+------------+----------------+-------
--+
| Name | Type | Row_format | Rows | Avg_row_length | Data_length
| Max_data_length | Index_length | Data_free | Auto_increment |
Create_time | Update_time | Check_time | Create_options
| Comment |
+-----------+--------+------------+------+----------------+-------------
+-----------------+--------------+-----------+----------------+---------
------------+---------------------+------------+----------------+-------
--+
| dependent | MyISAM | Dynamic | 0 | 0 | 0
| 4294967295 | 1024 | 0 | NULL |
2003-07-31 14:37:48 | 2003-07-31 14:37:48 | NULL |
| |
+-----------+--------+------------+------+----------------+-------------
+-----------------+--------------+-----------+----------------+---------
------------+---------------------+------------+----------------+-------
--+
1 row in set (0.00 sec)
Bye
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/my***********...ie.nctu.edu.tw
