Creating a table with a couple as primary key in MySQL Server

Question

I have 2 tables:

Authors(id, name, add, DOB) where the fields are INTEGER, CHAR(20), CHAR(20), DATE respectively. Primary key = id.
Books(bid, title, publisher) where the fields are INTEGER, CHAR(20), CHAR(20) respectively. Primary key = bid.

I want to create another table Write with fields aid, bid, datePublished, where:

aid = INTEGER ... this is actually the id from Authors.
bid = INTEGER ... this is actually the bid from Books
Primary Key = (aid, bid).

How do I do this?

I tried the following lines of code, and all of them gave syntax errors:

CREATE TABLE Write (
    aid INTEGER, 
    bid INTEGER, datePublished DATE, 
    PRIMARY KEY NONCLUSTERED (aid,bid), 
    FOREIGN KEY (aid) REFERENCES Authors(id), 
    FOREIGN KEY (bid) REFERENCES Books(bid)
);

CREATE TABLE Write (
    aid INTEGER, 
    bid INTEGER, 
    datePublished DATE, 
    PRIMARY KEY (aid,bid)
);

What's the actual error you're getting? – Jesse Sep 26 '12 at 05:51 — Jesse, Sep 26 '12 at 05:51

score 3 · Answer 1 · answered Sep 26 '12 at 04:42

Funny problem you're having. The reason is that Write is a reserved keyword in mysql and you can only use it as an identifier if you suround it by backticks in your queries. I recomend you picking another name or, if you really want to use Write, replace your create query with

CREATE TABLE `Write` (
    aid INTEGER, 
    bid INTEGER, 
    datePublished DATE, 
    PRIMARY KEY (aid,bid)
);

Just remember that any other query on the table Write would require the backticks as well, which might lead to annoying errors if someone forgets them.

Thanks Eric. You were correct. I'll use Writes to make my life easier :) — Aditya Chirimar, Sep 26 '12 at 04:45

Andreas Wederbrand · Answer 2 · 2012-09-26T04:51:37.383

1

Write is a reserved word, no table can be named that (it can using back tics but to make your life easy stay away from names that requires back tics).

Here is the complete schema http://sqlfiddle.com/#!2/dfe22/1

edited Sep 26 '12 at 04:51

answered Sep 26 '12 at 04:46

Andreas Wederbrand

38,065
11
68
78

this is wrong, see my answer and this sqlfiddle http://sqlfiddle.com/#!2/f2ecd – Erik Sep 26 '12 at 04:47
You are correct. It is possible with back ticks. Personally I treat that as a bad hack and never use names that requires back tics. – Andreas Wederbrand Sep 26 '12 at 04:50
+1 for "stay away from backticks" – Sep 26 '12 at 06:39

score 0 · Answer 3 · answered Sep 26 '12 at 04:39

0

Try this :

CREATE TABLE Write (aid INTEGER, bid INTEGER, datePublished DATE PRIMARY KEY (aid,bid));

answered Sep 26 '12 at 04:39

Amrish Prajapati

767
3
8

Thanks for your prompt reply Amrish. Unfortunately, that too didn't work. I tried what you suggested, and also what you suggested except that I added a comma after DATE and before PRIMARY KEY. Neither worked. – Aditya Chirimar Sep 26 '12 at 04:42
I'm getting a SQL Syntax error :( – Aditya Chirimar Sep 26 '12 at 04:43
Do u have created Authors and Books table ? can you please send that also ? – Amrish Prajapati Sep 26 '12 at 04:49

Creating a table with a couple as primary key in MySQL Server

3 Answers3