SQL DATA CONSTRAINTS DATA CONSTRAINTS Business rules which
SQL DATA CONSTRAINTS
DATA CONSTRAINTS • Business rules, which are enforced on data being stored in a table, are called constraints. • Business rules that are applied to data are completely System dependent.
APPLYING DATA CONSTRAINTS • • • Oracle permits data constraints to be attached to table column via SQL syntax. It checks data for integrity prior storage. Both the Create Table/ Alter Table SQL verb can be used to write sentences that attach constraints to a table column.
TYPES OF CONSTRAINTS • • Two types of constraints (1). I/O constraint (input/output) This data constraint determines the speed at which data can be inserted or extracted from a oracle table (2). Business rule constraint.
I/0 CONSTRAINTS 1. PRIMARY KEY constraints 2. FOREIGN KEY (SELF REFERENCE) 3. UNIQUE KEY constraints
PRIMARY KEY • • A primary key is one or more column(s) in a table used to uniquely identify each row in the table. Null value can not be allowed. A table can have only one PRIMARY KEY. The data held across the column MUST be UNIQUE.
PRIMARY KEY • • A single column primary key is called Simple key. A multicolumn primary key is called a Composite key. The only function of primary key in a table is to uniquely identify a row. Defined primary key in either a CREATE TABLE statement or an ALTER TABLE statement.
FEATURES OF PRIMARY KEY • • Record Uniqueness Not allow duplicate values Not allow null values It is not compulsory but it is recommended PRIMARY KEY can not be LONG and LONG RAW data type. Only one key per table. Unique index is created automatically Combine up to 16 columns in composite primary key
PRIMARY KEY DEFINED AT COLUMN LEVEL Syntax: <Column. Name> <Datatype>(<Size>) PRIMARY KEY • Example: CREATE TABLE Persons (P_Id Number(10) PRIMARY KEY, Last. Name varchar 2(255), First. Name varchar 2(255), Address varchar 2(255), City varchar 2(255)) ; •
PRIMARY KEY • • • ? Insert into persons( 1, ’CHINTAN’, ’SHAH’, ’SATELLITE’, ’AHME DABAD’); Row created Insert into persons(1, ’SANJAY’, ’PATEL’, ’GANDHIN AGAR’, ’GANDHINAGAR’);
PRIMARY KEY DEFINED AT TABLE LEVEL Syntax: PRIMARY KEY (<Column. Name>, <Column. Name>) • Example: CREATE TABLE student (Id Number(10), Eno Number(10), Last. Name varchar 2(255), First. Name varchar 2(255), Address varchar 2(255), City varchar 2(255), PRIMARY KEY(Id, Eno)) ; •
PRIMARY KEY • • • ? Insert into persons( 1, 100, ’CHINTAN’, ’SHAH’, ’SATELLITE’, ’A HMEDABAD’); Row created Insert into persons(1, 200, ’SANJAY’, ’PATEL’, ’GAND HINAGAR’, ’GANDHINAGAR’);
FOREIGN KEY • • • Foreign keys represent relationships between tables. A foreign key is a column whose values are derived from the primary key or unique key of some other table. Foreign Table or Detail Table Primary Table or Master Table CREATE and ALTER
FEATURES OF FOREIGN KEY • • Foreign key is a columns(s) that references a colum(s) of a table and it can be the same table also. Parent that is being referenced has to be unique or Primary key. Child may have duplicates and nulls but unless it specified. Foreign key constraint can be specified on child but not on parent.
• • Parent record can be delete provided no child record exist. Master table can not be updated if child record exist. Record can not be inserted into a detail table if corresponding records in the master table do not exist. Records of the master table can not be deleted if corresponding records in the detail table actually exist.
PRINCIPLES OF FOREIGN KEY CONSTRAINT • • • Rejects an INSERT or UPDATE of a value, if a corresponding value does not currently exist in the master key table. Rejects a DELETE from the master table if corresponding records in the DETAIL table exist. Must reference PRIMARY KEY or UNIQUE column(s) have matching data types.
FOREIGN KEY AT COLUMN LEVEL Syntax • <column. Name><Data. Type(Data. Size)> REFERENCES <Table. Name>[(<Columnname>)]; . • Let’s take an example for better understanding
EXAMPLE PERSONS TABLE Order Table O_ID ORDERNO P-ID 1 12357 1 2 52863 2 3 77809 2 4 45824 3
FOREIGN KEY � 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. � The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.
FOREIGN KEY CONSTRAINT DEFINED AT COLUMN LEVEL � CREATE TABLE Orders ( O_ID NUMBER(5) PRIMARY KEY, ORDERNO NUMBER(5), P_ID NUMBER(6) REFERENCES Persons(P_ID) );
FOREIGN KEY CONSTRAINT DEFINED AT TABLE LEVEL � CREATE TABLE Orders ( O_ID NUMBER(5) PRIMARY KEY, ORDERNO NUMBER(5), P_ID NUMBER(6) FOREIGN KEY REFERENCES Persons(P_ID) )
UNIQUE KEY � The Unique column constraint permits multiple entries of NULL into the column. � The NULL values are clubbed at the top of the column in the order in which they were entered into the table.
UNIQUE KEY CONSTRAINTS � Unique key will not allowed duplicate values � Unique index is created automatically. � More than one unique key in a table. � Unique key can combine upto 16 columns in a Composite unique key. � Unique key can not be LONG or LONG RAW data type.
UNIQUE KEY DEFINED AT COLUMN LEVEL Syntax: � <Column. Name> <Datatype>(<Size>) UNIQUE • Example: � CREATE TABLE Persons (P_Id Number(10) UNIQUE, Last. Name varchar 2(255), First. Name varchar 2(255), Address varchar 2(255), City varchar 2(255)) ; •
BUSINESS RULE CONSTRAINTS � ORACLE allows the application of business rules to table column. � Example. � Business rules can be implemented in ORACLE by using CHECK constraints. � CHECK constraints bound to be COLUMN or TABLE using the CREATE TABLE OR ALTER TABLE command.
BUSINESS RULE CONSTRAINTS � ORACLE allows programmer to define constraints at: v COLUMN LEVEL v TABLE LEVEL
NULL VALUE CONCEPTS �A NULL value is different from a blank or a zero. A NULL value can be inserted in to columns or any data type.
PRINCIPLES OF NULL VALUES � Setting a NULL value is appropriate when the actual value is unknown or not a meaningful value. � NULL value can be inserted into columns of any data type. � If column has a NULL value oracle ignores any UNIQUE , FOREIGN KEY, CHECK constraints that may be attach to the column.
DIFFERENCE BETWEEN EMPTY STRING AND NULL VALUES � Empty string is treated as a null value in oracle. � Ex: CREATE TABLE PERSONS (ID VARCHAR 2(10), NAME VARCHAR 2(20)); � INSERT VALUES: INSERT INTO PERSONS VALUES(‘A’, NULL); INSERT INTO PERSONS VALUES(‘B’, ’’);
NOT NULL CONSTRAINT DEFINED AT THE COLUMN LEVEL � Oracle has NOT NULL as a column constraint � The NOT NULL column constraint ensures that a table column cannot be left empty. � Syntax : <Column. Name> <Datasize>(<size>) NOT NULL
NOT NULL CONSTRAINT DEFINED AT THE COLUMN LEVEL � EX : CREATE TABLE Persons ( P_Id int NOT NULL, Last. Name varchar(255) NOT NULL, First. Name varchar(255), Address varchar(255), City varchar(255) );
THE CHECK CONSTRAINT � Business rule validations can be applied to a table column by using CHECK constraint. � CHECK constraints must be specified as logical expression that a evaluates either to TRUE or FALSE
CHECK CONSTRAINT DEFINED AT THE COLUMN LEVEL � Syntax: <Column. Name> <Datatype>(<size>) CHECK (<Logical Expression>)
CHECK CONSTRAINT DEFINED AT THE COLUMN LEVEL � EX: CREATE TABLE Persons ( P_Id number(10) CHECK(P_Id>0), Last. Name varchar(255) CHECK(Last. Name=UPPER(Last. Name), First. Name varchar 2(200) CHECK(First. Name=UPPER(First. Name) );
RESTRICTIONS ON CHECK CONSTRAINTS � The condition must be a Boolean expression that can be evaluated using the values in the row being inserted or updated. � The condition cannot contain subqueries or sequences. � The condition cannot include the SYSDATE , UID , USER OR USERENV SQL functions.
DEFAULT VALUE CONCEPTS � At the time of table creation a Default Value can be assigned to a column. � Oracle engine will automatically load this column with the default value specified. � The data type of the default value should match the data type of the column. � The DEFAULT clause can be used to specify a default value fro a column.
DEFAULT VALUE CONCEPTS � Syntax : <Column. Name> <Data. Type>(<Size>) DEFAULT <Value>; EX: CREATE TABLE Persons ( P_Id number(10) DEFAULT 0, Last. Name varchar(255) DEFAULT ‘A’); �
- Slides: 37