April 19, 2016 at 10:21 pm
I have TABLES
products1 and products2
CREATE TABLE products1
( product_id INT PRIMARY KEY,
product_name VARCHAR(50) NOT NULL,
category VARCHAR(25)
);
CREATE TABLE products2
( product_id INT ,
product_name VARCHAR(50) NOT NULL,
category VARCHAR(25) PRIMARY KEY
);
ALTER TABLE products1
ADD CONSTRAINT fk_name
FOREIGN KEY(category)
REFERENCES products2(category)
ALTER TABLE products2
ADD CONSTRAINT fk_name_2
FOREIGN KEY(product_id)
REFERENCES products1(product_id)
Then i tried to insert a record into table product1, i got the foreign key conflict error which was expected.
INSERT INTO products1 VALUES(1,'SSS','FMCG')
The Problem here is parent table is also acting like a child table.
is there anyway to insert a record , for this kind of scenario ?
April 20, 2016 at 12:59 am
I don't know the answer for this... is it even possible? Would you ever have a scenario like this?
I'm curious to see if any one has a solution for you or if they are like me questioning why this design takes place.
I would have suggested to have the below
CREATE TABLE product
(product_id INT PRIMARY KEY,
product_name VARCHAR(50) NOT NULL,
);
CREATE TABLE category
(
category_id INT PRIMARY KEY,
category_name VARCHAR(50) NOT NULL,
);
CREATE TABLE productcategory
(
ID INT PRIMARY KEY,
category_id INT,
product_id INT
);
the naming of the columns and tables are confusing me a little bit, not sure if Product_name is the same in both tables and its duplicated or if they are unique in both tables... I've assumed it is different so I called it category name for the other.
April 20, 2016 at 1:33 am
The Problem here is parent table is also acting like a child table.
is there anyway to insert a record , for this kind of scenario ? Yes it will behave in that way as you are referencing that parent table from the secondary table so you will never be able to insert any records in one of these table. The only way to insert records would be to drop your foreign keys, but then you loose data integrity.
This is obviously not a good design as you are what it seems like just duplicating data with these tables.
As stated by Tava below design is better in having your products and categories split into their own unique tables.
CREATE TABLE product
(product_id INT PRIMARY KEY,
product_name VARCHAR(50) NOT NULL,
);
CREATE TABLE category
(
category_id INT PRIMARY KEY,
category_name VARCHAR(50) NOT NULL,
);
CREATE TABLE productcategory
(
ID INT PRIMARY KEY,
category_id INT,
product_id INT
);
You can even eliminate the referencing table by just moving the CategoryID to your Products table for reference.
CREATE TABLE product
(product_id INT PRIMARY KEY,
product_name VARCHAR(50) NOT NULL,
category_id INT NOT NULL,
CONSTRAINT [FK_Product_Category] FOREIGN KEY (category_id ) REFERENCES dbo.category(category_id)
);
CREATE TABLE category
(
category_id INT PRIMARY KEY,
category_name VARCHAR(50) NOT NULL
);
April 20, 2016 at 7:27 am
Just a small note on your nice suggestion.
You offered first a solution for a many to many relationship. This is useful when a product can have different categories. But you have an unnecessary column.
CREATE TABLE product
(
product_id INT CONSTRAINT PK_Product PRIMARY KEY,
product_name VARCHAR(50) NOT NULL,
);
CREATE TABLE category
(
category_id INT CONSTRAINT PK_Category PRIMARY KEY,
category_name VARCHAR(50) NOT NULL,
);
CREATE TABLE productcategory
(
category_id INT,
product_id INT,
CONSTRAINT PK_ProductCategory PRIMARY KEY (category_id,product_id)
);
The second suggestion is a one to many relationship which is useful when a product can only have one category. The tables are declared in an incorrect order and would cause an error if the script is run as it is. The category table is the parent and should be created first to prevent the error.
April 20, 2016 at 9:51 am
kaza_rohan (4/19/2016)
The Problem here is parent table is also acting like a child table.is there anyway to insert a record , for this kind of scenario ?
The ANSI-standard for SQL has defined a feature called "deferred constraint checking". This means that once you open a transaction, you can make changes that cause constraint violations without getting errors. The constraints are checked when you try to commit the transaction. That feature is very useful for this type of scenario.
Unfortunately, it is not (yet??) implemented in SQL Server. So on this platform, the answer is "no". You cannot insert a row in any of these two tables.
If your data model calls for a database design like this (and though uncommon, that is not unheard of - though probably a bit more complex than your simplified repro), then your only option is to pick one constraint and choose not to enforce it. Often you can ensure that the data eventually ends up consistently by careful design of the application. (In all other cases, I am a fierce defender of enforcing all business rules as constraints in the database).
April 20, 2016 at 9:59 am
Hugo Kornelis (4/20/2016)
kaza_rohan (4/19/2016)
The Problem here is parent table is also acting like a child table.is there anyway to insert a record , for this kind of scenario ?
The ANSI-standard for SQL has defined a feature called "deferred constraint checking". This means that once you open a transaction, you can make changes that cause constraint violations without getting errors. The constraints are checked when you try to commit the transaction. That feature is very useful for this type of scenario.
Unfortunately, it is not (yet??) implemented in SQL Server. So on this platform, the answer is "no". You cannot insert a row in any of these two tables.
If your data model calls for a database design like this (and though uncommon, that is not unheard of - though probably a bit more complex than your simplified repro), then your only option is to pick one constraint and choose not to enforce it. Often you can ensure that the data eventually ends up consistently by careful design of the application. (In all other cases, I am a fierce defender of enforcing all business rules as constraints in the database).
Indeed. Still an active connect item about it, if we all want to try to clamor for it to be added at some point 🙂
Cheers!
April 20, 2016 at 3:06 pm
Luis Cazares (4/20/2016)
Just a small note on your nice suggestion.You offered first a solution for a many to many relationship. This is useful when a product can have different categories. But you have an unnecessary column.
Yeap, you are correct, was going to note that but the next comment advised a 2nd solution.
The requirements mentioned by OP did state its a PK but I don't think the op knows exactly what he wants. So just an idea thrown out
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply