• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

mysql table

 
sujesh Katri
Ranch Hand
Posts: 115
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hello,
i wrote mysql data baste table but i don't wether it is correct or not..i'm familiar with oracle...i modify oracle table with slite difference ..please tell me wether i did right or wrong..if wrong, where i did mistake please tell me.
MY main doubt is on trigger table.

 
James Boswell
Bartender
Posts: 1051
5
Chrome Eclipse IDE Hibernate
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Have you tried this SQL out? What result did it give you?
 
Ron McLeod
Bartender
Pie
Posts: 1050
65
Android Eclipse IDE Java Linux MySQL Database Redhat
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
sujesh Katri wrote:...
USERNAME CHAR(4000),
PASSWORD CHAR(4000),
REPASSWORD CHAR(4000),
...

I don't know about the syntax, but the size of fields for name and password seem excessive. Are PASSWORD and REPASSWORD storing the same values? Is ADDRESS an email address, postal address, IP address?
 
Dave Tolls
Ranch Hand
Posts: 2112
16
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Not only do those look like overly large columns, using CHAR and not VARCHAR?

Here's the TRIGGER docs for 5.5.
More importantly you're using a sequence there, which don't (at least not in the same way) exist in MySQL.
Why not just make the ACCOUNT_NO column AUTO_INCREMENT?
 
sujesh Katri
Ranch Hand
Posts: 115
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
NEWACCOUNT table created successfull with small changes that i replaced char with varchar and while executing trigger table it shows error ..i'm placing that error below...SOME body said there is no sequences in the mysql database..i used oracle sequence in my below jdbc statement how can i replace that statement to produce correct result in mysql..i used the sequence in listener class and i'm placing my listener class also below.
jdbc statement:-
listener program:-
some body said replace that sequence with auto increment of newaccout column..how can i use in this jdbc program that autoincrement instead of sequence..my assumption i'm placing below..
PreparedStatement ps3=con.prepareStatement("CREATE table newaccount1(ACCOUNTNO auto_increment)");if this statement wrong how can i achieve that task..i myself doing simple bank project with newaccount module how can i create new account instead of using sequenc in mysql
 
sujesh Katri
Ranch Hand
Posts: 115
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
in the above mylistener class, i placed oracle syntax where as in my system i created with mysql syntax using netbeans environment..my main thing is trigger table replacement of sequence with mysql statement..i don't know how to write that code..suggest me how can i achive that newaccount module.
 
Dave Tolls
Ranch Hand
Posts: 2112
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I said use auto_increment.
You define the column as an AUTO_INCREMENT one, and that's that.
No trigger needed.

MySQL has very good documentation for all this.

Here's the 5.5 version.

You are making the fairly common mistake when translating code from one language to another by doing it in a one-for-one basis, and not actually going into how the other language works and what assumptions it makes.
In this case, MySQL comes equipped with something that renders the TRIGGER unnecessary.
 
sujesh Katri
Ranch Hand
Posts: 115
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Before inserting into my actual table,i have to get accountno and later i will insert all fields with accountno in my original table..i my concern i think it it is necessary to me triggers..my assumtions i'm placing blew..my original table is NEWACCOUNT.I'M placing that table first
BEFORE inserting values into above table i have to get ACCOUNTNO FROM another table..i'm placing that one also below i have to get ACCOUNTNO from newaccount1 before inserting into NEWACCOUNT TABLE ..i think i can achieve this only through triggers is n't it.
 
Dave Tolls
Ranch Hand
Posts: 2112
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
In JDBC there's a getGeneratedKeys method on the Statement class for getting hold of the generated ids.

Here's the MySQL documentation on using it to get the value of an AUTO_INCREMENTED column.

As I said before, you need to look into how MySQL works, not attempt to shoe horn it into the same flow as Oracle uses.
 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Add an extra column called ID and make it an AUTO_INCREMENT and use it as your primary key. This will then be populated automatically by the database when you create a record.

Define your ACCOUNTNO as a UNIQUE KEY and NOT NULL (assuming it must be populated), then populate this as appropriate for your business rules.

This approach means your primary key ID will always be populated and will always be unique, but you can decide when/how to populate (or change) the ACCOUNTNO according to your system's rules e.g. via a trigger. This artificial numeric key (ID here) is called a surrogate key and is a widely used technique in database systems.

It means the business rules for your Account Number do not affect your primary key, so your PK need never change (and the PK should not change anyway). This is useful e.g. if your bank merges with another one, maybe they'll add a prefix to the account numbers to keep them separate or avoid errors due to duplicates, so you would have to change the Account Number but the ID for each account (and any records referring to it via foreign keys) would stay the same.


 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic