Solved

How to create table with combo primary key which are also two foreign keys

Posted on 2008-10-29
2
167 Views
Last Modified: 2012-06-27
I'm creating a bridge table to change a many to many relationship to two many to one relationships.
I'm new to transact-sql.  The create table statement below references two columns for the primary key, au_id and title_id.  These are also both primary keys in two other tables, called authors and titles.  I'm not sure I have the syntax correct below.  Any suggestions, oberservations are appreciated.

create table titleauthor (
au_id VARCHAR (7) NOT NULL,
title_id VARCHAR (7) NOT NULL,
royalty FLOAT,
notes TEXT,
PRIMARY KEY (au_id,title_id),
FOREIGN KEY (au_id,title_id) REFERENCES authors,titles);
0
Comment
Question by:chuckmullins
2 Comments
 
LVL 37

Accepted Solution

by:
momi_sabag earned 125 total points
ID: 22829041
try
create table titleauthor (
au_id VARCHAR (7) NOT NULL,
title_id VARCHAR (7) NOT NULL,
royalty FLOAT,
notes TEXT,
PRIMARY KEY (au_id,title_id),
FOREIGN KEY (au_id) REFERENCES authors,
FOREIGN KEY (title_id) REFERENCES titles);
0
 

Author Closing Comment

by:chuckmullins
ID: 31511068
Perfect!  Thanks so much!
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
View SQL 2005 Job package 16 70
SSRS 2012 r2 - Parm Drop Down has Date/Time 12 40
TSQL query to generate xml 4 46
Filtering characters in an SQL field 2 16
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question