CREATING TABLES AND ENFORCING INTEGRITY
Go to Oracle Constraints, go to MS SQL Server Constraints Documentation. Also see Creating Database chapter.
Q1. How are tables created in SQL?
A1. Use a create table statement as for example:
CREATE TABLE P
(pNo text(5),
pName text(50),
color text(20),
weight double,
city text(20) );
Q2. Can you give the data types we can use in SQLLEARN and Oracle?
A2. SQLLEARN ( works for Microsoft Access)
|
Data Type |
Description |
|
byte |
Integer in range 0 to 255 |
|
currency |
Monetary value, accurate to 4 decimal places |
|
datetime |
The dates must be in single quotes, various formats are acceptable, e.g. '10/03/99' or '03-OCT-99' |
|
integer |
integer value |
|
single, double |
single or double precision floating-point number |
|
text, longtext |
alphanumeric text in single quotes, longtext is a memo |
Oracle
|
DATA TYPE |
DESCRIPTION |
|
char(n) |
Character string of size n |
|
varchar2(n) |
Variable length string up to n characters. |
|
date |
Date usually in format '03-0ACT-1999' |
|
number |
Real value up to 40 spaces. |
|
number(n) |
Real value up to n spaces |
|
number(n,d) |
Real value up to n spaces with d digits after decimal point. |
|
integer |
integer value (no decimals) |
|
integer(n) |
integer with n spaces. |
Q3. So what's the formal sytax to create a table?
A3. CREATE TABLE <table name>
(col-name, ..., col-name, table-constraint, ...,
table-constraint);
Q4. And syntax for column definition?
A4. <column-name> <data-type> [default
<expr>] [<column-constraints>]
Note in formal definitions [...] means optional
and <...> user defined identifier or expression.
Q5.And syntax for column constraint?
A5. [constraint <constraint_name>] [not]
null | check (< condition >) | unique | primary key | refereneces
<table_name>[(column_name>)] [on delete cascade] | [on delete
restrict] | [on delete set default] | [on update cascade] | [on update
restrict] | [on update set default]
Note that | means OR.
Q6. What are table and column constraints all
about?
Q6. Table and column constraints enforce integrity
rules, which are:
1. Every table must have a Primary Key
2. Every Foreign Key must reference an exiting
Primary Key. (Referential Integrity Rule)
Go to top
Another way of stating the Referential Integrity Rule:
A table should never have an orphan record
(row).
A record is an orphan if it has a Foreign Key that references a non-exiting Primary Key. This means it’s a good idea to create
the parent table before you create the children tables. So easy to remember; Parents come before children!
Q7. How are Referential Integrity Constraints
enforced?
A7. In theory, one can implement one of the three
policies for each Foreign Key, which are:
1. CASCADE Policy -
If a record that is being referenced is DELETED then delete the referencing record as well.
If a Primary Key is UPDATED (changed) then update the corresponding Foreign Key as well.
2. RESTRICT Policy -
A record can not be DELETED if there is a Foreign Key referencing it.
A Primary Key can not be UPDATED if there is a Foreign Key referencing it.
A record with a Foreign Key can not be INSERTED (restrict insert) if it has a Foreign Key not referencing an exiting Primary Key.
3. DEFAULT Policy -
If a record that is being referenced is DELETED then set the corresponding Foreign Key to some default value, usually NULL.
If a Primary Key is UPDATED then set the corresponding Foreign Key to a default value, usually to NULL.
Q8. How do you implement the above policies in
practice?
A8. Some of the policies are implemented with
constraints when a table is created but most vendors do not support all three
policies
See Oracle
Constraints, go to MS SQL Server Constraints
Documentation.
Q9. Can you give me an example?
A9. CREATE TABLE SPJ
( sNo text(5) DEFAULT ‘S1’,
pNo text(5) DEFAULT ‘P1’,
jNo text(5) DEFAULT ‘J1’,
qty double CHECK ( qty>=0 and
qty<10000),
CONSTRAINT shipPK Primary Key (sNo, pNo, jNo) ,
CONSTRAINT SFK FOREIGN KEY(sNo) REFERENCES S(sNo)
ON UPDATE CASCADE , /* not supported by Oracle */
CONSTRAINT PFK FOREIGN KEY(pNo) REFERENCES P(pNo)
ON DELETE SET DEFAULT ‘P1’ /* not supported by Oracle */
ON UPDATE CASCADE ,
CONSTRAINT JFK FOREIGN KEY(jNo) REFERENCES J(jNo)
ON DELETE SET DEFAULT NULL /* not supported by Oracle */
ON UPDATE CASCADE ) ; /* not supported by Oracle */
Restrict policy is the default policy, so
there is no need to say ON DELETE RESTRICT.
For example CONSTRAINT JFK FOREIGN KEY(jNo) REFERENCES J(jNo)
will restrict deleting a parent record in J table that has a child record in
SPJ table, and restrict inserting a child record into SPJ table that has no
parent record in the J table, and will restrict updating either the parent’s
primary key or the child’s foreign key if that update will create an orphan
record. The bottom line is that any DELETE or
UPDATE will be restricted (not permitted) if the result yields an orphan
record.
WARNING: At the time if this writing Oracle only
supports CASCADE but not DEFAULT in SQL.
Another example:
CREATE TABLE S
( sNo CHAR(50) PRIMARY KEY,
sName
VARCHAR2(20),
status
NUMBER(5) CHECK( status >0 and status <= 30000)
city
VARCHAR2(20) CHECK (city IN (
‘Paris’, ‘London’, ‘Athens’, ‘New York’, ‘Newark’) ) ) ;
Q10 Since the tools we use do not support all of
the above policies, how can they be enforced?
A10. Usually by writing triggers. Triggers are
store procedures invoked by, in this case, a delete or update or insert
command.
Q11. How can we modify a table that has been
created?
A11. You can use DROP TABLE and then create a new
one or use ALTER TABLE command. ALTER TABLE is not supported in SQL by MS
Access. For example:
DROP TABLE
P; Will drop the table and all the
data in it, so use it with caution.
For Microsoft SQL Server CREATE and
CONSTRAINT documentation click: SQLserver
Back to top
The following should work in Oracle:
Q12. How do you DROP a table that has children
records?
A12. You may have to DROP or DISABLE the FOREIGN
KEY CONSTRAINTS first.
For example:
ALTER TABLE spj DISABLE CONSTRAINT pfk ;
To enable again :
ALTER TABLE spj ENABLE CONSTRAINT pfk ;
Q13. How do you DROP the FOREIGN KEY CONSTRAINTS?
A13. ALTER TABLE spj DROP CONSTRAINT
pfk ;
This will drop the PFK constraint in A10 . above.
Q14. How do you add a constraint as an after
though?
A14. ALTER TABLE SPJ
ADD CONSTRAINT SFK FOREIGN KEY (pNo) REFERENCES
P (pNo) ;
WARNING: Can’t do it if TABLE SPJ has references to
none existing pNo in TABLE P. No orphans allowed! Remember?
This will add a foreign key constraint to table
SPJ.
Q15. How do you drop a Primary Key?
A15. ALTER TABLE SPJ
DROP
Primary Key ;
Again you may have to drop or disable the FOREIGN
KEY CONSTRAINTS first before dropping the Primary Key.
Q16. How do you add a Primary Key to a table as
an after though?
A16. ALTER TABLE P
ADD Primary
Key (pNo) ;
This will add Primary Key constraint.
Q17. How do you change a data type in a column?
A17. ALTER TABLE P
MODIFY color CHAR(10) ;
This will change the color data type to CHAR(10).
Q18. Can you add a new column to a table?
A18. Yes
ALTER TABLE S
ADD fax CHAR(12) ;
This will add column fax to table S.
Q19. Can you drop a column in a table?
A19. Most vendors do not support dropping a
column. Microsoft Access and SQL Server
support dropping a column.