Checking oracle key constraints

Results 1 to 2 of 2

Thread: Checking oracle key constraints

  1. #1
    Join Date
    Dec 1969

    Default Checking oracle key constraints

    I have a very simple table in my Oracle Database with just 2 columns - username and password. <BR><BR>The username column is the primary key and in my register page I am using the following SQL to insert new rows into the database: <BR><BR>[code language="C#"]<BR>string createUserSQL = "Insert into users(username, password)" + <BR> " values(@username, @password)"; <BR><BR>OleDbCommand cmd = new OleDbCommand(createUserSQL, conn); <BR>cmd.Parameters.Add("@username", tbUsername.Text); <BR>cmd.Parameters.Add("@password", tbPassword.Text); <BR>[/code]<BR><BR>However, I don&#039;t know how to check that there isn&#039;t already a user with that username. Is there a quicker way than querying the table to see if the username returns a row? <BR><BR>Thanks for your help, <BR><BR>Wallace

  2. #2
    Join Date
    Dec 1969

    Default Like many things there are many

    ways you can do this. ; )<BR><BR>One way would be to first do a SELECT to see if a record exists in the database then do your INSERT if it doesn&#039;t return any records.<BR><BR>Another way which I like better would be to write yourself a nice database stored procedure (or Oracle package..I suggest you look this one up if you are not familiar) to do it all for you.<BR><BR>To go a step further, I&#039;m sure you are going to have a login and where you are going to want to authenticate your users. So the nice thing would be to create yourself a set of functions and procedures that you can re-use.<BR><BR>The nice thing about this way is you are not doing multiple database hits from your web server and you can easily add/change all your security logic in one place. This is especially true as your logic becomes more complex (which it most likely will as you grow).<BR><BR>Obviously this might be a little advanced for you at this point, however I strongly suggest you learn about Oracle packages (and db procedures/functions). IMHO, if you are going to use a database like Oracle then take advantage of its features.<BR><BR>If you like, I&#039;d be happy to show you examples of this.<BR><BR>On a different note, you may want to add a couple fields to that user table like when the record was created (date/time) and other &#039;audit&#039; type fields. This is for security purposes so give yourself as much info for the future. ; )<BR><BR>Pete<BR><BR><BR><BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts