[RESOLVED] Primary, Uniqe & Fulltext columns?

[Zpixel]

I know the benefits of indexing and how to perform it, but what are Primary, Uniqe & Fulltext columns intended for? Can we make our databases more efficiently using for example a primary column or a uniqe one? and what about fulltext?

Any comprehensive article or explanation that examplifies the usage is being demanded!?

O.o
---)

Thanks a lot.

[Weedpacket]

I'm sure most DBMSs have documentation that describe the different kinds of indexes and what they are for. The better ones even make observations about efficiency and internal representation.

[reli4nt]

You should google some documentation for your db but basically:

Primary is the unique identifier for each record or row in your table. It is how other tables will link to this one and how you can identify the correct record when you use it or even delete it.

Unique would be any other value that should never be duplicated. An example may be a product table where you use an internal id as a Primary Key but also a SKU which should never be duplicated. A custom url for a post would be another example since this field can;t be the Primary since it can change but should never be duplicated since the url can only point to one page.

Fulltext determines if you want that field searchable in that manner I believe. I seldom use it.

[Zpixel]

intresting. and some other information about them:

The presence of an AUTO_INCREMENT column implicitly limits the number of rows a table may have. For example, if the column is TINYINT UNSIGNED, the maximum value it may hold is 255, so that also becomes the maximum number of rows the table may hold. Larger integer types allow more rows. More generally, including any PRIMARY KEY or UNIQUE index in a table limits its row count to the maximum number of unique values in the index.

AUTO_INCREMENT is a column attribute that should be used only with integer types. That immediately limits your choices to TINYINT tHRough BIGINT.

An AUTO_INCREMENT column is intended only for generating sequences of positive values, so you should define it as UNSIGNED.

AUTO_INCREMENT columns must be indexed. Furthermore, to prevent duplicates in the column, the index must be unique. This means you must define the column as a PRIMARY KEY or as a UNIQUE index.

AUTO_INCREMENT columns must be NOT NULL.

You define it like this:

mycol integer_type UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (mycol)

Or like this:

mycol integer_type UNSIGNED NOT NULL AUTO_INCREMENT,
UNIQUE (mycol)

---

It seems that marking a column as AUTO_INCREMENT shoud be follwed by marking it as a primary or uniqe feature to prevent duplicates in record set values, but most of our uses from db may include duplicate values and even we check for example when registering an email address by programming, so i think there are less times that may need primary or uniqe columns rather than indexed columns?

[Weedpacket]

Zpixel wrote:

i think there are less times that may need primary or uniqe columns rather than indexed columns?

Primary/unique key constraints are implemented using indexes.

Here's what documentation for the DBMS I use says about what primary keys are for.

5.3.4. Primary Keys

Technically, a primary key constraint is simply a combination of a unique constraint and a not-null constraint. So, the following two table definitions accept the same data:

CREATE TABLE products (
    product_no integer UNIQUE NOT NULL,
    name text,
    price numeric
);

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

Primary keys can also constrain more than one column; the syntax is similar to unique constraints:

CREATE TABLE example (
    a integer,
    b integer,
    c integer,
    PRIMARY KEY (a, c)
);

A primary key indicates that a column or group of columns can be used as a unique identifier for rows in the table. (This is a direct consequence of the definition of a primary key. Note that a unique constraint does not, by itself, provide a unique identifier because it does not exclude null values.) This is useful both for documentation purposes and for client applications. For example, a GUI application that allows modifying row values probably needs to know the primary key of a table to be able to identify rows uniquely.

A table can have at most one primary key. (There can be any number of unique and not-null constraints, which are functionally the same thing, but only one can be identified as the primary key.) Relational database theory dictates that every table must have a primary key. This rule is not enforced by PostgreSQL, but it is usually best to follow it.

Section 5.3.5 goes on to say that in a foreign-key constraint, if the (unique) column in the parent table is not explicitly given, the primary key will be used.

Oh, better do this bit:

Legal Notice

PostgreSQL is Copyright © 1996-2006 by the PostgreSQL Global Development Group and is distributed under the terms of the license of the University of California below.

Postgres95 is Copyright © 1994-5 by the Regents of the University of California.

Permission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a written agreement is hereby granted, provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies.

IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN “AS-IS” BASIS, AND THE UNIVERSITY OF CALIFORNIA HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.

[Zpixel]

Well. about how to use primary and unique keys and an objective use of them I could find that if we have one of those keys above in our db, the deletion action within the database for more than one column is done very well and the records we have chosen to delete would be eliminated correctly and no records would be left.

an aplicable usage of these keys I'm searching for....
could somebody give a simple example on how to use them?
for example we have two tables and we want to join them,so we need a primary key, etc. somthing like this. of course i'm not sure about my last sentence.

[Weedpacket]

I'm not sure about any of your sentences. Like where they start or finish.