How can I define a composite primary key in SQL

Question

How can I define a composite primary key consisting of two fields in SQL   I am using PHP to create tables and everything  I want to make a table name voting with fields QuestionID  MemeberID  and vote  And the Composite primary key consists of the fields QuestionID and MemberID    How should I do this

User · Answer

CREATE TABLE  voting       QuestionID  int 10  unsigned NOT NULL     MemberId  int 10  unsigned NOT NULL     vote  int 10  unsigned NOT NULL    PRIMARY KEY    QuestionID   MemberId

User · Answer

In Oracle database we can achieve like this  CREATE TABLE Student    StudentID Number 38  0  not null    DepartmentID Number 38  0  not null    PRIMARY KEY  StudentID  DepartmentID

User · Answer

Just for clarification  a table can have at most one primary key  A primary key consists of one or more columns  from that table   If a primary key consists of two or more columns it is called a composite primary key  It is defined as follows   CREATE TABLE voting     QuestionID NUMERIC    MemberID NUMERIC    PRIMARY KEY  QuestionID  MemberID       The pair  QuestionID MemberID  must then be unique for the table and neither value can be NULL  If you do a query like this   SELECT   FROM voting WHERE QuestionID   7   it will use the primary key s index  If however you do this   SELECT   FROM voting WHERE MemberID   7   it won t because to use a composite index requires using all the keys from the  left   If an index is on fields  A B C  and your criteria is on B and C then that index is of no use to you for that query  So choose from  QuestionID MemberID  and  MemberID QuestionID  whichever is most appropriate for how you will use the table   If necessary  add an index on the other   CREATE UNIQUE INDEX idx1 ON voting  MemberID  QuestionID

[sql] How can I define a composite primary key in SQL?

Examples related to sql

Examples related to primary-key

Examples related to composite-primary-key