Hi all,
I have two tables that look like this:
 | | |
| Code: mysql> describe unit; +-------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+-------+ | unit_number | varchar(7) | NO | PRI | NULL | | | BexamDate | varchar(10) | YES | | NULL | | | C6examDate | varchar(10) | YES | | NULL | | | C4examDate | varchar(10) | YES | | NULL | | +-------------+-------------+------+-----+---------+-------+ mysql> describe defect; +-------------+---------------+------+-----+---------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +-------------+---------------+------+-----+---------------------+-----------------------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | defect | varchar(1000) | NO | | NULL | | | estimated | varchar(8) | YES | | NULL | | | date_closed | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | | date_opened | timestamp | NO | | 0000-00-00 00:00:00 | | | comment | varchar(2000) | YES | | NULL | | | unit_number | varchar(7) | NO | | NULL | | +-------------+---------------+------+-----+---------------------+-----------------------------+ | |
| | |
In the defect table, unit_number should be a foreign key to the primary key of the unit table.
My SQL looks like this:
| | |
| Code: CREATE TABLE unit( unit_number VARCHAR(7) NOT NULL, BexamDate VARCHAR(10), C6examDate VARCHAR(10), C4examDate VARCHAR(10), PRIMARY KEY(unit_number) ); CREATE TABLE defect( id INT NOT NULL AUTO_INCREMENT, defect VARCHAR(1000) NOT NULL, estimated VARCHAR(8), date_closed TIMESTAMP, date_opened TIMESTAMP, comment VARCHAR(2000), unit_number VARCHAR(7) NOT NULL REFERENCES unit(unit_number), PRIMARY KEY(id) ); | |
| | |
My questions are:
1) Is the SQL correct?
2) When I am doing an insert, how do I make the reference?
Many thanks,
Ben