{"id":10687,"date":"2019-07-24T11:46:50","date_gmt":"2019-07-24T11:46:50","guid":{"rendered":"https:\/\/ittutorial.org\/?p=10687"},"modified":"2019-07-24T12:58:14","modified_gmt":"2019-07-24T12:58:14","slug":"oracle-sql-tutorials-chapter-7-part-1-of-2","status":"publish","type":"post","link":"https:\/\/ittutorial.org\/oracle-sql-tutorials-chapter-7-part-1-of-2\/","title":{"rendered":"Oracle SQL Tutorials Chapter 7 ( Part 1 of 2 )"},"content":{"rendered":"<p><strong>Chapter 7 \u2013 CREATING AND MANAGING TABLES<\/strong><\/p>\n<ul>\n<li><strong>Classify Main Database Objects<\/strong><\/li>\n<li><strong>Examining Table Structure<\/strong><\/li>\n<li><strong>Data Types of the Columns<\/strong><\/li>\n<li><strong>Creating a Simple Table<\/strong><\/li>\n<li><strong>Understanding How Schema Objects Work<\/strong><\/li>\n<\/ul>\n<p><strong>DATABASE OBJECTS<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-10688\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-148.png\" width=\"483\" height=\"284\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-148.png 795w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-148-300x176.png 300w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-148-768x451.png 768w\" sizes=\"auto, (max-width: 483px) 100vw, 483px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p><strong>NAMING RULES<\/strong><\/p>\n<p>Tables and columns are named according to the following rules ;<\/p>\n<ul>\n<li>Must be start with a letter.<\/li>\n<li>Can be up to 30 characters long.<\/li>\n<li>Only \u201cA \u2013 Z, a \u2013 z, 0\u20139, _, $, #\u201d characters are allowed.<\/li>\n<li>Under the same user, a name can only be used once.<\/li>\n<li>There should be no names reserved for Oracle.<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p><strong>CREATING TABLE<\/strong><\/p>\n<ul>\n<li>The following syntax is used when creating a table ;<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-10689\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-149.png\" width=\"403\" height=\"71\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-149.png 625w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-149-300x53.png 300w\" sizes=\"auto, (max-width: 403px) 100vw, 403px\" \/><\/p>\n<pre>CREATE TABLE HR.WORKERS\r\n(\r\n    WORKER_ID    NUMBER (6),\r\n    FIRST_NAME   VARCHAR2 (20 BYTE),\r\n    LAST_NAME    VARCHAR2 (25 BYTE),\r\n    HIRE_DATE    DATE,\r\n    JOB_ID       VARCHAR2 (10 BYTE),\r\n    SALARY       NUMBER (8, 2)\r\n\r\n)<\/pre>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p><strong>ADDING COMMENTS TO TABLE<\/strong><\/p>\n<ul>\n<li>Comments can be added to tables or columns seperatly.<\/li>\n<li>These comments can be queried from the USER_TAB_COMMENTS and USER_COL_COMMENTS views.<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<pre>COMMENT ON TABLE hr.workers IS 'This table is for workers';\r\n\r\nCOMMENT ON COLUMN hr.workers.salary IS 'This column is for salaries of workers';<\/pre>\n<ul>\n<li>You can see comments from schema browser like this ;<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"601\" height=\"352\" class=\"wp-image-10690\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-150.png\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-150.png 601w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-150-300x176.png 300w\" sizes=\"auto, (max-width: 601px) 100vw, 601px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p><strong>CONSTRAINTS<\/strong><\/p>\n<ul>\n<li>Constraints force table-level rules to be applied.<\/li>\n<li>They prevent deletion of interdependent data.<\/li>\n<li>They prevent incorrect data entry.<\/li>\n<li>They ensure that data is unique.<\/li>\n<li>They can be at table or column level.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-10691\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-151.png\" width=\"212\" height=\"159\" \/><\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p><strong>NOT NULL CONSTRAINT<\/strong><\/p>\n<ul>\n<li>Prevents specified columns from being null.<\/li>\n<\/ul>\n<pre>CREATE TABLE HR.WORKERS\r\n(\r\n    EMPLOYEE_ID   NUMBER (6),\r\n    FIRST_NAME    VARCHAR2 (20 BYTE) NOT NULL,\r\n    LAST_NAME     VARCHAR2 (25 BYTE),\r\n    HIRE_DATE     DATE,\r\n    JOB_ID        VARCHAR2 (10 BYTE),\r\n    SALARY        NUMBER (8, 2)\r\n);<\/pre>\n<p>&nbsp;<\/p>\n<ul>\n<li>Let\u2019s try to insert null value to \u2018first_name\u2019 column.<\/li>\n<\/ul>\n<pre>INSERT INTO hr.workers (first_name)\r\n     VALUES ('');<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-10692\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-152.png\" width=\"417\" height=\"138\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-152.png 523w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-152-300x99.png 300w\" sizes=\"auto, (max-width: 417px) 100vw, 417px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p><strong>UNIQUE CONSTRAINT<\/strong><\/p>\n<ul>\n<li>Unique Constraint can be placed for one or more columns.<\/li>\n<li>More than one UC can be placed in a table.,<\/li>\n<\/ul>\n<pre>CREATE TABLE HR.WORKERS\r\n(\r\n    EMPLOYEE_ID   NUMBER (6) UNIQUE,\r\n    FIRST_NAME    VARCHAR2 (20 BYTE),\r\n    LAST_NAME     VARCHAR2 (25 BYTE),\r\n    HIRE_DATE     DATE,\r\n    JOB_ID        VARCHAR2 (10 BYTE),\r\n    SALARY        NUMBER (8, 2)\r\n)<\/pre>\n<p>&nbsp;<\/p>\n<ul>\n<li>Let\u2019s execute this insert command two times and see the results. It will throw error at the second time we executed. Because \u2018employee_id\u2019 colums must be unique.<\/li>\n<\/ul>\n<pre>INSERT INTO hr.workers (employee_id)\r\n     VALUES (1);<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-10693\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-153.png\" width=\"420\" height=\"137\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-153.png 524w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-153-300x98.png 300w\" sizes=\"auto, (max-width: 420px) 100vw, 420px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p><strong>PRIMARY KEY CONSTRAINT<\/strong><\/p>\n<ul>\n<li>A table can only have one primary key.<\/li>\n<li>This column can not be Null.<\/li>\n<li>It also works as an index.<\/li>\n<\/ul>\n<pre>CREATE TABLE HR.WORKERS\r\n(\r\n    EMPLOYEE_ID    NUMBER (6) CONSTRAINT E_PK PRIMARY KEY,\r\n    FIRST_NAME     VARCHAR2 (20 BYTE),\r\n    LAST_NAME      VARCHAR2 (25 BYTE),\r\n    HIRE_DATE      DATE,\r\n    JOB_ID         VARCHAR2 (10 BYTE),\r\n    SALARY         NUMBER (8, 2)\r\n)<\/pre>\n<ul>\n<li>\u2018Employee_id\u2019 column is primary key, so can not take same values and can not be null.<\/li>\n<li>Index is created on \u2018Employee_id\u2019 columnd automatically.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"828\" height=\"187\" class=\"wp-image-10694\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-154.png\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-154.png 828w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-154-300x68.png 300w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-154-768x173.png 768w\" sizes=\"auto, (max-width: 828px) 100vw, 828px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p><strong>FOREIGN KEY CONSTRAINT<\/strong><\/p>\n<ul>\n<li>It guarantees that, the data in one column of a table must come from another table.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"956\" height=\"441\" class=\"wp-image-10695\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-155.png\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-155.png 956w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-155-300x138.png 300w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-155-768x354.png 768w\" sizes=\"auto, (max-width: 956px) 100vw, 956px\" \/><\/p>\n<ul>\n<li>Let\u2019s create a table with foreign key constraint.<\/li>\n<\/ul>\n<pre>CREATE TABLE HR.WORKERS\r\n(\r\n    EMPLOYEE_ID    NUMBER (6) CONSTRAINT E_PK PRIMARY KEY,\r\n    FIRST_NAME     VARCHAR2 (20 BYTE),\r\n    LAST_NAME      VARCHAR2 (25 BYTE),\r\n    HIRE_DATE      DATE,\r\n    JOB_ID         VARCHAR2 (10 BYTE),\r\n    SALARY         NUMBER (8, 2),\r\n    DEPARTMENT_ID  NUMBER (4),\r\n    CONSTRAINT D_FK\r\n    FOREIGN KEY (DEPARTMENT_ID)\r\n    REFERENCES HR.DEPARTMENTS(department_id)\r\n);<\/pre>\n<p>&nbsp;<\/p>\n<ul>\n<li>Here is the Departments table\u2019s content.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"426\" height=\"319\" class=\"wp-image-10696\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-156.png\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-156.png 426w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-156-300x225.png 300w\" sizes=\"auto, (max-width: 426px) 100vw, 426px\" \/><\/p>\n<p>&nbsp;<\/p>\n<ul>\n<li>I will try to add \u2018123\u2019 to department_id column. I will receive an error like below. Because we have foreign key constraint in department_id columnd and \u2018123\u2019 doesn\u2019t exist in the Departments table.<\/li>\n<li>For instance ; I can insert \u201870\u2019 to department_id because it is exist on the other table.<\/li>\n<\/ul>\n<pre>INSERT INTO hr.workers (employee_id, department_id)\r\n     VALUES (1, 123);<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-10697\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-157.png\" width=\"421\" height=\"135\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-157.png 521w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-157-300x96.png 300w\" sizes=\"auto, (max-width: 421px) 100vw, 421px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p><strong>CHECK CONSTRAINT<\/strong><\/p>\n<ul>\n<li>It defines the requirements for each row.<\/li>\n<li>CURRVAL, NEXTVAL, LEVEL and ROWNUM aliases can not be used.<\/li>\n<li>SYSDATE, UID, USER and USERNV functions can not be called.<\/li>\n<li>Queries referring to other values in other rows can not be performed.<\/li>\n<\/ul>\n<pre>CREATE TABLE HR.WORKERS\r\n(\r\n    EMPLOYEE_ID    NUMBER (6) CONSTRAINT E_PK PRIMARY KEY,\r\n    FIRST_NAME     VARCHAR2 (20 BYTE),\r\n    LAST_NAME      VARCHAR2 (25 BYTE),\r\n    HIRE_DATE      DATE,\r\n    JOB_ID         VARCHAR2 (10 BYTE),\r\n    SALARY         NUMBER (8, 2) CONSTRAINT S_MIN CHECK (SALARY &gt; 3000)\r\n);<\/pre>\n<p>&nbsp;<\/p>\n<ul>\n<li>We can\u2019t insert \u20182500\u2019 to salary column because of the check constraint.I can only insert values that higher than \u20183000\u2019.<\/li>\n<\/ul>\n<pre>INSERT INTO hr.workers (employee_id, salary)\r\n     VALUES (1, 2500);<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-10698\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-158.png\" width=\"422\" height=\"137\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-158.png 520w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-158-300x98.png 300w\" sizes=\"auto, (max-width: 422px) 100vw, 422px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p>You can continue to read from this link ;<\/p>\n<blockquote class=\"wp-embedded-content\" data-secret=\"ZdnBqC0usT\"><p><a href=\"https:\/\/ittutorial.org\/oracle-sql-tutorials-chapter-7-part-2-of-2\/\">Oracle SQL Tutorials Chapter 7 ( Part 2 of 2 )<\/a><\/p><\/blockquote>\n<p><iframe loading=\"lazy\" class=\"wp-embedded-content\" sandbox=\"allow-scripts\" security=\"restricted\" style=\"position: absolute; clip: rect(1px, 1px, 1px, 1px);\" title=\"&#8220;Oracle SQL Tutorials Chapter 7 ( Part 2 of 2 )&#8221; &#8212; IT Tutorial\" src=\"https:\/\/ittutorial.org\/oracle-sql-tutorials-chapter-7-part-2-of-2\/embed\/#?secret=aOdgBuvcux#?secret=ZdnBqC0usT\" data-secret=\"ZdnBqC0usT\" width=\"600\" height=\"338\" frameborder=\"0\" marginwidth=\"0\" marginheight=\"0\" scrolling=\"no\"><\/iframe><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Chapter 7 \u2013 CREATING AND MANAGING TABLES Classify Main Database Objects Examining Table Structure Data Types of the Columns Creating a Simple Table Understanding How Schema Objects Work DATABASE OBJECTS &nbsp; &nbsp; NAMING RULES Tables and columns are named according to the following rules ; Must be start with a letter. Can be up to &hellip;<\/p>\n","protected":false},"author":29,"featured_media":9920,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[3019],"tags":[5500,5496,5523,5527,5499,5506,5489,5492,3009,3048,3010,5491,5495,5509,5512,5533,5534,5535,5526,5528,5529,5530,5531,5532,5497,5493,5518,5508,1340,5507,5520,5516,3001,3013,5517,1572,5524,5525,5503,3003,5501,5505,5488,5494,5519,5490,5511,5510,5515,5504,5502,3000,3012,5514,5498,5513,5522,5521,3002,3008],"class_list":["post-10687","post","type-post","status-publish","format-standard","has-post-thumbnail","","category-oracle-sql","tag-add-column-comment","tag-add-comments-on-table","tag-check-constraint","tag-check-constraint-violated","tag-column-comment","tag-constraints","tag-create-table","tag-database-objects","tag-how-to-write-sql","tag-learn-oracle","tag-learn-sql","tag-manage-tables","tag-naming-rules","tag-not-null-constraint","tag-ora-01400","tag-ora-00001","tag-ora-00001-unique-constraint-violated","tag-ora-01400-cannot-insert-null-into","tag-ora-02290","tag-ora-02290-check-constraint-violated","tag-ora-02290-check-constraint-violated-solution","tag-ora-02291","tag-ora-02291-integrity-constraint-violated","tag-ora-02291-integrity-constraint-violated-parent-key-not-found","tag-oracle-add-comment-on-table","tag-oracle-database-objects","tag-oracle-foreign-key","tag-oracle-not-null","tag-oracle-sql","tag-oracle-sql-constraints","tag-oracle-sql-foreign-key","tag-oracle-sql-primary-key","tag-oracle-sql-tutorial","tag-oracle-sql-tutorials","tag-primary-key-constraint","tag-sql","tag-sql-check","tag-sql-check-constraint","tag-sql-column-comment","tag-sql-commands","tag-sql-comment","tag-sql-constraints","tag-sql-create-table","tag-sql-database-objects","tag-sql-foreign-key","tag-sql-manage-tables","tag-sql-not-null","tag-sql-not-null-constraint","tag-sql-primary-key","tag-sql-table-and-column-comments","tag-sql-table-comment","tag-sql-tutorial","tag-sql-tutorials","tag-sql-unique-constraint","tag-table-comment","tag-unique-constraint","tag-what-is-foreign-key","tag-what-is-foreign-key-in-sql","tag-what-is-sql","tag-writing-sql"],"aioseo_notices":[],"jetpack_featured_media_url":"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/06\/sql-express.png","jetpack_sharing_enabled":true,"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/10687","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/users\/29"}],"replies":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/comments?post=10687"}],"version-history":[{"count":4,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/10687\/revisions"}],"predecessor-version":[{"id":10733,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/10687\/revisions\/10733"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media\/9920"}],"wp:attachment":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media?parent=10687"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/categories?post=10687"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/tags?post=10687"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}