Re: Changes create.sql for spec-ref-no-pk

* Juan Sequeda <juanfederico@gmail.com> [2012-03-21 07:45-0500]
> Eric
> 
> create.sql for spec-ref-no-pk does not run automatically given that the
> table Department has a FK to People and People has a FK to Department.

hmm, <http://developer.mimer.com/validator/parser200x/index.tml> says [[
The following feature outside Core SQL-2003 is used:

F381, "Extended schema manipulation"

]]
Mimer could be mistaken, or ALTER...ADD FOREIGN KEY could have
migrated from an extension to core between 2003 and 2009. At least SQL
Server and postgresql enforce "Referencable Table" rules at CREATE
time so there seems to be no choice. Too bad, I liked the grouping
before.


> Can that be changed to use alter tables instead.
> 
> CREATE TABLE "Addresses" (
> "ID" INT,
> PRIMARY KEY("ID"),
>  "city" VARCHAR(10),
> "state" CHAR(2)
> );
> 
> CREATE TABLE "Department" (
> "ID" INT,
> PRIMARY KEY("ID"),
>  "name" VARCHAR(50),
> "city" VARCHAR(50),
> UNIQUE ("name", "city"),
>  "manager" INT
> );
> 
> CREATE TABLE "People" (
> "ID" INT,
>  PRIMARY KEY("ID"),
> "fname" VARCHAR(10),
> "addr" INT,
>  FOREIGN KEY ("addr") REFERENCES "Addresses"("ID"),
> "deptName" VARCHAR(50),
>  "deptCity" VARCHAR(50)
> );
> 
> ALTER TABLE "Department" ADD FOREIGN KEY("manager") REFERENCES
> "People"("ID");
> ALTER TABLE "People" ADD FOREIGN KEY("deptName", "deptCity") REFERENCES
> "Department"("name", "city");

weak, now i have to beef up my SQL parser.

> And another issue.. which seems to be a SQL Server: In the table Projects,
> which has a FK to Department, has deptName and deptCity with varchar(50)
> while the table Department uses varchar(10) for name and city. In Oracle,
> this isn't a problem, but in SQL Server, you need can only create foreign
> keys to attributes that have the exact datatype (you learn things every

Which seems quite reasonable. SQL Server++ for noticing.

SQL *could* say specify that the domain of the referer be a subclass
of the domain of the referred column, but that's perhaps needless
fiddling.


> day!!) In the sql snippet that I pasted above, I already changed the
> datatypes of name and city to varchar(50).
> 
> Thanks
> Juan Sequeda
> +1-575-SEQ-UEDA
> www.juansequeda.com

-- 
-ericP

Received on Wednesday, 21 March 2012 13:18:51 UTC