You cannot delete a parent record if any existing child record is there. Some points to remember for referential integrity If any value is notĮxisting then the above statement fails and Oracle returns an error indicating Values which are existing in EMPNO column of EMP table. The above command succeeds only if EMPNO column in ATTENDANCE table contains Here EMPNO in attendance table is a foreign key referring to EMPNO of EMPĪlter table attendance add constraint empno_fkįoreign key (empno) references emp(empno) The following shows an example of parent child relationship. Table which has Primary Key is known as PARENT table and the table which hasįoreign key is known as CHILD table. This Primary keyĪnd Foreign key relationship is also known as PARENT-CHILD relationship i.e. A foreign key columnĬan refer to primary key or unique key column of other tables. On whichever column you put FOREIGN KEY constraint then the values in thatĬolumn must refer to existing values in the other table. If an Index already exist on that column then Oracle uses that Whenever you define a PRIMARY KEY Oracle automatically creates a index on Value then the above command fails and Oracle returns an error indicating of non The above command will succeed only if the existing values are compliant i.e. To define a primary key on a table give the following command.Īlter table emp add constraint emppk primary key (empno) You can use sequence numbers to easily generate numeric primary keys.įor example in EMP table EMPNO column is a good candidate for PRIMARY KEY. KEY constraint, by definition, does not allow any row toĬontain a null in any column that is part of the primary key.Ĭhoose a column that is short and numeric. Therefore, primary key values should rarely orĬhoose a column that does not contain any nulls. Is only used to identify a row in the table, and its data should never be Primary key is to uniquely identify each row of the table.Ĭhoose a column whose data values are never changed. ForĮxample, composite primary key values are long and cannot be assigned byĬhoose a column whose data values are unique, because the purpose of a Keys are allowed, they do not satisfy all of the other recommendations. Minimize your use of composite primary keys. Simple way to satisfy all the other guidelines. Whenever practical, use a column containing a sequence number. Table and ensures that no duplicate rows exist.
Only put NOT NULL constraint on those column which are essential forĮxample in EMP table ENAME column is a good candidate for NOT NULL constraint.Įach table can have one primary key, which uniquely identifies each row in a Then you will not be able insert rows for those employees whose commission is If you put NOT NULL constraint on COMM column it should not be left blank,Īlways be careful in defining NOT NULL constraint on columns, for example inĮmployee table some employees might have commission and some employees If you want toĮnsure that a column must always have a value, i.e. They areīy default all columns in a table can contain null values. There are five Integrity Constraints Available in Oracle. Integrity Constraints are used to prevent entry of invalid information Integrity Constraints (PRIMARY KEY, NOT NULL.) DEFAULT Values Dropping Constraints Disabling and Enabling Differing Constraints Check View Info about Constraints Working with Dates Oracle Views Oracle Sequences Oracle Synonyms Indexes and Clusters Table Partitioning Altering Partition Tables Dropping Partitions Merging Partitions Splitting Partitions Coalescing Partitions Oracle Objects and Object Types Oracle TO_TIMESTAMP Number Functions (Math Functions) Character Functions Miscellaneous Functions Aggregate Functions Date and Time Functions Oracle Join Queries GROUP BY Queries, SUB Queries CUBE, ROLLUP Functions Oracle DML (INSERT, UPDATE, DELETE.) Oracle DDL (CREATE, ALTER, DROP.) COMMIT, ROLLBACK,SAVEPOINT Data Control Language (GRANT, REVOKE) Oracle SQL Tutorial Contents Introduction to Databases CODD'S Rules Datatypes and Create Table Oracle SELECT Statement Formatting in SQL*Plus UNION, INTERSECT, MINUS Operators and Sorting Query Result 60 Technical Questions 42 Backup & Recovery Questions Unix For Oracle DBA 20 Questions Oracle DBA Interview Questions Most asked Oracle DBA Interview Questions.
0 kommentar(er)
