Basic SQL DDL Statements

[Kokujin]

This is going to be a very simple question:
I use Access 2000 with Jet Engine version 4 I wrote the following SQL
statements based on my Text Book....However I could not run the Second
table...
(1st Table)
CREATE TABLE VENDOR
([V_CODE] INTEGER NOT NULL UNIQUE,
[V_NAME] VARCHAR(35) NOT NULL,
[V_CONTACT] VARCHAR(15) NOT NULL,
[V_AREACODE] FCHAR(13) NOT NULL,
[V_PHONE] FCHAR(8) NOT NULL,
[V_STATE] FCHAR(2) NOT NULL,
[V_ORDER] FCHAR(1) NOT NULL,
PRIMARY KEY( [V_CODE]) );

(2nd Table)
CREATE TABLE PRODUCT
(P_CODE CHAR(10) NOT NULL UNIQUE,
P_DESCRIPT CHAR(35) NOT NULL,
P_INDATE DATE NOT NULL,
P_ONHAND SMALLINT NOT NULL,
P_MIN SMALLINT NOT NULL,
P_PRICE DECIMAL(8,2) NOT NULL
P_DISCOUNT DECIMAL(5,2) NOT NULL,
V_CODE INTERGER,

PRIMARY KEY(P_CODE),
FOREIGN KEY(V_CODE) REFERENCES VENDOR
ON DELETE RESTRICT
ON UPDATE CASCADE);

It seems that the way SQL is implemented with Access 2000 4.0, There
are some Reserved words that are not understood by the "JET" engine.
Such as the "ON DELETE RESTRICT" clauses. Therefore the questions is
How do I using SQL as implemented with Access 2000 4.0 Enforce
Referential Intergrity ? Are there new Words and Syntax "these" days ?
Do I have to use the "Constraint" word to define PKeys and FKeys ?
What is the best SQL dialect to learn these days and should I just
convert to SQL Server ? Yeah I know that "DECIMAL" should be replaced
by "CURRENCY". Thanks for any Help.

 

[Granny Spitz via AccessMonster.com]

This is going to be a very simple question

Actually it's 5 very simple questions.

However I could not run the Second
table...

Hold on, let's back up to the first table's syntax. Jet doesn't recognize
the FCHAR data type, so you'll need to change that to CHAR. I know this next
part is in your text book, but in real life you don't want to place a unique
index on a column (V_CODE) and then place a primary key constraint on that
same column. You only need the primary key constraint in Jet. And when you
declare a primary key constraint, you want to give it a name in Jet. Why?
Because Jet will assign it's own internal name and if you ever need to refer
to it later in code, like when you want to drop it, you'll have to look it up
first to find out it is, like this:

ALTER TABLE VENDOR
DROP CONSTRAINT Index_48171710_055B_4AEA;

It would be much easier to maintain a table that had its primary key declared
on the last line of your SQL like this:

CONSTRAINT PrimaryKey PRIMARY KEY (V_CODE));

so that when you drop it, the code would be like this:

ALTER TABLE VENDOR
DROP CONSTRAINT PrimaryKey;

As a matter of fact, if you set the primary key through the GUI, Jet will
automatically assign PrimaryKey as the index name, so if you do this to all
your tables you create with SQL, they'll be consistent. Now to your second
table. Integer is misspelled in V_CODE INTERGER and you need a comma after
P_PRICE DECIMAL(8,2) NOT NULL

It seems that the way SQL is implemented with Access 2000 4.0, There
are some Reserved words that are not understood by the "JET" engine.
Such as the "ON DELETE RESTRICT" clauses.

Jet 4.0 doesn't recognize the "ON DELETE RESTRICT" clause, and Jet in Access
2000 doesn't recognize "ON UPDATE CASCADE" and "ON DELETE CASCADE" in the SQL
view window either. In newer versions of Access, a database setting will
allow "ON UPDATE CASCADE" and "ON DELETE CASCADE" in the SQL view window.
The workaround is to use ADO code in a VBA module.

Therefore the questions is
How do I using SQL as implemented with Access 2000 4.0 Enforce
Referential Intergrity ?

You need to set up a foreign key constraint to enforce referential integrity.
But that's not all. Both tables must reside in the same database file. The
syntax is like this:

ALTER TABLE tblProducts
ADD CONSTRAINT tblSupplierstblProducts_FK
FOREIGN KEY (SupID) REFERENCES tblSuppliers SupID;

Are there new Words and Syntax "these" days ?

Not really. ANSI SQL-92 (AKA SQL 2) is the most recent standard, so it's
about 14 years old. Most database vendors have their own SQL dialects, so
every time a new release comes out it gives them an opportunity to come
closer to the ANSI SQL-92 standard and add some more to their own dialect.

Do I have to use the "Constraint" word to define PKeys and FKeys ?

No, but you should use it so that you can name the constraint.

What is the best SQL dialect to learn these days

Whatever dialect your employer is using.

should I just
convert to SQL Server ?

It's easier to learn SQL in Jet first. SQL Server requires more disk space
and it's either *always running,* or you have to start the process before you
can use the database.

 

[Jamie Collins]

ANSI SQL-92 (AKA SQL 2) is the most recent standard

You've forgotten SQL-99 and SQL-2003. See:

http://developer.mimer.com/validator/index.htm

Whatever dialect your employer is using.

The best approach IMO is to learn standard SQL... or at least learn
where you employer's SQL product's implementation deviates from the
standards ... OK at the *very* least learn which of the SQL syntax you
use are based on proprietary syntax. The Mimer SQL validators (see
above link) are great for this.

Whenever I see someone using SELECT TOP N as if it were standard SQL, I
think of the quote Joe Celko uses: "He is a barbarian and thinks the
customs of his tribe and island are the laws of nature." - Caesar and
Cleopatra; George Bernard Shaw 1898.

It's easier to learn SQL in Jet first.

I can't agree, simply because Jet is not entry level SQL-92 compliant.
Something as simple as

UPDATE mytable SET mycolumn = (<scalar subquery>)

fails in Jet, which has an unsafe proprietary UPDATE..JOIN syntax all
of its own, not even the same as SQL Server's unsafe proprietary
UPDATE..JOIN syntax <g> but at least SQL Server supports the standard
construct (and in SQL Server 2005 you can use a CTE rather than lots of
similar-but-not-quite-the-same scalar subqueries, as you would have to
in previous versions).

Jet in Access
2000 doesn't recognize "ON UPDATE CASCADE" and "ON DELETE CASCADE" in the SQL
view window either. In newer versions of Access, a database setting will
allow "ON UPDATE CASCADE" and "ON DELETE CASCADE" in the SQL view window.

Don't forget Jet's ON DELETE SET NULL. Microsoft also tries to claim
they have invented ON UPDATE SET NULL (see
http://support.microsoft.com/default.aspx?scid=kb;en-us;275561) but it
turns out that's a fallacy <g>.

Jamie.

--

 

[aaron.kempf]

I disagree.

it is not easier to learn SQL in jet.

JET is a piece of shit ass language and anyone that uses it is a
friggin leper and you should spit on them.

If you need newbie-design view-- use Access Data Projects.

long term-- everyone needs to run SQL. Access MDB just isn't reliable
enough for a single user and a single record.

I reccomend that you learn 1 dialect of SQL instead of 2 and you stay
focused.

additionally; the educational resources that are available for SQL
Sever vs the resources for Access?

everybody in the world knows that Access is for friggin babies; and
it's nearly impossible to find CHALLENGING mdb development classes for
example.

but SQL Server? I've seen SQL Server classes in a hundred different
places.

simplify. One Dialect of SQL.

Spit on anyone that tells you otherwise.

-Aaron