Sql create table using cascade
has average rating
8
out of 10.
Total 18 users rated.
Description
The CASCADE option with ON DELETE allows to delete rows from the child table when the corresponding rows are deleted from the parent table.
The DELETE CASCADE works across a foreign key link and removes the child records associated with the parent records.
Example
To create a table which contains the following field name and data types -
| Field Name | Data Type | Size | Decimal Places | NULL | Constraint |
|---|---|---|---|---|---|
| tranno | decimal | No | |||
| company_id | varchar | 6 | Yes | FOREIGN KEY | |
| itemcode | varchar | 10 | Yes | PRIMARY KEY | |
| coname | varchar | 35 | Yes | ||
| itemname | varchar | 35 | Yes | ||
| iqty | integer | Yes |
The table contains a PRIMARY KEY on 'itemcode' and a FOREIGN KEY on 'company_id' column which references to the 'company_id' column of 'company' table.
the following sql statement can be used :
CREATE TABLE mytest( tranno decimal NOT NULL, company_id varchar(6), itemcode varchar(10), coname varchar(35), itemname varchar(35), iqty integer, PRIMARY KEY(itemcode), FOREIGN KEY(company_id) REFERENCES company (company_id) ON DELETE CASCADE);
To see the structure of the created table :
DESCRIBE mytest;
Output
Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition.
