Rank: Newbie
Joined: 4/25/2008
Posts: 8
Location: Germany
|
Hi,
I imported a DB design, marked all, and selected the generation of SQL code for create. But what was generated is not usable. :-(
If U would try to use this code (in this case for Access 2007) it would fail.
Code:CREATE TABLE
[Addresses] (
[TableIndex] COUNTER NOT NULL,
[Street] TEXT (255) NULL,
[HouseNumber] TEXT (255) NULL,
[ZIP] TEXT (255) NULL,
[City] TEXT (255) NULL,
[Country] INT NULL,
[AreaCode] TEXT (255) NULL,
CONSTRAINT [PrimaryKey] PRIMARY KEY ([TableIndex]) ,
CONSTRAINT [CountriesAddresses] FOREIGN KEY ([Country]) REFERENCES
[Countries] ([TableIndex]) ) ;
If U would try to add such table and the references would have to be created to, this creation would fail.
The references between the different tables when more than one table is marked is ignored, and the existing descriptions of the columns are ignored too.
Better would be what DeZign generates (in this case for Firebird 2). It generates first the following.
Code:CREATE TABLE ADDRESSES (
AUTOINDEX INTEGER NOT NULL,
STREET VARCHAR(255),
HOUSENUMBER VARCHAR(255),
ZIP VARCHAR(255),
CITY VARCHAR(255),
COUNTRY INTEGER,
AREACODE VARCHAR(255),
CONSTRAINT PK_ADDRESSES PRIMARY KEY (AUTOINDEX)
);
CREATE INDEX AREACODE ON ADDRESSES (AREACODE);
CREATE INDEX CITY ON ADDRESSES (CITY);
CREATE INDEX ZIP ON ADDRESSES (ZIP);
UPDATE RDB$RELATION_FIELDS SET RDB$DESCRIPTION = 'Primärindex der Addresses Tabelle.' WHERE (RDB$RELATION_NAME = 'ADDRESSES') AND (RDB$FIELD_NAME = 'AUTOINDEX');
UPDATE RDB$RELATION_FIELDS SET RDB$DESCRIPTION = 'Straße' WHERE (RDB$RELATION_NAME = 'ADDRESSES') AND (RDB$FIELD_NAME = 'STREET');
UPDATE RDB$RELATION_FIELDS SET RDB$DESCRIPTION = 'Hausnummer' WHERE (RDB$RELATION_NAME = 'ADDRESSES') AND (RDB$FIELD_NAME = 'HOUSENUMBER');
UPDATE RDB$RELATION_FIELDS SET RDB$DESCRIPTION = 'Postleitzahl' WHERE (RDB$RELATION_NAME = 'ADDRESSES') AND (RDB$FIELD_NAME = 'ZIP');
UPDATE RDB$RELATION_FIELDS SET RDB$DESCRIPTION = 'Stadt' WHERE (RDB$RELATION_NAME = 'ADDRESSES') AND (RDB$FIELD_NAME = 'CITY');
UPDATE RDB$RELATION_FIELDS SET RDB$DESCRIPTION = 'FK_Countries' WHERE (RDB$RELATION_NAME = 'ADDRESSES') AND (RDB$FIELD_NAME = 'COUNTRY');
UPDATE RDB$RELATION_FIELDS SET RDB$DESCRIPTION = 'Vorwahl' WHERE (RDB$RELATION_NAME = 'ADDRESSES') AND (RDB$FIELD_NAME = 'AREACODE');
And at the end of the generation of all of the tables the following.
Code:ALTER TABLE ADDRESSES ADD CONSTRAINT COUNTRIESADDRESSES
FOREIGN KEY (COUNTRY) REFERENCES COUNTRIES (AUTOINDEX);
Remarking greetings
Reiner
|
Rank: Newbie
Joined: 3/29/2010
Posts: 1
Location: NY
|
hi i must say its a very nice post keep it up this kind of good work
|
