CODEWITHSUNDEEP
sqlsql-serverforeign-keysconstraints
GreenFerret95
GreenFerret95

Reputation: 117

SQL Server : constraints and foreign keys

I have an assignment where I must

  1. Create an Entity Relationship diagram of a particular situation, and
  2. Write up the SQL code to represent the diagram

I am new to SQL Server, but I have a table class that has a primary key CRN varchar(10)(UNN) and two foreign keys, emp_id varchar(20) (NN) which has a 1 mandatory relationship with instructor, and room_number varchar(5) (UNN) which also has a 1 mandatory relationship with Classroom.

My code for table Class: 

CREATE TABLE class
(
     CRN varchar(10) UNSIGNED NOT NULL, 
     emp_id varchar(20), 
     room_number varchar(5), 
     enrollment smallint UNSIGNED NOT NULL,

     CONSTRAINT pk_class PRIMARY KEY (CRN),
     CONSTRAINT fk_class 
         FOREIGN KEY (emp_id) REFERENCES instructor (emp_id),
     CONSTRAINT fk_class 
         FOREIGN KEY (room_number) REFERENCES classroom (room_number)
);

The error I'm getting is:

Constraint "FK_CLASS" already exists; SQL statement:
CREATE TABLE class
(CRN varchar(10) UNSIGNED NOT NULL,
emp_id varchar(20),
room_number varchar(5),
enrollment smallint UNSIGNED NOT NULL,
CONSTRAINT pk_class PRIMARY KEY (CRN),
CONSTRAINT fk_class FOREIGN KEY (emp_id) REFERENCES instructor (emp_id),
CONSTRAINT fk_class FOREIGN KEY (room_number) REFERENCES classroom (room_number) ) [90045-193]

I have seen many different examples on how to make a table have two foreign keys, but have had no luck. What am I doing wrong?

Upvotes: 0

Views: 473

Answers (1)

Stephan Lechner
Stephan Lechner

Reputation: 35164

Contraints must have a unique name, and you are using name fk_class twice. So naming the one fk_instructor and the other fk_classroom should solve the problem.

Note that there is a shorter notation, which avoids such issues:

CREATE TABLE class (
  CRN varchar(10) PRIMARY KEY, 
  emp_id varchar(20) references instructor (emp_id), 
  room_number varchar(5) REFERENCES classroom (room_number), 
  enrollment smallint UNSIGNED NOT NULL
);

Upvotes: 3

Related Questions