CODEWITHSUNDEEP
mysqlconstraints
kevorski
kevorski

Reputation: 836

adding constraint to column using another table's column

So I'm trying to add a constraint from one column in a table using another column's value in a separate table.

I tried:

ALTER TABLE BOOK_STORES ADD CONSTRAINT Rep_ID CHECK(Rep_ID IN (SELECT Rep_ID FROM STORE_REPS));

These are my two tables:

Store_Reps

Rep_ID INT(5) (PK), Last VARCHAR(15) NOT NULL, First VARCHAR(10) NOT NULL, Comm CHAR(1)

Book_Stores

Store_ID INT(8), Name VARCHAR(30) UNIQUE NOT NULL, Contact VARCHAR(20), Rep_ID(5)

I'm trying to add the constraint to the book stores rep_id using the store_reps rep_id

Upvotes: 0

Views: 715

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

I think you want a foreign key constraint:

ALTER TABLE BOOK_STORES
    ADD FOREIGN KEY (Rep_ID) REFERENCES STORE_REPS(Rep_Id);

You can also do this directly in the create table statement quite succinctly:

create table book_stores (
     . . .
     Rep_Id int references store_reps(rep_id),
);

Upvotes: 2

Related Questions