Обсуждение: 2 novice questions

Do primary keys need to be defined in all tables that are releated by the
key or only in the primary one?

For instance if I create tables to hold file info as shown below...

CREATE TABLE filename (id SEQUENCE PRIMARY KEY, name TEXT UNIQUE);

CREATE TABLE filestat (id INTEGER, size INTEGER, ts TIMESTAMP, md5
CHAR(32));

Does the filestat.id column need a PRIMARY KEY constraint?

Also, if I have a read only user, do I need to grant SELECT access on the
sequence?
GRANT SELECT ON filename_id_seq TO myuser;

Thanks for all the help this list provides:)

Thanks,

dale.sykora@compaq.com

Hey,

>Do primary keys need to be defined in all tables that are releated by the
>key or only in the primary one?

Only the primary one.

>For instance if I create tables to hold file info as shown below...
>
>CREATE TABLE filename (id SEQUENCE PRIMARY KEY, name TEXT UNIQUE);

Try...
create table filename (id serial primary key, name text unique);

serial is the datatyep to create a sequence.

>CREATE TABLE filestat (id INTEGER, size INTEGER, ts TIMESTAMP, md5
>CHAR(32));
>
>Does the filestat.id column need a PRIMARY KEY constraint?

You can use foreign keys in postgresql :)
Try this out...

create table filestat (id int not null constraint filestatid references
filename,size integer, ts timestamp, md5
char(32));

basically, id references filename(id) and this constraint is called
"filestatid" (you can rename this to something else of course).

http://techdocs.postgresql.org/techdocs/hackingreferentialintegrity.php <--
might give you a starting point for it all.

>Also, if I have a read only user, do I need to grant SELECT access on the
>sequence?
>GRANT SELECT ON filename_id_seq TO myuser;

Yes, you will.

HTH
----------------------
      Chris Smith
http://www.squiz.net/