SQL Server : constraints and foreign keys

Question

I have an assignment where I must

Create an Entity Relationship diagram of a particular situation, and
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?

Each constraint MUST have a unique name, just like every table must have a unique name. Maybe something like fk_class_emp_id and fk_class_room_number instead? — Sean Lange
– Sean Lange, Commented Jan 31, 2017 at 22:15
You need a unique name for each primary key and foreign key constraint. You've named both your foreign key constraints the same thing. Thus the error. Your names in general are way, way too vague and generic. But specifically, your FK names should indicate what the FK relationship is... FK_class_instructor, and FK_class_classroom are possible suggestions. — pmbAustin
– pmbAustin, Commented Jan 31, 2017 at 22:15

Stephan Lechner · Accepted Answer · 2017-01-31 22:29:38Z

3

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
);

answered Jan 31, 2017 at 22:29

Stephan Lechner

35.2k4 gold badges38 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Server : constraints and foreign keys

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related