Chris Doyle wrote:
While we're on the subject, I was curious if it might be useful to apply a primary key definition to the UP_PERMISSION table? I see that there is an index defined (UPPERM_IDX), but didn't know if there was a good reason for not defining a primary key. The only condition I can think of is if someone wanted to define a series of timed permissions via the EFFECTIVE/EXPIRES columns. However, if there is no reason, might we consider the following?---- 1) Add <not-null> to the following columns: OWNER PRINCIPAL_TYPE PRINCIPAL_KEY ACTIVITY TARGET PERMISSION_TYPE 2) Resize the following columns: (*** SEE NOTE BELOW ***) ACTIVITY - VARCHAR(64) PERMISSION_TYPE - VARCHAR(64) 3) Apply the following as the <primary-key> definition: OWNER PRINCIPAL_TYPE PRINCIPAL_KEY ACTIVITY TARGET PERMISSION_TYPE *** NOTE *** There is a size restriction of 900 bytes for the definition of a primary key in MS SQL Server. Though it "allows" for a primary key definition greater than 900 bytes, it will return the following "warning": "Warning! The maximum key length is 900 bytes. The index 'PK_UP_PERMISSION' has maximum length of 1279 bytes. For some combination of large values, the insert/update operation will fail." ---- Since both the ACTIVITY and PERMISSION_TYPE values are typically smaller, could we safely resize them down to VARCHAR(64), which would bring the primary key size down to just under 900 bytes? I'm not sure if other databases have similar restrictions, so it might be worth a little research. If applying the primary key does not make sense, then does it at least make sense to apply the <not-null> restrictions? Are there real cases where NULL values should be allowed in the aforementioned columns? Thoughts? --Chris
smime.p7s
Description: S/MIME Cryptographic Signature
