Hello
This mail is not about bugs or use but about concepts, so excuse me if
it is a bit boring.
I'm developing JDBC/SQL92 test, and working with SAPDB and other
databases.
When testing the DDL statements, I loose some time with the way SAPDB
understand constraints. The term constraints in SQL92 standard and the most
known databases is a more general one than in SAPDB, and can confuse when
reading the SAPDB manual, and I think has some negative implications in the
way the SAPDB works.
Let me compare DB2, Oracle, SQL92 and SAPDB about that.
In SQL92 there are four types of constraints, UNIQUE, CHECK, PRIMARY and
FOREIGN. I think this is the usual meaning of constraint for people that
works with databases. The statements to add and drop constraints in a table
was the same for all the constraints
ALTER TABLE ADD CONSTRAINT <constraint name><constraint definition>
ALTER TABLE ADD <constraint definition>
ALTER TABLE DROP CONSTRAINT <constraint name>.
In Oracle the types of constraints are UNIQUE, PRIMARY KEY, FOREIGN KEY
and CHECK for tables and UNIQUE, PRIMARY KEY, REFERENCES, CHECK and NOT NULL
for columns. The statements to add and drop constraints are the one from
SQL92 plus some non standard ones.
In DB2 there are three types of constraints UNIQUE (unique and primary
keys), CHECK and REFERENTIAL (foreign) and the syntax specification uses the
term constraint for all. The statements to add and drop constraints are the
one from SQL92 plus some non standard ones.
In SAPDB there are not a general term for constraints instead it uses
four different terms for constraints (SQL92 concept), CONSTRAINT(check),
REFERENTIAL CONSTRAINT (foreign), UNIQUE and KEY/PRIMARY KEY (primary). The
statements for the constraints are not the same for all constraints:
ALTER TABLE ADD CONSTRAINT <check_constraint name><check_constraint
definition>
ALTER TABLE ADD <check_constraint_definition>
ALTER TABLE ADD <referential_constraint_definition>
ALTER TABLE ADD <key_definition>
ALTER TABLE DROP CONSTRAINT <check_constraint_name>
ALTER TABLE DROP PRIMARY KEY
When adding a constraint to a table or dropping it not all the
constraints (SQL92 concept) work.
ADD CHECK work
ADD UNIQUE didn't work
ADD PRIMARY KEY work
ADD FOREIGN KEY work.
ADD CONSTRAINT <name> CHECK work
ADD CONSTRAINT <name> UNIQUE didn't work
ADD CONSTRAINT <name> PRIMARY KEY didn't work
ADD CONSTRAINT <name> FOREIGN KEY didn't work
DROP CONSTRAINT <check constraint name> work
DROP CONSTRAINT <primary constraint name> don't work
DROP CONSTRAINT <foreign constraint name> don't work
DROP CONSTRAINT <unique constraint name> don't work
Perhaps somewhere in the future the syntax can be simplified and
completed.
Regards
Blas Rodriguez Somoza
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
