| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_primarykey | | 31 Aug 14 | Defects Dependencies |
1 2 /* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */ 3 /* 4.8 1.1 06/14/90 sproc/src/password */ 4 5 /* 6 ** Messages for "sp_primarykey" 17740 7 ** 8 ** 17390, "Table or view name must be in 'current' database." 9 ** 17492, "The table or view named doesn't exist in the current database." 10 ** 17740, "Only the owner of the table may define a primary key." 11 ** 17741, "Primary key already exists on table -- drop key first." 12 ** 17742, "The table has no such first column." 13 ** 17743, "The table has no such second column." 14 ** 17744, "The table has no such third column." 15 ** 17745, "The table has no such fourth column." 16 ** 17746, "The table has no such fifth column." 17 ** 17747, "The table has no such sixth column." 18 ** 17748, "The table has no such seventh column." 19 ** 17749, "The table has no such eighth column." 20 ** 17750, "New primary key added." 21 ** 17756, "The execution of the stored procedure '%1!' in database 22 ** '%2!' was aborted because there was an error in writing the 23 ** replication log record." 24 */ 25 26 /* 27 ** IMPORTANT NOTE: 28 ** This stored procedure uses the built-in function object_id() in the 29 ** where clause of a select query. If you intend to change this query 30 ** or use the object_id() or db_id() builtin in this procedure, please read the 31 ** READ.ME file in the $DBMS/generic/sproc directory to ensure that the rules 32 ** pertaining to object-id's and db-id's outlined there, are followed. 33 */ 34 35 create procedure sp_primarykey 36 @tabname varchar(767), /* table name that has the key */ 37 @col1 varchar(255), /* columns that make up the key */ 38 @col2 varchar(255) = NULL, 39 @col3 varchar(255) = NULL, 40 @col4 varchar(255) = NULL, 41 @col5 varchar(255) = NULL, 42 @col6 varchar(255) = NULL, 43 @col7 varchar(255) = NULL, 44 @col8 varchar(255) = NULL 45 as 46 47 declare @uid int /* owner id of the object */ 48 declare @cnt smallint /* howmany columns in key */ 49 declare @key1 smallint /* colids of the columns in the key */ 50 declare @key2 smallint 51 declare @key3 smallint 52 declare @key4 smallint 53 declare @key5 smallint 54 declare @key6 smallint 55 declare @key7 smallint 56 declare @key8 smallint 57 declare @msg varchar(1024) 58 declare @dbname varchar(255) 59 60 61 if @@trancount = 0 62 begin 63 set chained off 64 end 65 66 set transaction isolation level 1 67 68 /* 69 ** Check to see that the tabname is local. 70 */ 71 if @tabname like "%.%.%" and 72 substring(@tabname, 1, charindex(".", @tabname) - 1) != db_name() 73 begin 74 /* 75 ** 17390, "Table or view name must be in 'current' database." 76 */ 77 raiserror 17390 78 return (1) 79 end 80 81 /* 82 ** See if we can find the object. It must be a system table, user table, 83 ** or view. The low 3 bits of sysobjects.sysstat indicate what the 84 ** object type is -- it's more reliable than using sysobjects.type which 85 ** could change. 86 */ 87 if not exists (select * 88 from sysobjects 89 where id = object_id(@tabname) 90 and (sysstat & 7 = 1 /* system table */ 91 or sysstat & 7 = 2 /* view */ 92 or sysstat & 7 = 3)) /* user table */ 93 begin 94 /* 95 ** 17492, "The table or view named doesn't exist in the current database." 96 */ 97 raiserror 17492 98 return (1) 99 end 100 101 /* 102 ** Check to see that object owner is adding the key. 103 */ 104 select @uid = uid 105 from sysobjects 106 where id = object_id(@tabname) 107 and (sysstat & 7 = 1 /* system table */ 108 or sysstat & 7 = 2 /* view */ 109 or sysstat & 7 = 3) /* user table */ 110 if @uid != user_id() 111 begin 112 /* 113 ** 17740, "Only the owner of the table may define a primary key." 114 */ 115 raiserror 17740 116 return (1) 117 end 118 119 /* 120 ** See if the primary key exists already. Syskeys.type = 1 indicates 121 ** a primary key. 122 */ 123 if exists (select * 124 from syskeys 125 where id = object_id(@tabname) 126 and type = 1) 127 begin 128 /* 129 ** 17741, "Primary key already exists on table -- drop key first." 130 */ 131 raiserror 17741 132 return (1) 133 end 134 135 /* 136 ** Now check out each column argument to verify it's existence. 137 */ 138 select @cnt = 1, @key1 = colid 139 from syscolumns 140 where name = @col1 141 and id = object_id(@tabname) 142 if @key1 is NULL 143 begin 144 /* 145 ** 17742, "The table has no such first column." 146 */ 147 raiserror 17742 148 return (1) 149 end 150 151 if @col2 is not NULL 152 begin 153 select @cnt = @cnt + 1, @key2 = colid 154 from syscolumns 155 where name = @col2 156 and id = object_id(@tabname) 157 if @key2 is NULL 158 begin 159 /* 160 ** 17743, "The table has no such second column." 161 */ 162 raiserror 17743 163 return (1) 164 end 165 end 166 else goto doinsert 167 168 if @col3 is not NULL 169 begin 170 select @cnt = @cnt + 1, @key3 = colid 171 from syscolumns 172 where name = @col3 173 and id = object_id(@tabname) 174 if @key3 is NULL 175 begin 176 /* 177 ** 17744, "The table has no such third column." 178 */ 179 raiserror 17744 180 return (1) 181 end 182 end 183 else goto doinsert 184 185 if @col4 is not NULL 186 begin 187 select @cnt = @cnt + 1, @key4 = colid 188 from syscolumns 189 where name = @col4 190 and id = object_id(@tabname) 191 if @key4 is NULL 192 begin 193 /* 194 ** 17745, "The table has no such fourth column." 195 */ 196 raiserror 17745 197 return (1) 198 end 199 end 200 else goto doinsert 201 202 if @col5 is not NULL 203 begin 204 select @cnt = @cnt + 1, @key5 = colid 205 from syscolumns 206 where name = @col5 207 and id = object_id(@tabname) 208 if @key5 is NULL 209 begin 210 /* 211 ** 17746, "The table has no such fifth column." 212 */ 213 raiserror 17746 214 return (1) 215 end 216 end 217 else goto doinsert 218 219 if @col6 is not NULL 220 begin 221 select @cnt = @cnt + 1, @key6 = colid 222 from syscolumns 223 where name = @col6 224 and id = object_id(@tabname) 225 if @key6 is NULL 226 begin 227 /* 228 ** 17747, "The table has no such sixth column." 229 */ 230 raiserror 17747 231 return (1) 232 end 233 end 234 else goto doinsert 235 236 if @col7 is not NULL 237 begin 238 select @cnt = @cnt + 1, @key7 = colid 239 from syscolumns 240 where name = @col7 241 and id = object_id(@tabname) 242 if @key7 is NULL 243 begin 244 /* 245 ** 17748, "The table has no such seventh column." 246 */ 247 raiserror 17748 248 return (1) 249 end 250 end 251 else goto doinsert 252 253 if @col8 is not NULL 254 begin 255 select @cnt = @cnt + 1, @key8 = colid 256 from syscolumns 257 where name = @col8 258 and id = object_id(@tabname) 259 if @key8 is NULL 260 begin 261 /* 262 ** 17749, "The table has no such eighth column." 263 */ 264 raiserror 17749 265 return (1) 266 end 267 end 268 269 /* 270 ** Type 1 is a primary key, 2 is a foreignkey, and 3 is a commonjoin. 271 */ 272 doinsert: 273 274 /* 275 ** This transaction also writes a log record for replicating the 276 ** invocation of this procedure. If logexec() fails, the transaction 277 ** is aborted. 278 ** 279 ** IMPORTANT: The name rs_logexec is significant and is used by 280 ** Replication Server. 281 */ 282 begin transaction rs_logexec 283 284 insert into syskeys 285 (id, type, depid, keycnt, size, key1, key2, key3, key4, key5, 286 key6, key7, key8, spare1) 287 values (object_id(@tabname), 1, NULL, @cnt, 0, @key1, @key2, @key3, @key4, 288 @key5, @key6, @key7, @key8, 0) 289 290 if (@@error != 0) 291 begin 292 rollback transaction rs_logexec 293 return (1) 294 end 295 296 /* 297 ** Write the log record to replicate this invocation 298 ** of the stored procedure. 299 */ 300 if (logexec() != 1) 301 begin 302 /* 303 ** 17756, "The execution of the stored procedure '%1!' in 304 ** database '%2!' was aborted because there was an 305 ** error in writing the replication log record." 306 */ 307 select @dbname = db_name() 308 raiserror 17756, "sp_primarykey", @dbname 309 310 rollback transaction rs_logexec 311 return (1) 312 end 313 314 commit transaction 315 316 /* 317 ** 17750, "New primary key added." 318 */ 319 exec sp_getmessage 17750, @msg output 320 print @msg 321 322 return (0) 323
exec sp_procxmode 'sp_primarykey', 'AnyMode' go Grant Execute on sp_primarykey to public go
| DEPENDENCIES |
| PROCS AND TABLES USED read_writes table sybsystemprocs..syskeys calls proc sybsystemprocs..sp_getmessage  reads table sybsystemprocs..sysusermessages reads table master..sysmessages (1)  reads table master..syslanguages (1) calls proc sybsystemprocs..sp_validlang reads table master..syslanguages (1) reads table sybsystemprocs..syscolumns reads table sybsystemprocs..sysobjects |
