refernces column for table

Amudhan Kuppan

8y865SQL

anyone please help me

how to create a reference column for a table

i will send table structure please send me

physician:

employeeid – this is a unique ID of a physician(primary key it should be auto increment)

name – this is the name of a physician

position – this is the designation of a physician

ssn – this is a security number of a physician

department:

departmentid – this is a unique ID for a department(primary key it should be auto increment)

name – this is the name of a department

head – this is the ID of the physician who is the head of a department, referencing to the column employeeid of the table physician

9 Replies

Know the answer? Post it — somebody with the same question will find it here.

Uttam Chaturvedi8y ago
Accepted answer
My Answer as per your qury:
As per my understanding you want to store 0 in Column code.
1. Since Cost column is is numeric and and you want to keep some amount, In that case you better not to make it as unique column add some other coulmn as key
2. If you are making column as unique it can have one unique value as 0 should not be an issue .

I hope I am answering your question
Uttam Chaturvedi8y ago
Hi Amudhan,

In short when we create a primary key on some column it is treated as clustred index and determine the physical oreder of the rows. We can have only one clustred index per table

whereas Non-Clustered is like a index of book. Data is strored in one place and index is stored in another place and index have the pointer to the storage location.

We can have more than one clustered index in a table
Amudhan Kuppan8y ago
ok thank you please see again in forum iam ask another question also clustered index and non clustered index

what is the difference between clustered index and non clustered index
clustered index means it reorders the way of column whatever iam give right?
and non clustered index means
please tell me i dont know
Amudhan Kuppan8y ago
what is the difference between clustered index and non clustered index
clustered index means it reorders the way of column whatever iam give right?
and non clustered index means
please tell me i dont know
Amudhan Kuppan8y ago
ok iam asking one more doubt i will send file
Uttam Chaturvedi8y ago
Hi Amudhan,

You can't have different value in foriegn key which is not existing in primary key column.

For example: If you try to insert different PhysicanId(foriegn key) in the department table which is not existing in Physican table as primary key then you will get an error something like below:

The INSERT statement conflicted with the FOREIGN KEY constraint
Amudhan Kuppan8y ago
ok how to cross check this Uttam
I need insert a data right?
try to enter a EmployeeID in which is not in physician table
sorry iam not understand this kind of requirement
please send me a query
Amudhan Kuppan8y ago
thank you
Uttam Chaturvedi8y ago
Hello,

You can do somthing like this to create refernce column
CREATE TABLE department(
departmentid int IDENTITY(1,1),
PhysicanId int
PRIMARY KEY (departmentid),
FOREIGN KEY (PhysicanId) REFERENCES Physican(PhysicanId)
)
Note: if you want to autoIncrement value by one then use Identity(1,1) as shown above. I hope it will help you