wolf_22 — 2013-06-18T10:42:06-04:00 — #1
I have the following schema for a referential table in a MySQL database:
ip varchar(255) NOT NULL,
value varchar(255) NOT NULL,
schedule int(11) NOT NULL,
UNIQUE KEY (ip)
"ip" is going to be used to record IP addresses.
"value" will contain 3 string values: "alive", "dead", or "unknown".
"schedule" will store Unix timestamps.
Since the table is storing IP addresses, I wanted to set ip to UNIQUE KEY to prohibit duplicates. Doing this would essentially make this table a referential table. Correct so far?
I'm not sure if the above schema for MySQL is as optimized as it should be, so please feel free to educate me further about what might be done to make it better.
That aside, I need the above to be translated into PGSQL. Is it as simple as copying over the MySQL schema into PGSQL or is there more to it? I read that the keys need to be executed separately from the SQL that creates the tables... Is this true? Because in MySQL, you don't have to do this...
Any insight is appreciated.
r937 — 2013-06-18T12:22:58-04:00 — #2
depends on what you mean by a "referential" table
you should probably make ip the PRIMARY KEY rather than UNIQUE KEY, because UNIQUE KEY will allow a null value
your definitions are fine for postgresql
as for declaring postgresql keys separately, why don't you test it and see?
wolf_22 — 2013-06-18T13:00:43-04:00 — #3
So basically it sounds like PRIMARY KEY is usually the preferred route because it also has the UNIQUE constraint... Yes?
r937 — 2013-06-18T13:42:19-04:00 — #4
yes... and this is true in all database systems