The ACLPermission table has a Compound PK (ACLPrincipleID &
ACLPermissionID )
The reason for this design is becuase there is a ACLRole table as well so..
[ACLRole] [ACLPrinciple]
PK PK
[ACLRolePermission] [ACLPrinciplePermission]
FK_ACLRole FK_ACLPrinciple
FK_ACLPermission FK_ACLPermission
[ACLPermission]
PK [PK]
PK
[ACLResource]
PK
FK_ACLPermission
is the full thing.
I figured it out anyway, stupid mistake..bad data...
Thanks Justin
-----Original Message-----
From: Robertson-Ravo, Neil (RX)
[mailto:[EMAIL PROTECTED]
Sent: 12 September 2003 14:29
To: '[EMAIL PROTECTED]'
Subject: RE: [ cf-dev ] OT: SQL server constraint question
Well,
The problems I can see straightawat is that your ACLPersmission table does
not have a PK itself (even as a lookup table, it should have one). Your
schema is not normalised in that you have explicitly tied both the
ACLprincipleID to both tables, therefore you cannot insert a record in your
lookup table which does not have a corresponding parent id : it wouldnt make
any sense.
N
-----Original Message-----
From: Justin MacCarthy [mailto:[EMAIL PROTECTED]
Sent: 12 September 2003 13:30
To: Cfuk
Subject: [ cf-dev ] OT: SQL server constraint question
Hi all,
In SQL server 7, I got the TABLES
[ACLPrincipleID]
ACLPrincipleID [pk] [autonumber]
ACLUser
[ACLPermission]
ACLPrincipleID [ck][fk ACLPrincipleID.ACLPrincipleID]
ACLPermissionID [ck][fk ACLPrinciplePermission.ACLPermissionID]
[ACLPrinciplePermission]
ACLPermissionID [pk] [autonumber]
ACLResourceID
ACLPermission
In a stored Proc I want to add a permission for a user, but I can't add the
permissions first as it breaks the constraint,and I can't add the record to
the ACLPermission table first because I need the ACLPermissionID that is
generated.
Is there a way to get SQL server to only check the constraint at the end?
I'm stuck with the [autoincrement] PK and this db design for now.
I have some ideas I don't like too much...
What is the best way to do it?
TIA
Justin
--
** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
For human help, e-mail: [EMAIL PROTECTED]
--
** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
For human help, e-mail: [EMAIL PROTECTED]
--
** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
For human help, e-mail: [EMAIL PROTECTED]
