Click here to Skip to main content
16,016,227 members

Comments by Webcodeexpert.com (Top 7 by date)

Webcodeexpert.com 30-May-19 5:17am View    
Thanks for your solution. It worked well. But i feel your and my query can be short. I don't know how. I am waiting for some other solutions.
Webcodeexpert.com 9-Apr-19 8:35am View    
.
Webcodeexpert.com 25-Mar-19 3:21am View    
thanks for your comments. I have redesigned the table and its is working as required. Please have a look and let me know is this correct or not

go
CREATE TABLE tbEmployee (
EmployeeId INT IDENTITY(1,1) PRIMARY KEY NOT NULL ,
EmployeeName VARCHAR(100)
);


GO
CREATE TABLE tbEmployeeIdentityDetails (
EmployeeId INT PRIMARY KEY FOREIGN KEY REFERENCES tbEmployee(EmployeeId),
AadharNumber VARCHAR(20) NOT NULL,
CONSTRAINT UC_Employee_AadharNumber UNIQUE (AadharNumber)
);


GO
INSERT INTO tbEmployee (EmployeeName)
VALUES
('Aman'),
('Kapil'),
('Vijay'),
('Panjak');

GO
INSERT INTO tbEmployeeIdentityDetails (AadharNumber, EmployeeId)
VALUES
('3157 8787 0987', 1),
('6432 3246 9097', 2),
('8875 8746 9234', 3)


GO
SELECT * FROM tbEmployee
SELECT * FROM tbEmployeeIdentityDetails



if we try to insert identity details for same EmployeeId which already exists in the tbEmployeeIdentityDetails table it will show "Violation of PRIMARY KEY constraint" error

INSERT INTO tbEmployeeIdentityDetails (AadharNumber, EmployeeId)
VALUES
('5543 4532 1123',2);


if we try to insert identity details for the an EmployeeId which does not exists in the tbEmployee table it givens Foreign key violation error
INSERT INTO tbEmployeeIdentityDetails (AadharNumber, EmployeeId)
VALUES
('8157 8789 4987',9);


if we try to insert existing aadhar number for an EmployeeId it will show "Violation of UNIQUE KEY constraint" erro
INSERT INTO tbEmployeeIdentityDetails (AadharNumber, EmployeeId)
VALUES
('3157 8787 0987',4);
Webcodeexpert.com 25-Mar-19 3:21am View    
thanks for your comments. I have redesigned the table and its is working as required. Please have a look and let me know is this correct or not

go
CREATE TABLE tbEmployee (
EmployeeId INT IDENTITY(1,1) PRIMARY KEY NOT NULL ,
EmployeeName VARCHAR(100)
);


GO
CREATE TABLE tbEmployeeIdentityDetails (
EmployeeId INT PRIMARY KEY FOREIGN KEY REFERENCES tbEmployee(EmployeeId),
AadharNumber VARCHAR(20) NOT NULL,
CONSTRAINT UC_Employee_AadharNumber UNIQUE (AadharNumber)
);


GO
INSERT INTO tbEmployee (EmployeeName)
VALUES
('Aman'),
('Kapil'),
('Vijay'),
('Panjak');

GO
INSERT INTO tbEmployeeIdentityDetails (AadharNumber, EmployeeId)
VALUES
('3157 8787 0987', 1),
('6432 3246 9097', 2),
('8875 8746 9234', 3)


GO
SELECT * FROM tbEmployee
SELECT * FROM tbEmployeeIdentityDetails



if we try to insert identity details for same EmployeeId which already exists in the tbEmployeeIdentityDetails table it will show "Violation of PRIMARY KEY constraint" error

INSERT INTO tbEmployeeIdentityDetails (AadharNumber, EmployeeId)
VALUES
('5543 4532 1123',2);


if we try to insert identity details for the an EmployeeId which does not exists in the tbEmployee table it givens Foreign key violation error
INSERT INTO tbEmployeeIdentityDetails (AadharNumber, EmployeeId)
VALUES
('8157 8789 4987',9);


if we try to insert existing aadhar number for an EmployeeId it will show "Violation of UNIQUE KEY constraint" erro
INSERT INTO tbEmployeeIdentityDetails (AadharNumber, EmployeeId)
VALUES
('3157 8787 0987',4);
Webcodeexpert.com 20-Mar-19 2:34am View    
thanks for you reply..Actually i was following these 3 articles
https://www.tech-recipes.com/rx/56738/one-to-one-one-to-many-table-relationships-in-sql-server/

https://code.tutsplus.com/articles/sql-for-beginners-part-3-database-relationships--net-8561

https://howtoprogramwithjava.com/database-relationships-many-many-one-one/

but could not get the proper way and use of 1-TO-1 Relationship. Please suggest which one should i follow..