Gossamer Forum
Home : Products : DBMan SQL : Discussion :

Alex! about dbmansql

Quote Reply
Alex! about dbmansql
i am speaking about dbmansql !

do you know how to validate fields input when adding a record or modifying so the information in the tables cannot be repeatly stored with the same information in diferent id

i mean to prevent this

id | name | lastname | postion |

1 | a | a | a | ok

2 | a | a | a | not ok - (this row is not right or cannot be added)

3 | a | b | a | ok

if you see the 2 row is not right because cannot be the same person with equal name and lastname and position

can you help me with this so users cannot duplicate data!
Quote Reply
Re: [kevinws] Alex! about dbmansql In reply to

I answered this via email, but it might help someone else to have the answer here.

I think this can be done by creating an index, and make it unique on the three fields. According the the MySQL manual, you can, try the below:

6.5.7 CREATE INDEX Syntax


[/url][/url][/url]CREATE [UNIQUE|FULLTEXT] INDEX index_name ON tbl_name (col_name[(length)],... )

The CREATE INDEX statement doesn't do anything in MySQL prior to Version 3.22. In Version 3.22 or later, CREATE INDEX is mapped to an ALTER TABLE statement to create indexes. See section 6.5.4 ALTER TABLE Syntax.

Normally, you create all indexes on a table at the time the table itself is created with CREATE TABLE. See section 6.5.3 CREATE TABLE Syntax. CREATE INDEX allows you to add indexes to existing tables.

A column list of the form (col1,col2,...) creates a multiple-column index. Index values are formed by concatenating the values of the given columns.

For CHAR and VARCHAR columns, indexes can be created that use only part of a column, using col_name(length) syntax to index the first length bytes of each column value. (For BLOB and TEXT columns, a prefix length is required; length may be a value up to 255.) The statement shown here creates an index using the first 10 characters of the name column: mysql> CREATE INDEX part_of_name ON customer (name(10));

Because most names usually differ in the first 10 characters, this index should not be much slower than an index created from the entire name column. Also, using partial columns for indexes can make the index file much smaller, which could save a lot of disk space and might also speed up INSERT operations!

Note that you can only add an index on a column that can have NULL values or on a BLOB/TEXT column if you are using MySQL Version 3.23.2 or newer and are using the MyISAM table type.

For more information about how MySQL uses indexes, see section 5.4.3 How MySQL Uses Indexes.

FULLTEXT indexes can index only VARCHAR and TEXT columns, and only in MyISAM tables. FULLTEXT indexes are available in MySQL Version 3.23.23 and later. section 6.8 MySQL Full-text Search.

You can enter this at the SQL Monitor

What that will do, is make sure that ALL THREE fields are used to create a unique entry.

CREATE UNIQUE INDEX index_name ON Table_Name (name, lastname, position)

You don't need to use ID, because that will _ALWAYS_ be different, and give erroneus results.

Older MySQL installs would use the Alter table format:

ALTER TABLE table_name ADD UNIQUE index_name (index_col_name,...)

This will give a database error (handled by the error trapping mechanisms) to prevent adding duplicate data. You'd want to check it out, to make sure your templates/error messages make sense to your users.

If this doesn't work for you, you can:

ALTER TABLE table_name DROP INDEX index_name

without any lingering ill effects.

PUGDOG´┐Ż Enterprises, Inc.

The best way to contact me is to NOT use Email.
Please leave a PM here.
Quote Reply
Re: [pugdog] Alex! about dbmansql In reply to
thanx puldog let u know!