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]
