checking foreign key in SQL, is it right?

Question

I need just little your help, can you just check if have I done foreign keys properly in SQL Server?

I'm new in SQL that is why sorry for stupid question.

If everything right, can I start to fill data?

--CREATING EMPLOYEE TABLE
CREATE TABLE EMPLOYEE(
[ID] INT IDENTITY(1,1) NOT NULL, 
[FIRST_NAME] NVARCHAR(25) NOT NULL,
[LAST_NAME] NVARCHAR(25) NOT NULL,
[EMAIL] nvarchar(60) NOT NULL CONSTRAINT UQ_EMPLOYEE_email UNIQUE,
[PHARMACY_ID] INT NOT NULL,
CONSTRAINT PK_EMPLOYEE_ID PRIMARY KEY (ID));

--CREATING PHARMACY TABLE
CREATE TABLE PHARMACY (
[ID] INT IDENTITY(1, 1) NOT NULL, 
[NAME] NVARCHAR(25),
[ADDRESS] NVARCHAR(25) NULL,
[PHONE] nvarchar(24) NULL CHECK(PHONE like '(___)-__-___ __ __'),
[EID] [INT] NOT NULL
CONSTRAINT PK_PHARMACY_ID PRIMARY KEY (ID));

--CREATE SUPPLIERS TABLE
CREATE TABLE SUPPLIERS(
[ID] INT IDENTITY(1,1) NOT NULL,
[NAME] NVARCHAR(25),
[ADDRESS] NVARCHAR(25) NULL,
[DRUGSID] [INT] NOT NULL,
CONSTRAINT PK_SUPPLIERS_ID PRIMARY KEY (ID));

--CREATE TABLE DRUGS
CREATE TABLE DRUGS(
[ID] INT IDENTITY(1,1) NOT NULL,
[NAME] NVARCHAR(25),
[QUANTITY] INT NULL,
[ORDERID] [INT] NULL,
[SUPPLIERID] [INT] NULL,
CONSTRAINT PK_DRUGS_ID PRIMARY KEY(ID));

--CREATEING TABLE ORDERS
CREATE TABLE ORDERS (
[ID] INT IDENTITY(1,1) NOT NULL,
[ONAME] NVARCHAR(25) NOT NULL,
[ODATE] DATETIME,
[OQUANTITY] INT NOT NULL,
[SUPPLIERID] [INT] NOT NULL,
[DRUGID] [INT] NOT NULL,
CONSTRAINT PK_ORDERS_ID PRIMARY KEY(ID));

--CREATING TABLE MANAGER TABLE 
CREATE TABLE MANAGER (
[PASSPORTNO] INT IDENTITY(1,1) NOT NULL,
[HOURLYPAY] DECIMAL(5,2) NULL,
[LANGUAGE_SKILLS] NVARCHAR(25) NULL
CONSTRAINT PK_MANAGER_NO PRIMARY KEY (PASSPORTNO));

--CREATING TABLE SALES ASSISTANT
CREATE TABLE SALES_ASSISTANT (
[PASSPORTNO] INT IDENTITY(1,1) NOT NULL,
[SALARY] DECIMAL(5,2) NULL
CONSTRAINT PK_SALES_ASSISTANT_NO PRIMARY KEY (PASSPORTNO));

-- FOREIGN KEYs CREATION
ALTER TABLE EMPLOYEE ADD CONSTRAINT FK_PHARMACYID FOREIGN KEY(PHARMACY_ID) 
REFERENCES PHARMACY(ID) ON DELETE CASCADE ON UPDATE CASCADE

ALTER TABLE PHARMACY ADD CONSTRAINT FK_EID FOREIGN KEY(EID) 
REFERENCES EMPLOYEE(ID) ON DELETE NO ACTION ON UPDATE NO ACTION

ALTER TABLE SUPPLIERS ADD CONSTRAINT FK_DRUGID FOREIGN KEY(DRUGSID) 
REFERENCES DRUGS(ID) ON DELETE NO ACTION ON UPDATE NO ACTION

ALTER TABLE DRUGS ADD CONSTRAINT FK_ORDERID FOREIGN KEY(ORDERID) 
REFERENCES ORDERS(ID) ON DELETE NO ACTION ON UPDATE NO ACTION


ALTER TABLE DRUGS ADD CONSTRAINT FK_SUPPLIERS FOREIGN KEY(SUPPLIERID) 
REFERENCES SUPPLIERS(ID) ON DELETE NO ACTION ON UPDATE NO ACTION


ALTER TABLE ORDERS ADD CONSTRAINT FK_SUPPLIERS_OR FOREIGN KEY(SUPPLIERID) 
REFERENCES SUPPLIERS(ID) ON DELETE NO ACTION ON UPDATE NO ACTION

ALTER TABLE ORDERS ADD CONSTRAINT FK_DRUGID_OR FOREIGN KEY(DRUGID) 
REFERENCES DRUGS(ID) ON DELETE NO ACTION ON UPDATE NO ACTION

Possible duplicate of [What is MySQL's default ON DELETE behavior?](http://stackoverflow.com/questions/1027656/what-is-mysqls-default-on-delete-behavior) — Lukasz Szozda, Dec 12 '15 at 09:39
Have you considered renaming each of the ID fields, to make it clearer which is which, and what joins to what? Example: DrugId instead of ID. — David Rushton, Dec 12 '15 at 10:23
@destination-data Have you considered the ID is good name for the PK of a table? Which is which? Drug.ID — paparazzo, Dec 12 '15 at 11:35

Stefan Steiger · Answer 1 · 2015-12-14T17:30:27.623

You can check the foreign keys you have set with this query

SELECT  
     KCU1.CONSTRAINT_NAME AS FK_CONSTRAINT_NAME 
    ,KCU1.TABLE_NAME AS FK_TABLE_NAME 
    ,KCU1.COLUMN_NAME AS FK_COLUMN_NAME 
    ,KCU1.ORDINAL_POSITION AS FK_ORDINAL_POSITION 
    ,KCU2.CONSTRAINT_NAME AS REFERENCED_CONSTRAINT_NAME 
    ,KCU2.TABLE_NAME AS REFERENCED_TABLE_NAME 
    ,KCU2.COLUMN_NAME AS REFERENCED_COLUMN_NAME 
    ,KCU2.ORDINAL_POSITION AS REFERENCED_ORDINAL_POSITION 
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC 

INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU1 
    ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG  
    AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA 
    AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME 

INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU2 
    ON KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG  
    AND KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA 
    AND KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME 
    AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION

One more thing: Always create your scripts so you can run it N times if necessary. So either drop the foreign key before you create it, or only create it if it doesn't exist. The same thing goes for the tables (don't drop them if they contain data).

Additionally, if you create a foreign key, you should check if all referenced tables exists, plus check that the referenced table has the primary key set.

Edit: One tip, on a quick glare:
I would check if you really need DATETIME.
If you only need a date, use date instead of datetime.

I don't think Nuriddin is looking for a statement to retrieve the definition from the database. She/He is looking for an advice if the design is correct. — , Dec 12 '15 at 10:53
@a_horse_with_no_name: Yes, but it can also be interpreted as "have I really set all foreign keys". — Stefan Steiger, Dec 21 '15 at 12:38

paparazzo · Answer 2 · 2015-12-12T11:32:49.540

0

That is how you do a FK but that data design does not look correct. You don't have an order point to a drug AND a drug point to an order. Read up on 3NF. A supplier does not have a single drug. DrugsID may have an S but it is still singular. This data design is not correct.

edited Dec 12 '15 at 11:32

answered Dec 12 '15 at 10:41

paparazzo

44,497
23
105
176

checking foreign key in SQL, is it right?

2 Answers2