{"id":344,"date":"2012-10-11T20:42:41","date_gmt":"2012-10-11T20:42:41","guid":{"rendered":"http:\/\/62.131.51.129\/wordpress\/?p=344"},"modified":"2012-10-11T20:42:41","modified_gmt":"2012-10-11T20:42:41","slug":"the-foreign-key-in-oracle-xe","status":"publish","type":"post","link":"http:\/\/archief.van-maanen.com\/?p=344","title":{"rendered":"The foreign key in Oracle XE"},"content":{"rendered":"

Oracle has introduced a small version of its DBMS that is labelled “XE”. This small version can be downloaded and used for free upon the condition that its usage is for personal study only.
\nI really like this application: it has a relative small footprint; it runs on a small machine and it allows you to learn Oracle in your own environment.
\nIt is possible to play with the features that Oracle has. To refresh my knowledge on foreign keys.<\/p>\n

Let us first start with the creation of two tables:<\/p>\n

DROP TABLE \"SCOTT\".\"CHILD\";\nCREATE TABLE \"SCOTT\".\"CHILD\" \n   (\t\"CHILD_ID\" NUMBER NOT NULL ENABLE, \n\t\"PARENT_ID\" NUMBER, \n\t\"BIJDRAGE\" NUMBER, \n\t CONSTRAINT \"CHILD_PK\" PRIMARY KEY (\"CHILD_ID\")) ;\n\nDROP TABLE \"SCOTT\".\"PARENT\";\nCREATE TABLE \"SCOTT\".\"PARENT\" \n   (\t\"PARENT_ID\" NUMBER NOT NULL ENABLE, \n\t\"PARENT_NAAM\" VARCHAR2(20 BYTE), \n\t CONSTRAINT \"PARENT_PK\" \n         PRIMARY KEY (\"PARENT_ID\")) ;\n\nINSERT INTO \"SCOTT\".\"PARENT\" \n(PARENT_ID, PARENT_NAAM) VALUES ('1', 'tom');\nINSERT INTO \"SCOTT\".\"PARENT\" \n(PARENT_ID, PARENT_NAAM) VALUES ('2', 'ine');\n\nINSERT INTO \"SCOTT\".\"CHILD\" \n(CHILD_ID, PARENT_ID, BIJDRAGE) VALUES ('1', '1', '123');\nINSERT INTO \"SCOTT\".\"CHILD\" \n(CHILD_ID, PARENT_ID, BIJDRAGE) VALUES ('2', '1', '23');\nINSERT INTO \"SCOTT\".\"CHILD\" \n(CHILD_ID, PARENT_ID, BIJDRAGE) VALUES ('3', '2', '45');\nINSERT INTO \"SCOTT\".\"CHILD\" \n(CHILD_ID, PARENT_ID, BIJDRAGE) VALUES ('4', '2', '65');\nINSERT INTO \"SCOTT\".\"CHILD\" \n(CHILD_ID, PARENT_ID, BIJDRAGE) VALUES ('5', '2', '78');<\/pre>\n
We now have two tables: a child and a parent. With a foreign key relation, I will link the two tables. This is done with:<\/p>\n
\nalter table \"CHILD\" \nadd foreign key(\"PARENT_ID\") \nreferences \"PARENT\"(\"PARENT_ID\")\n; \n<\/pre>\n
This links the parent to the child. In this case, one record in the “parent” table may be linked to zero, one or more records in the “child” table. In it also true that a record in the child must always be linked to the parent. This can also be seen in the database diagramme:
\n
\n\"\"<\/a>
\nThe foreign key relation described above does not allow you to remove a parent record that has still child records. If we want to remove such a record, we should first remove the child records.
\nTo avoid this somewhat cumbersome procedure (first delete relevant chiuld record, then remove parent record), we might use a different way of creating the foreign key:<\/p>\n
\nalter table \"CHILD\" \nadd foreign key(\"PARENT_ID\") \nreferences \"PARENT\"(\"PARENT_ID\")\non delete cascade\n; \n<\/pre>\n
In that case, removal of a parent record automatically deletes the child records. This could be quite convenient if we have a system where details are stored in a separate table, away from a general table. With the removal of records from the general table, the details are automatically removed as well.<\/p>\n
 <\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
Oracle has introduced a small version of its DBMS that is labelled “XE”. This small version can be downloaded and used for free upon the condition that its usage is for personal study only. I really like this application: it has a relative small footprint; it runs on a small machine and it allows you […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[],"class_list":["post-344","post","type-post","status-publish","format-standard","hentry","category-nice-to-know"],"_links":{"self":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/344","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=344"}],"version-history":[{"count":0,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/344\/revisions"}],"wp:attachment":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=344"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=344"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=344"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}