Require pair of fields to be unique?
Hello, I have a strange question. I'm in the design phase for a table and was wondering if it's possible in MySQL to force a pair of fields in a record to be unique within the table. I would like it to look like this: user_id job_id What I'd like to do is never have the same user_id and job_id paired up paired together more than once. Maybe I'm missing a better way to setup this table. If I am, please let me know. Thanks. -- Greg Macek | Senior IT Manager Marketing Resources, Inc. [EMAIL PROTECTED] | http://www.mrichi.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Require pair of fields to be unique?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tuesday 22 July 2003 21:56, Greg Macek wrote: Hello, I have a strange question. I'm in the design phase for a table and was wondering if it's possible in MySQL to force a pair of fields in a record to be unique within the table. I would like it to look like this: user_id job_id What I'd like to do is never have the same user_id and job_id paired up paired together more than once. Maybe I'm missing a better way to setup this table. If I am, please let me know. Thanks. Just create a Unique index. For example if the table is named Tbl then (for example) Create Unique Index IX_Tbl1 on Tbl (user_id, job_id); This will allow user_id or job_id to be duplicated in the table, but no combination of user_id and job_id can be repeated. -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.2-rc1-SuSE (GNU/Linux) iD8DBQE/HfzTjeziQOokQnARAtYmAJ9j7rWWvIbEPkLKcL1oDbdveJJELQCfWkKE e+BFvxSllsIqZJ5Q3nCeXcg= =zPh7 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Require pair of fields to be unique?
At 21:56 -0500 7/22/03, Greg Macek wrote: Hello, I have a strange question. I'm in the design phase for a table and was wondering if it's possible in MySQL to force a pair of fields in a record to be unique within the table. I would like it to look like this: user_id job_id What I'd like to do is never have the same user_id and job_id paired up paired together more than once. Maybe I'm missing a better way to setup this table. If I am, please let me know. Thanks. Create a composite PRIMARY KEY or UNIQUE index on both columns. ALTER TABLE tbl_name ADD PRIMARY KEY (user_id, job_id); or ALTER TABLE tbl_name ADD UNIQUE (user_id, job_id); You can use a PRIMARY KEY only if both columns are NOT NULL. -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
