+5
Started

Ability to generate FK, PK Constraints as ALTER TABLE statements rather than CREATE TABLE

kurtpeterson 3 years ago updated 3 years ago 4

I don't see a way to generate constraints that utilize the ALTER TABLE DDL rather than CREATE TABLE.  Creating alter table DDL at the end of the CREATE table script would make it easier to manage the creation of tables first and then apply the constraints.

Under review

It is not available as of now. may be it is good idea to have that option.


But I think this is already covered in the tool. In case constraints are applied later then user can always compare revisions and generate Alter SQL script". 

Would that help?

Thanks,

Team - SqlDBM

+1

The advantage of applying the FK constraints after the tables are created is that you can create the tables in any order (along with PK's).   Once the tables have been created, the FK's can then also be applied in any order.  

Is this already a part of SqlDBM and I'm missing it?

Started

SqlDBM is capable of ordering tables in right order based on the dependencies, so user need not to worry about that.

What if I don't want to apply the constraints, but apply them later, after I have loaded data?  Having this option would allow me more flexibility.  This is something that is done all the time when working with RDMS systems.

CREATE TABLE "T_PORTFOLIOS" (
"ID" varchar ,
"NAME" varchar(1020) NOT NULL ,
"DESCRIPTION" varchar(4000) ,
"OWNERID" varchar ,
"ENTEREDBYID" varchar ,
"LASTUPDATEDBYID" varchar ,
"ENTRYDATE" varchar NOT NULL ,
"LASTUPDATEDONDATE" varchar ,
"CATEGORYID" varchar ,
"OLV" varchar DEFAULT 0 NOT NULL ,
"ALIGNMENTSCORECARDID" varchar ,
"EXTREFID" varchar(1020) ,
"ISACTIVE" varchar DEFAULT 1 NOT NULL ,
"HASNOTES" varchar DEFAULT 0 NOT NULL ,
"HASDOCUMENTS" varchar DEFAULT 0 NOT NULL ,
"AUDITTYPES" varchar DEFAULT 2 NOT NULL ,
"HASMSGS" varchar DEFAULT 0 NOT NULL ,
"BUDGET" varchar(18) ,
"CUSTOMERGUID" varchar(128) NOT NULL ,
"GUID" varchar(128) NOT NULL ,
"ENTEREDBYGUID" varchar(128) ,
"LASTUPDATEDBYGUID" varchar(128) ,
"OWNERGUID" varchar(128) ,
"ALIGNMENTSCORECARDGUID" varchar(128) ,
"CATEGORYGUID" varchar(128) ,
"DELETED" char(4) ,
"DELETIONDATE" timestamp ,
"RECYCLETOKEN" varchar(128) ,
CONSTRAINT "PK_T_PORTFOLIOS" PRIMARY KEY ( "GUID" )
);

CREATE TABLE "T_PARAMETERVALUES" (
"ID" varchar ,
"PARAMETERNAME" varchar(800) NOT NULL ,
"PARAMETERID" varchar ,
"OBJCODE" varchar(24) NOT NULL ,
"OBJID" varchar ,
"DATEVAL" varchar ,
"TEXTVAL" varchar(4000) ,
"NUMBERVAL" varchar(26) ,
"OLV" varchar DEFAULT 0 NOT NULL ,
"OBJGUID" varchar(128) NOT NULL ,
"PARAMETERGUID" varchar(128) NOT NULL ,
"GUID" varchar(128) NOT NULL ,
"PROGRAMGUID" varchar(128) ,
"PROJECTGUID" varchar(128) ,
"MASTERTASKGUID" varchar(128) ,
"OPTASKGUID" varchar(128) ,
"TEMPLATETASKGUID" varchar(128) ,
"TEMPLATEGUID" varchar(128) ,
"USERGUID" varchar(128) ,
"TASKGUID" varchar(128) ,
"PORTFOLIOGUID" varchar(128) ,
"DOCUMENTGUID" varchar(128) ,
"COMPANYGUID" varchar(128) ,
"EXPENSEGUID" varchar(128) ,
"CUSTOMERGUID" varchar(128) ,
"ITERATIONGUID" varchar(128) ,
"REFUSERGUID" varchar(128) ,
"REFTEAMGUID" varchar(128) ,
"REFCOMPANYGUID" varchar(128) ,
"REFGROUPGUID" varchar(128) ,
"REFROLEGUID" varchar(128) ,
"REFPORTFOLIOGUID" varchar(128) ,
"REFPROGRAMGUID" varchar(128) ,
"REFPROJECTGUID" varchar(128) ,
"REFTEMPLATEGUID" varchar(128) ,
CONSTRAINT "PK_T_PARAMETERVALUES" PRIMARY KEY ( "GUID" )
);


ALTER TABLE "T_PARAMETERVALUES" ADD CONSTRAINT "FK_PARAMETERVALUES_REFPORTGUID" FOREIGN KEY ( "REFPORTFOLIOGUID" ) REFERENCES "T_PORTFOLIOS"( "GUID" ) ON UPDATE CASCADE;

ALTER TABLE "T_PARAMETERVALUES" ADD CONSTRAINT "FK_PARAMVALS_PORTFOLIOGUID" FOREIGN KEY ( "PORTFOLIOGUID" ) REFERENCES "T_PORTFOLIOS"( "GUID" ) ON UPDATE CASCADE;