- · Allows a foreign key row that is referenced to a primary key row to be deleted- ON DELETE CASCADE
- · Explicitly defines a condition that must be met- Check Constraint
- · A column or set of columns that uniquely identifies each row in a table- PRIMARY KEY
- · Constraint ensures that the column contains no null values-NOT NULL
- · Allows a child row to remain in a table with null values when a parent record has been deleted-ON DELETE SET NULL
- · Establishes a relationship between the foreign key column and a primary key or unique key in the same table or a different table-FOREIGN KEY Constraint
1.
What is the purpose of a
a. PRIMARY KEY
Uniquely identify each row in table.
b.
FOREIGN KEY
Referential integrity constraint links back parent table's
primary/unique key to child table's column.
c.
CHECK CONSTRAINT
Explicitly define condition to be met by each row's fields.
This condition must be returned as true or unknown.
2.
Using the column information for the animals table
below, name constraints where applicable at the table level, otherwise name
them at the column level. Define the primary key (animal_id). The
license_tag_number must be unique. The admit_date and vaccination_date columns
cannot contain null values.
animal_id NUMBER(6) -
PRIMARY KEY
name VARCHAR2(25)
license_tag_number NUMBER(10)- UNIQUE
admit_date DATE-
NOT NULL
adoption_id NUMBER(5),
vaccination_date DATE- NOT NULL
3.
Create the animals
table. Write the syntax you will use to create the table.
CREATE TABLE animals
( animal_id
NUMBER(6,0) CONSTRAINT anl_anl_id_pk PRIMARY KEY ,
name VARCHAR2(25),
license_tag_number NUMBER(10,0) CONSTRAINT anl_l_tag_num_uk
UNIQUE,
admit_date DATE
CONSTRAINT anl_adt_dat_nn NOT NULL ENABLE,
adoption_id
NUMBER(5,0),
vaccination_date DATE
CONSTRAINT anl_vcc_dat_nn NOT NULL ENABLE
);
DESCRIBE
animals;
SELECT *
FROM
user_constraints
WHERE
LOWER(table_name) = 'animals';
4.
Enter one row into
the table. Execute a SELECT * statement to verify your input. Refer to the
graphic below for input.
|
ANIMAL_ID
|
NAME
|
LICENSE_TAG_NUMBER
|
ADMIT_DATE
|
ADOPTION_ID
|
VACCINATION_DATE
|
|
101
|
Spot
|
35540
|
10-Oct-2004
|
205
|
12-Oct-2004
|
INSERT INTO animals (animal_id, name, license_tag_number,
admit_date, adoption_id, vaccination_date)
VALUES( 101, 'Spot', 35540, TO_DATE('10-Oct-2004',
'DD-Mon-YYYY'), 205, TO_DATE('12-Oct-2004', 'DD-Mon-YYYY'));
SELECT * FROM animals;
5.
Write the syntax to
create a foreign key (adoption_id) in the animals table that has a
corresponding primary- key reference in the adoptions table. Show both the
column-level and table-level syntax. Note that because you have not actually created an adoptions table, no
adoption_id primary key exists, so the foreign key cannot be added to the
animals table.
Note:
I tried below mentioned queries with d_songs(id) and later find replace all for
the sake of problem statement.
If there is a
value in animals.adoption_id, which is not present as primary key/unique key in
parent table, it will give error, so first fix it:
UPDATE animals
SET
adoption_id = ( SELECT id FROM
adoptions WHERE ROWNUM = 1);
or
UPDATE animals
SET
adoption_id = NULL;
SELECT * FROM
animals;
Adding foreign
key using column level statement:
ALTER TABLE animals
MODIFY ( adoption_id
NUMBER(5,0) CONSTRAINT anl_adopt_id_fk
REFERENCES adoptions(id) ENABLE );
Verify that
constraint is generated:
SELECT *
FROM
user_constraints
WHERE LOWER(table_name)
= 'animals' AND constraint_type = 'R';
Now drop it:
ALTER TABLE
animals
DROP CONSTRAINT
anl_adopt_id_fk ;
Adding foreign
key using table level statement:
ALTER TABLE animals
ADD CONSTRAINT anl_adopt_id_fk FOREIGN
KEY (adoption_id)
REFERENCES adoptions(id) ENABLE;
Verify that
constraint is generated:
SELECT *
FROM
user_constraints
WHERE
LOWER(table_name) = 'animals' AND constraint_type = 'R';
6.
What is the effect
of setting the foreign key in the ANIMAL table as:
ALTER TABLE animals
ADD CONSTRAINT anl_adopt_id_fk FOREIGN KEY (adoption_id)
REFERENCES adoptions (id) ENABLE ;
Gives:
SELECT
delete_rule
FROM
user_constraints
WHERE
LOWER(table_name) = 'animals' AND constraint_type = 'R';
Assume,
adoptions has a row with id 500 and this
row is referenced in animals. If I try:
DELETE FROM adoptions
WHERE id= 500;
I will get
error:
ORA-02292: integrity constraint (HKUMAR.ANL_ADOPT_ID_FK)
violated - child record found
b.
ON DELETE SET NULL
ALTER TABLE animals
DROP CONSTRAINT anl_adopt_id_fk ;
ALTER
TABLE animals
ADD
CONSTRAINT anl_adopt_id_fk FOREIGN KEY
(adoption_id)
REFERENCES adoptions(id) ON DELETE SET NULL
ENABLE ;
SELECT delete_rule
FROM user_constraints
WHERE LOWER(table_name) = 'animals' AND
constraint_type = 'R';
DELETE FROM adoptions WHERE id= 500;
SELECT * FROM animals;
Value
in animals.adoption_id where 500 adoptions.id from parent was referred is now
set to NULL;
a.
ON DELETE CASCADE
Recreate the
row in adoptions with adoptions.id 500 again, refer it in animals.adoption_id.
ALTER TABLE
animals
DROP CONSTRAINT
anl_adopt_id_fk ;
ALTER TABLE animals
ADD CONSTRAINT anl_adopt_id_fk FOREIGN KEY (adoption_id)
REFERENCES adoptions(id)
ON DELETE CASCADE ENABLE ;
SELECT
delete_rule
FROM
user_constraints
WHERE
LOWER(table_name) = 'animals' AND constraint_type = 'R';
DELETE FROM adoptions WHERE id= 500;
But
in fact child table also lost the row referring to this parent row:
SELECT * FROM animals;
7.
What are the
restrictions on defining a CHECK constraint?
·
I
cannot specify check constraint for a view however in this case I could use
WITH CHECK OPTION clause
·
I
am restricted to columns from self table and fields in self row.
·
I
cannot use subqueries and scalar subquery expressions.
·
I
cannot call functions that are not deterministic e.g. CURRENT_DATE,
CURRENT_TIMESTAMP, DBTIMEZONE, LOCALTIMESTAMP, SESSIONTIMEZONE, SYSDATE, SYSTIMESTAMP, UID, USER, and USERENV
CREATE TABLE
hemant2
(somecolumnname2
DATE CHECK (somecolumnname2 > SYSDATE)
);
Says ORA-02436:
date or system variable wrongly specified in CHECK constraint
But below
mentioned works:
CREATE TABLE
hemant2
(somecolumnname2
DATE CHECK (somecolumnname2 > TO_DATE('03-Nov-2016','DD-Mon-YYYY'))
);
SELECT *
FROM
user_constraints
WHERE
LOWER(table_name) = 'hemant2';
·
I
cannot call user defined functions
·
I
cannot dereference a REF column e.g. using the DEREF function
·
Nested
table columns or attributes are not allowed
·
pseudocolumns
CURRVAL, NEXTVAL, LEVEL, or ROWNUM are not allowed
·
I
can't use Date constants that are not fully specified
This last
statement is ambiguous, better is to convert a date with fully specified format
in check constraint. I give one example for this:
In Application Express 4.0.2.00.09:
SELECT SYSDATE,
TO_CHAR(SYSDATE, 'dd-month-yyyy') from dual;
SELECT TO_DATE('11/04/2016'), TO_CHAR(TO_DATE('11/04/2016'),
'dd-month-yyyy') from dual;
So, I got to know that in Application Express 4.0.2.00.09
default format is 'mm/dd/yyyy'
But
CREATE TABLE
hemant3
(somecolumnname2
DATE CHECK (somecolumnname2 > TO_DATE('11/04/2016'))
);
Says: ORA-01843: not a valid month
But Below mentioned works:
CREATE TABLE
hemant3
(somecolumnname2
DATE CHECK (somecolumnname2 > TO_DATE('11/04/2016','mm/dd/yyyy' ))
);
Application Express 5.0.3.00.03
SELECT SYSDATE,
TO_CHAR(SYSDATE, 'dd-month-yyyy') from dual;
SELECT TO_DATE('03-Nov-2016'), TO_CHAR(TO_DATE('03-Nov-2016'),
'dd-month-yyyy') from dual;
So, I got to know that in Application Express 5.0.3.00.03
default format is ‘dd-Mon-yyyy’
As opposed to previous version of apex, here, below
mentioned works!
CREATE TABLE
hemant3
(somecolumnname2
DATE CHECK (somecolumnname2 > TO_DATE('03-Nov-2016'))
);
Also, below
mentioned works:
CREATE TABLE
hemant5
(somecolumnname2
DATE CHECK (somecolumnname2 > TO_DATE('11/04/2016','mm/dd/yyyy' ))
);
So, seems to be newer versions of ORACLE can bypass this
limitation, but better is to stick to this limitation to keep my code version
free.
No comments:
Post a Comment