Downloaded 178 times
![Constraint [Constraint name] Constraint Type](https://image.slidesharecdn.com/sqlconstraint-copy-120220160804-phpapp02/75/oracle-Sql-constraint-4-2048.jpg)
Uploaded byhome
oracle Sql constraint
This document discusses SQL constraints. It defines constraints as limitations on the type of data that can go into a table. The main types of constraints covered are: 1. Not null constraints, which enforce that a column cannot be null 2. Unique constraints, which uniquely identify each record in a table 3. Primary key constraints, which uniquely identify each record and cannot be null 4. Foreign key constraints, which link to primary keys in other tables to define relationships 5. Check constraints, which define valid value ranges for a column 6. Default constraints, which provide a default value for a column if no other value is specified. Examples are provided for each constraint type to illustrate their syntax and usage
In this document
Powered by AI
Introduction to SQL and its constraints, presented by Israa Al-Jarrah from the CIS Department.
Explanation of SQL constraints, the meaning of constraints, and types of constraints including integrity, value, and table constraints. Constraints can be defined inline (for columns) or out-of-line (for tables) during table creation or alteration.
The UNIQUE constraint ensures each record is distinct, with examples provided for defining it within SQL tables.
Primary key constraints ensure unique and non-null values, with detailed examples of their implementation in tables.
Comparison between Unique and Primary Keys in terms of null value acceptance and usage in tables.
Foreign keys link records between tables, maintaining integrity with examples of related Persons and Orders tables.
Examples of SQL commands to create tables with Foreign Key constraints, ensuring relational integrity.
Check constraints limit allowed values in columns; Default constraints insert preset values for new records.
Example SQL statements defining primary keys composed of multiple columns and unique constraints.
Reference materials and closing remarks from the presenter, thanking the audience for their attention.
More Related Content
Similar to oracle Sql constraint
![Vibe Coding vs. Spec-Driven Development [Free Meetup]](https://cdn.slidesharecdn.com/ss_thumbnails/vibecodingvsspecdrivendevelopment-251209105622-43f455e7-thumbnail.jpg?width=640&height=640&fit=bounds)
oracle Sql constraint
- 1. SQL Constraint Made By: Israa Al- Jarrah CIS Dept-- IT Faculty
- 2. SQL Constraint Meaning Write Constraint Constraint Type
- 3. Constraints canbe specified when a table is created (with the CREATE TABLE statement) or after the table is created (with the ALTER TABLE statement) Constraints are used to limit the type of data that can go into a table.
- 4. Constraint [Constraint name] Constraint Type
- 5. You can defineconstraints syntactically in two ways: 1. As part of the definition of an individual column or attribute. This is called inline specification. 2. As part of the table definition. This is called out-of-line specification.
- 6. Types of constraints: 1.Integrity constraints 2.Value constraints Table constraint Restricts data value with respect to all other values in table
- 7. 1. Not Null 2.Unique Key 3. Primary Key 4. Foreign Key 5. Check Key 6. Default Key
- 8. Not Null constraintenforces a column to NOT accept NULL values. constraint enforces a column to NOT accept NULL values. This means that you cannot insert a new record, or update a record without adding a value to this field. Expression of Not Null we use short cut NN
- 9. 1. Not NullExample :- Create Table Language ( Language_Name varchar2(30), Language_Id number(15) Constraint Constraint_Language_language_name _NN Not Null );
- 11. All other constraints canbe declared either inline or out of line.
- 12. 2. Unique Key The UNIQUE constraint uniquely identifies each record in a database table.
- 13. 2. Unique KeyExample Create Table Person ( Person_Id number(15) Constraint Person_person_id_UN Unique Person_name varchar2(40), Person_BD Date );
- 14. 2. Unique KeyExample Create Table Person ( Person_Id number(15), Person_name varchar2(40), Person_BD Date Constraint Person_person_id_UN Unique (Person_Id)
- 15. 3. Primary Key Primary keys must contain unique values A primary key column cannot contain NULL values. Each table should have a primary key, and each table can have only ONE primary key
- 16. 3. Primary KeyExample CREATE TABLE locations_demo location_id NUMBER(4) Constraint Constraint_loc_id_pk PRIMARY KEY , street_address VARCHAR2(40) , postal_code VARCHAR2(12) );
- 17. 3. Primary KeyExample CREATE TABLE locations_demo location_id NUMBER(4) , street_address VARCHAR2(40) , postal_code VARCHAR2(12) , Constraint Constraint_loc_id_pk PRIMARY KEY (Location_id) );
- 18. Unique Key Unique Key Primary Key
- 19. Unique Key UniqueKey accept only one null value unique key use many time in table.
- 20. Primary Key UniqueKey Primary key does not accept null value primary key use only one time in table.
- 21. 4. Foreign Key Unique Key A FOREIGN KEY in one table points to a PRIMARY KEY in another table The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.
- 22. 4. Foreign KeyExample Unique Key The "Persons" table: P-Id LastName FirstName Address CIty 1 Hansen Ola Timoteivn Sandnes 10 2 Svendson Tove Borgvn23 Sandnes 3 Pettersen Kari Storagt 20 Sandnes
- 23. 4. Foreign KeyExample Unique Key The “Order" table: O_Id OrderNo P_id 1 77895 3 2 44678 3 3 22456 2 4 24562 1
- 24. 4. Foreign KeyExample "P_Id" column in the "Orders" table points to the "P_Id" column in the "Persons" table. The "P_Id" column in the "Persons" table is the PRIMARY KEY in the "Persons" table. The "P_Id" column in the "Orders" table is a FOREIGN KEY in the "Orders" table.
- 25. 4. Foreign KeyExample First step must create “Person” Table Create Table Person ( P_Id number Constraint constraint_person_person_Id_PK primary key, LastName varcahr2(45), FirstName varchar2(45), Address varchar2(45), City varchar2(45) );
- 26. 4. Foreign KeyExample Create Table Order ( Order_id number(15) CONSTRAINT con_Order_id_pk Primary Key, OredrNO number(30) , P_id number(15) CONSTRINT con_person_order_id_FK refernace Person (p_id) );
- 27. 5. Check Key The CHECK constraint is used to limit the value range that can be placed in a column. If you define a CHECK constraint on a single column it allows only certain values for this column
- 28. 5. Check KeyExample CREATE TABLE divisions ( div_no NUMBER CONSTRAINT check_divno CHECK (div_no BETWEEN 10 AND 99), div_name VARCHAR2(9) CONSTRAINT check_divname_cc CHECK (div_name = UPPER(div_name)) );
- 29. 5. Check Key 6. Default Key The DEFAULT constraint is used to insert a default value into a column The default value will be added to all new records if no other value is specified
- 30. 5. Check KeyExample 6. Default Key CREATE TABLE Persons ( P_Id Number(15), LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255) DEFAULT 'Sandnes' );
- 31. CONSTRAINT constraint_name PRIMARY KEY(columnname1, columnname2 …)
- 32. Create table person ( Person_ID Number(15), Person_phone number(15), Constraint con_person_un Unique(Person_ID,Person_Phone) );
- 33.
- 34. Thank you forlisten Israa jarrah