Re: M1 Security Schema MSSQL Constraints

[Randy Watler]

I believe that changes to security-schema.xml on 12/3 just before the M1 
release that included adding "on delete cascade" to these foreign keys 
is what broke the MSSQL configuration for me. While I believe these 
constraints are appropriate schema wise, I am afraid they are hitting a 
MS SQL Server 2000 limitation. Fixing this probably involves customizing 
Torque... something Ate is pondering replacing... not sure it is worth 
the trouble.

If we want to keep the cascading deletes, a Simpler fix might involve 
splitting the usage of the SECURITY_PRINCIPAL into its separate usages, 
(i.e. USER_PRINCIPAL, ROLE_PRINCIPAL, GROUP_PRINCIPAL, and 
REMOTE_PRINCIPAL). This way, the cascading delete will not attempt to 
visit the same table twice. Will this change cause problems in the admin 
portlets?

Any thoughts on preserving the SECURITY_PRINCIPAL heterogeneous mapping 
vs. cascading deletes? Does anyone want to even consider extending Torque?

Randy
Randy Watler wrote:
Guys,
Apparently, multiple foreign keys to a single related table with an ON 
DELETE CASCADE constraint is not supported in MS SQL Server by design. 
It requires use of yet another trigger to implement. Unfortunately, 
this is not the first time I have been told to use a trigger for basic 
constraints, (such as ON DELETE SET NULL),  in MS SQL Server, >:-(.

I am no SQL Server expert, so does anyone else care to attempt this 
before I do?

Randy
rwatler wrote:
David/Ate/Scott,
While executing the M1 scripts for DB configuration of MS SQL Server 
2K, I ended up with these errors:

db.execute:
    [echo] Executing scripts for ./sql/DDL/mssql/security-schema.sql
     [sql] Executing file: 
/usr/share/tomcat5/jetspeed-database/scripts/sql/DDL/mssql/security-schema.sql 

     [sql] Failed to execute:   BEGIN ALTER TABLE 
SSO_PRINCIPAL_TO_REMOTE ADD CONSTRAINT SSO_PRINCIPAL_TO_REMOTE_FK_2 
FOREIGN KEY (REMOTE_PRINCIPAL_ID) REFERENCES SECURITY_PRINCIPAL 
(PRINCIPAL_ID) ON DELETE CASCADE END

BUILD FAILED
/usr/share/tomcat5/jetspeed-database/scripts/build.xml:64: The 
following error occurred while executing this line:
/usr/share/tomcat5/jetspeed-database/scripts/build.xml:38: 
java.sql.SQLException: Introducing FOREIGN KEY constraint 
'SSO_PRINCIPAL_TO_REMOTE_FK_2' on table 'SSO_PRINCIPAL_TO_REMOTE' may 
cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION 
or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

...

---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]