About Constraints in Oracle SQL

-

 Constraints

============

Constraints are conditions put on table columns.

Oracle provides certain constraints that can be assigned to table columns.

The total number of constraints are: 

1.CHECK 

2.UNIQUE 

3.NOT NULL 

4.PRIMARY KEY 

5.FOREIGN KEY


Check Constraint

----------------

A CHECK constraint can be used for validation, 

for example, ensuring a hire date is greater than the company's establishment date.

If a date lower than the establishment date is inserted, the constraint will throw an error.


Unique Constraint

-----------------

The UNIQUE constraint can be used to ensure a column, such as a cell number, has unique values for every person.

A column with a UNIQUE constraint can allow records without a cell number (null values).


Primary Key Constraint

----------------------

The PRIMARY KEY constraint is used to ensure values are both unique and not null.

It achieves the goal of not allowing duplicate values and not allowing null values.

If a PRIMARY KEY is imposed, trying to insert a duplicate or null value will not be allowed; a unique value must be passed.


Foreign Key Constraint

----------------------

Relational Database Management Systems (RDBMS) maintain data in multiple tables and relate them using a common column as a primary-foreign key relationship.

The department number in the department table is a primary key.

The department number in the employee table is a foreign key.

The foreign key column refers to the primary key column of the foreign (parent) table.

When a department number is inserted into the employee table, the foreign key constraint checks if that department number exists in the parent table's primary key column.

If the value exists, the record is allowed; otherwise, it is rejected with an error message.

The foreign key column in the employee table allows duplicate values, as multiple employees can belong to the same department.


Constraint Repositories

-----------------------

The repositories for constraints follow the pattern of those for tables, using the words "user" and "all" as prefixes.

USER_ constraints show only the constraints for the logged-in user.

select *from USER_constraints;

ALL_ constraints show the constraints for all users and all schemas.

Select *from ALL_constraints;

Comments

Post a Comment

Popular posts from this blog

Importance of Incremental Data in Oracle Fusion

-
 What is incremental data? Incremental Data Loads ----------------------- If you are sending employee data to a third-party payroll provider on every Friday, we don’t want to send all employees instead we only want the ones who changed current week. In this scenario, "LAST_UPDATE_DATE" column in the table plays vital role. LAST_UPDATE_DATE tells us when the record was physically saved. */ --Examples: ------------------------------- select count(1) from ( Select  papf.person_number, ppnf.FULL_NAME from  per_all_people_f papf, per_person_names_f ppnf WHERE 1=1 and papf.person_id=ppnf.person_id  and papf.LAST_UPDATE_DATE >=sysdate-7 --(Incremental) and ppnf.NAME_TYPE='GLOBAL' and sysdate between papf.EFFECTIVE_START_DATE and papf.EFFECTIVE_END_DATE and sysdate between ppnf.EFFECTIVE_START_DATE and ppnf.EFFECTIVE_END_DATE ) Result:  55 Records(Only Changed Records in current week) --------------------------------- select count(1) from ( Select  papf.person_n...
Read more

Mastering Parameters in Oracle Fusion BI Publisher

-
Mastering Parameters in Oracle Fusion BI Publisher: Mandatory, Optional, and "All" Logic When building Data Models in Oracle Fusion, how you handle parameters determines the user experience and report performance. Here is the definitive syntax guide: 1. Mandatory Parameters Use this when a value must be provided for the report to run. Syntax: AND column =:parameter Example: AND Person_Number = :p_Person_Number 2. Optional Parameters (The "All" Logic) Use this to allow users to leave a field blank or select "All" to see all records. Syntax (Recommended):  AND (:parameter IS NULL OR column = :parameter) Example: AND (:p_Person_Number IS NULL OR Person_Number = :p_Person_Number) Note: Using NVL is common but can be slower on large datasets as it may bypass indexes. 3. Multi-Select Parameters Use this when the user can select multiple values from a list. Syntax: AND (column IN (:parameter) OR 'All' IN (:parameter)) Example: AND (Person_Number IN (:p_Pe...
Read more

REST Web Service in Oracle Fusion to manage Banks

-
 REST Web Service  ================= Rest Resources Resources would have 5 end-points(methods) usually.  Ex: Create a Bank Read-Get a/all Banks Update Bank Delete a Bank Fusion Navigation for managing Banks  Setup and Maintenance Tasks Global Tasks Manage Banks Get All Banks  /fscmRestApi/resources/11.13.18.05/cashBanks https://fa-eubg-saasfademo1.ds-fa.oraclepdemos.com/fscmRestApi/resources/11.13.18.05/cashBanks Get a specific Bank  https://fa-eubg-saasfademo1.ds-fa.oraclepdemos.com/fscmRestApi/resources/11.13.18.05/cashBanks/{BankPartyId} Create a Bank  https://fa-eubg-saasfademo1.ds-fa.oraclepdemos.com/fscmRestApi/resources/11.13.18.05/cashBanks patch  Update  https://fa-eubg-saasfademo1.ds-fa.oraclepdemos.com/fscmRestApi/resources/11.13.18.05/cashBanks/{BankPartyId} Put https://fa-eubg-saasfademo1.ds-fa.oraclepdemos.com/fscmRestApi/resources/11.13.18.05/cashBanks/{BankPartyId} Delete  https://fa-eubg-saas...
Read more