Quoted from the CREATE TABLE Syntax page: A PRIMARY KEY can be a multiple-column index. However, you cannot create a multiple-column index using the PRIMARY KEY key attribute in a column specification. Doing so only marks that single column as primary. You must use a separate PRIMARY KEY(index_col_name, …) clause. Something like this can be … Read more
Just set a unique index on composite of (username, date). ALTER TABLE `table` ADD UNIQUE INDEX `name` (`username`, `date`); Alternatively, you can try to ALTER TABLE `table` DROP PRIMARY KEY, ADD PRIMARY KEY(`username`,`date`); and I think in the latter case you need those columns to be declared NOT NULL.
If there’s a combination of columns that makes a row unique, model a primary key class around the combination of columns. If there isn’t, you’re basically out of luck — but you should reexamine the design of the view since it probably doesn’t make sense. There are a couple different approaches: @Entity public class RegionalArticle … Read more
Your code and schema are OK. You probably trying on previous version of table. http://sqlfiddle.com/#!2/9dc64/1/0 Your table even has no UNIQUE, so that error is impossible on that table. Backup data from that table, drop it and re-create. Maybe you tried to run that CREATE TABLE IF NOT EXIST. It was not created, you have … Read more
Since you don’t want textbook definitions, loosely speaking, a super key is a set of columns that uniquely defines a row. This set can have one or more elements, and there can be more than one super key for a table. You usually do this through functional dependencies. In your example, I’m assuming: StudentNumber unique … Read more
Yes you can. You just need to make that column be an index. CREATE TABLE `test` ( `testID` int(11) NOT NULL, `string` varchar(45) DEFAULT NULL, `testInc` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`testID`), KEY `testInc` (`testInc`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; insert into test( testID, string ) values ( 1, ‘Hello’ ); insert into test( … Read more
For table 1, this INSERT statement will succeed. If you run it 100 times, it will succeed 100 times. insert into referencing_table values (null); The same INSERT statement will fail on table 2. ERROR: null value in column “indexing_table_id” violates not-null constraint DETAIL: Failing row contains (null).
add_index :words, [“id”, “language_id”], :unique => true It should work. Maybe you have already some non-unique data in your db and index can’t be created? But (as @Doon noticed it will be redundant since id is always unique). So you need create primary key on two columns. To define 2 column primary key in rails … Read more
You could do something like the following, however it is better to include it in the create table as a_horse_with_no_name suggests. if NOT exists (select constraint_name from information_schema.table_constraints where table_name=”table_name” and constraint_type=”PRIMARY KEY”) then ALTER TABLE table_name ADD PRIMARY KEY (id); end if;
The advantages you have for choosing a character datatype as a primary key field is that you may choose what data it can show. As an example, you could have the email address as the key field for a users table. The eliminates the need for an additional column. Another advantage is if you have … Read more