pointing foreign key to datetime

Question

is that possible to make foreign key from datetime datatype ? i try this but i got error message : Msg 1776, Level 16, State 0, Line 1 There are no primary or candidate keys in the referenced table 'penduduk' that match the referencing column list in the foreign key 'tgllahir'. Msg 1750, Level 16, State 0, Line 1 Could not create constraint. See previous errors.

i use this query

PARENT TABLE :

create table penduduk (
no int identity(1,1),
noktp char(11) primary key,
nama varchar(20),
tgl_lahir datetime NOT NULL,
namahari varchar(20),
tgl int,
bulan int,
namabulan varchar(20),
tahun int,
umur int
)

CREATE TABLE tua(
noktp CHAR(11) PRIMARY KEY,
tgl_lahir datetime NOT NULL CONSTRAINT tgllahir FOREIGN KEY REFERENCES penduduk(tgl_lahir),
FOREIGN KEY(noktp) REFERENCES penduduk(noktp),
)

Answer 1

Try this:

create table penduduk (
no int identity(1,1),
noktp char(11) primary key,
nama varchar(20),
tgl_lahir datetime NOT NULL unique,
namahari varchar(20),
tgl int,
bulan int,
namabulan varchar(20),
tahun int,
umur int
)

CREATE TABLE tua(
noktp CHAR(11) PRIMARY KEY,
tgl_lahir datetime NOT NULL CONSTRAINT tgllahir FOREIGN KEY REFERENCES penduduk(tgl_lahir),
FOREIGN KEY(noktp) REFERENCES penduduk(noktp),
)

Answer 2

You can only use a column as a reference in a Foreign Key constraint if it's a suitable candidate key.

From Books Online:

A FOREIGN KEY constraint does not have to be linked only to a PRIMARY KEY constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table.

See Foreign Key Constraints.

In your case tgl_lahir is neither Unique or a Primary so cannot be used in your Foreign Key constraint.

If you add a Unique constraint to tgl_lahir it should work; whether that's workable with your data is the real question.

Answer 3

penduduk.tgl_lahir column needs to be defined as either a primary key or with a unique constraint to be referenced by a foreign key constraint.

SQLFiddle